Dynamic sql generation using oracle 10g stored procedures

[archna1]

Hi,

I created a stored procedure as --

 
 
(
V_Search_Key in varchar2,
P_LinkSearchResults_CUR out sys_refcursor
)
as
lv_sql varchar2(1000);
begin
lv_sql := 'select b.link_id, b.link_display_name, b.override_link_display_name, b.link_path
from oppe_search a, lu_links b 
where a.content_id=b.link_ID and upper(a.search_key) in (:v_search_key)' ;
open P_LinkSearchResults_CUR FOR 
lv_sql using v_search_key;
end;
 
 

I dont get any errors but when I pass on values to it, it returns no rows although there are rows in the table for the search string. Any clue on how I can form this search string. Is it the best way of writing the procedure?

Any immediate help is greatly appreciated.

Thanks,
Archna

[amitpatel66]

Try this:

 
(
V_Search_Key IN VARCHAR2,
P_LinkSearchResults_CUR OUT sys_refcursor
)
AS
lv_sql VARCHAR2(1000);
BEGIN
lv_sql := 'select b.link_id, b.link_display_name, b.override_link_display_name, b.link_path
from oppe_search a, lu_links b 
where a.content_id=b.link_ID and upper(a.search_key) = UPPER(:v_search_key)' ;
OPEN P_LinkSearchResults_CUR FOR 
lv_sql USING v_search_key;
END;
 
 

I have used "=" sign instead of IN becuase it is got to be one value passed as input parameter right?

[archna1]

My input parameter is comma separated values to search within a table, hence I needed dynamic sql and the 'IN' clause. It is part of a 'Search' function I am building for an intranet, so I needed all the values entered by the user for Search--

I tried this code and unexpectedly it worked..

 
(
V_Search_Key in varchar2,
P_LinkSearchResults_CUR out sys_refcursor
)
as
begin
open P_LinkSearchResults_CUR FOR 
'select b.link_id, b.link_display_name, b.override_link_display_name, b.link_path ' ||
'from oppe_search a, lu_links b ' ||
'where a.content_id=b.link_ID and upper(a.search_key) in (' || 
V_Search_Key || ')';
 
end;
 

Thanks for your reply.

----------------------------------------------------------------------------------------------------------------

Try this:

 
(
V_Search_Key IN VARCHAR2,
P_LinkSearchResults_CUR OUT sys_refcursor
)
AS
lv_sql VARCHAR2(1000);
BEGIN
lv_sql := 'select b.link_id, b.link_display_name, b.override_link_display_name, b.link_path
from oppe_search a, lu_links b 
where a.content_id=b.link_ID and upper(a.search_key) = UPPER(:v_search_key)' ;
OPEN P_LinkSearchResults_CUR FOR 
lv_sql USING v_search_key;
END;
 
 

I have used "=" sign instead of IN becuase it is got to be one value passed as input parameter right?

[Saii]

There is nothing unexpected about this. When you pass a list of values to NDS, you have to enclose the list in quotes.