Dynamic sql generation using oracle 10g stored procedures

[archna1]

Hi,

I created a stored procedure as --

[code=oracle]


(
V_Search_Key in varchar2,
P_LinkSearchRes ults_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_LinkSearchRes ults_CUR FOR
lv_sql using v_search_key;
end;

[/code]



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:

[code=oracle]

(
V_Search_Key IN VARCHAR2,
P_LinkSearchRes ults_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_LinkSearchRes ults_CUR FOR
lv_sql USING v_search_key;
END;

[/code]


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..
[code=oracle]

(
V_Search_Key in varchar2,
P_LinkSearchRes ults_CUR out sys_refcursor
)
as
begin
open P_LinkSearchRes ults_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;
[/code]
Thanks for your reply.

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

Try this:

[code=oracle]

(
V_Search_Key IN VARCHAR2,
P_LinkSearchRes ults_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_LinkSearchRes ults_CUR FOR
lv_sql USING v_search_key;
END;

[/code]


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.