-
Passing in Comma Separated List to Stored Proc Input Param
I have a Stored Procedure in Oracle that accepts a VARCHAR input parameter - I_LIST IN VARCHAR. The value will be a comma separated list "1,2,3".
The SQL statement in the proc is select * from table where id in (I_LIST);
No results are returned. When I just pass in one value it works fine but more then one and I get no results. Any thoughts? Thanks!!
-
Hi.
It's using the parameter as a single item, not a list of items. For an explanation of what to do look at this:
http://asktom.oracle.com/pls/ask/f?p...D:110612348061
Cheers
Tim...
-
Also see here.
If PL/SQL could parse comma-separated lists transparently like that, how would it know when not to? What happens when you pass an address, say, that happens to contain a comma?
You could try something similar to this (requires 9i):
Code:
var i_list VARCHAR2(100)
exec :i_list := '7369,7566,7788,9999,5'
SELECT empno, ename FROM emp
WHERE empno IN
( SELECT EXTRACTVALUE(xt.column_value,'e')
FROM TABLE(XMLSEQUENCE
( EXTRACT
( XMLTYPE('<coll><e>' ||
REPLACE(:i_list,',','</e><e>') ||
'</e></coll>')
, '/coll/e') )) xt );
or as a PL/SQL block,
Code:
DECLARE
p_csvlist VARCHAR2(100) := '7369,7566,7788,9999,5';
p_xmllist XMLTYPE :=
XMLTYPE('<coll><e>' || REPLACE(p_csvlist,',','</e><e>') || '</e></coll>');
BEGIN
FOR r IN (
SELECT empno, ename FROM emp
WHERE empno IN
( SELECT EXTRACTVALUE(xt.column_value,'e')
FROM TABLE(XMLSEQUENCE(EXTRACT(p_xmllist,'coll/e'))) xt )
)
LOOP
DBMS_OUTPUT.PUT_LINE(r.empno || ' ' || r.ename);
END LOOP;
END;
/
-
Hi all,
Above Query Work Fine For Number Datatype but how can i supply multiple values having varchar datatype
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|