Re: char parameter to cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 17 Aug 1999 18:41:39 GMT
Message-ID: <37c4ab76.113905207_at_newshost.us.oracle.com>

A copy of this was sent to "Bernard Willemot" <Bernard.Willemot_at_solluflex.net> (if that email address didn't require changing) On Tue, 17 Aug 1999 20:14:19 +0200, you wrote:

>Hi,
>
>Have a small problem here passing a special variable of type char to
>cursor...
>
>declare
> thegroup char(10);
> cursor c1(what char) is select id from products where id in (what);
>
>begin
> thegroup := '5,8,10';
>
> for a in c1(thegroup) loop
> insert into requests (prodid) values a.id;
> end loop;
>end;
>/
>
>I supose this is not a realy difficult one, but I cannot find it...
>
>Can anyone help me ?
>
>Thanx, BW.
>
>
>

that is the same as saying:

select id from procedure where id in ( '''5,8,10''' );

that will return all records where id equals the string '5,8,10'.

Using static SQL you cannot alter the number of elements in a "in list". The above query has 1 element in the in list and will never have more or less then that.

You would use dynamic sql to do this.

In Oracle8i, release 8.1, it might look like:

declare

    type refcur is ref cursor;
    l_cursor refcur;
    thegroup char(10);
    l_id number;
begin

    thegroup := '5,8,10';

    open l_cursor for

       'select id from products where id in ( ' || thegroup || ')';

    loop

       fetch l_cursor into l_id;
       exit  when l_cursor%notfound;
       insert into requests values ( l_id );

    end loop;
end;

(actually, if the logic is as simple as that I would just code:

begin

   execute immediate 'insert into requests

                      select id 
                        from products 
                       where id in ( ' || thegroup || ')';

end;

and be done with it...)

In Oracle8.0 and before you must use dbms_sql, for example:

create or replace procedure dynquery( p_tname in varchar2, p_set in varchar2) is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_query         varchar2(1000) default 'select ename from ' || p_tname ||
                                          ' where job in ( ' ||
                                            p_set || ' )';

begin

    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );     dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );
        dbms_output.put_line(  l_columnValue );

    end loop;
    dbms_sql.close_cursor(l_theCursor);

end ;
/

exec dynquery( 'emp', ' ''CLERK'', ''MGR'' ' )

shows the relevant syntax and such.....     

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Tue Aug 17 1999 - 20:41:39 CEST