Listing 4: Gen_multcoll.sp (multilevel-collection code generator)


CREATE OR REPLACE PROCEDURE gen_multcoll (
   levels   IN   INTEGER,
   showit   IN   BOOLEAN := FALSE
)
IS

   lines     DBMS_SQL.varchar2s;
   typestr   VARCHAR2 (100)     := 'VARCHAR2(100)';

   PROCEDURE addline (line_in IN VARCHAR2)
   IS
   BEGIN

      lines (  NVL (lines.LAST, 0)
             + 1) := line_in;

      IF showit
      THEN
         DBMS_OUTPUT.put_line (line_in);
      END IF;

   END;
BEGIN
   addline (
      'create or replace procedure multcoll_test is'
   );

   FOR indx IN 1 .. levels
   LOOP

      addline (
            'type ibtab'
         || indx
         || ' is table of '
         || typestr
         || ' index by binary_integer;'
      );
      typestr :=    'ibtab'
                 || indx;

   END LOOP;

   addline (   'mytab ibtab'
            || levels
            || ';');
   addline ('begin');
   addline ('mytab');

   FOR indx IN 1 .. levels
   LOOP

      addline ('(1)');
   END LOOP;

   addline (' := ''abc'';');
   addline ('dbms_output.put_line (mytab');

   FOR indx IN 1 .. levels
   LOOP
      addline ('(1)');
   END LOOP;


   addline (');');
   addline ('end;');
   exec_array (lines);
   lines.DELETE;
   lines (1) := 'begin multcoll_test; end;';
   exec_array (lines);
END;
/