Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Tokyo
    Posts
    10

    Unanswered: Problem in PL/SQL Cursor

    Hi All

    I am having a problem in Pl/SQL code. Following is the code:

    Cursor Attributevalue_cur(list_in varchar2) is
    Select
    Value
    From
    AttributeValue
    Where
    model_id = 4179
    And
    Attribute_NO in
    (list_in);
    Now When I do:
    myList := 'A0031';
    Open Attributevalue_cur(myList);

    The procedure works but when I do:
    myList := 'A0031, A0032';
    Open Attributevalue_cur(myList);
    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 advance
    Omer Imtiaz

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    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:
    execute procedure('value1,value2');
    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) := ')';
    begin
    l_sqlstring := l_sqlstring||list||l_string_end;
    execute immediate l_sqlstring;
    end;
    /

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •