Dynamic Table Names in PL/SQL Cursor

From: Christian Kurz <kurz_at_ani.univie.ac.at>
Date: Thu, 17 Aug 2000 13:09:26 +0200
Message-ID: <8ngh0t$66go$1@www.univie.ac.at>

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; Received on Thu Aug 17 2000 - 06:09:26 CDT