Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Dynamic Table Names in PL/SQL Cursor
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
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;