Re: char parameter to cursor
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 CorporationReceived on Tue Aug 17 1999 - 20:41:39 CEST