Thread: Problem in PL/SQL Cursor
10-02-03, 23:39 #1Registered User
- Join Date
- Oct 2003
Unanswered: Problem in PL/SQL Cursor
I am having a problem in Pl/SQL code. Following is the code:
Cursor Attributevalue_cur(list_in varchar2) is
model_id = 4179
Now When I do:
myList := 'A0031';
The procedure works but when I do:
myList := 'A0031, A0032';
It doesnt work
i have even tried this thing:
myList := '''' ||'A0031'|| '''' ||',' || '''' || 'A0032'|| '''';
but even then it doesnt work.
Can Anybody help me with this? I need to make several queries in the same procedure and all of them use the same "AttributeList" which itself is calculated by writing 2 queries. So ofcourse I can not afford to write them again and again...
Thanx in advanceOmer Imtiaz
10-03-03, 02:35 #2Registered User
- Join Date
- Sep 2003
- The Netherlands
Your problem is that you create a search list but it is not treated like that by the cursor.Say you call the procedure like this:
This string is used by the cursor like this :
select * from tabel where item_no in ('value1,value2')
But you wanted :
select * from table where item_no in ('value1', 'value2');
(Note the quotation marks).
If you want to use the procedure to be able to use one or more values like above, use Native Dynamic SQL.
Code will be like this :
procedure x (list varchar2) is
l_sqlstring varchar2(1200) := select * from table where item_no in (';
l_string_end varchar2(20) := ')';
l_sqlstring := l_sqlstring||list||l_string_end;
execute immediate l_sqlstring;
Beware : if the select returns more then one row you'll have to rewrite the code to be able to fetch all the records.
Hope this helps.Edwin van Hattem
OCP DBA / System analyst