Re: Dynamic Table Names in PL/SQL Cursor

In article <8ngh0t$66go$1_at_www.univie.ac.at>,   "Christian Kurz" <kurz_at_ani.univie.ac.at> wrote:
> Hi!
>
> I have a problem creating cursors with dynamic table names. I am not
 sure if
> it is possible at all. If you have any suggestions concerning my
 problem
> please send me a mail. Thanks
>
> DECLARE
> TYPE ArrChar IS VARRAY (23) OF CHAR (10);
> portnrs ArrChar;
> TYPE GenericCurTyp IS REF CURSOR;
> c_packsz GenericCurTyp;
> sess_rec bisante.packsz_cnt_1_50%ROWTYPE;
>
> PROCEDURE exec(STRING IN varchar2) AS
> cursor_exec INTEGER;
> ret INTEGER;
> BEGIN
> cursor_exec := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE (cursor_exec, string, DBMS_SQL.native);
> ret := DBMS_SQL.EXECUTE (cursor_exec);
> DBMS_SQL.CLOSE_CURSOR (cursor_exec);
> END;
>
> BEGIN
> portnrs := ArrChar ('14', '17', '19', '50');
> FOR currentcluster IN 1..1 LOOP --for each Cluster, first only for
> cluster 1
> FOR i IN 1..23 LOOP --all clusters
> exec ('OPEN c_packsz FOR SELECT * FROM packsz_cnt_' ||
> currentcluster || '_' || portnrs(i) ||
> 'ORDER BY value)');
> LOOP
> ................................. do some ops on the table
> END LOOP;
> END LOOP;
> END LOOP;
> COMMIT;
> END;
>

If you hard-code the variable string in procedure exec, does it work? In other words, instead of:

DBMS_SQL.PARSE (cursor_exec, string, DBMS_SQL.native);

use:

DBMS_SQL.PARSE (cursor_exec, 'OPEN c_packsz FOR SELECT * FROM packsz_cnt_1_14 ORDER BY value)', DBMS_SQL.native);

If this works, then your code above should work, too. Sorry I can't give you more answers--I'm not in a position to try it out now.

Salaam Yitbarek

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 18 2000 - 12:37:26 CDT