More on Dynamically Building a Large PL/SQL Package

Here's a somewhat more realistic, worldly example that uses str2arr.

This builds a package header containing the prototypes for insert routines for every table.

DECLARE
  c_ins CONSTANT VARCHAR2(100)      := 'PROCEDURE ins_~t(~pl);';
  v_stmt         VARCHAR2(32767);
  v_arr          DBMS_SQL.varchar2s;   -- index-by array to hold generated code
  v_cur          INTEGER;   -- cursor handle
  v_pl           VARCHAR2(32767);
  v_delim        VARCHAR2(1);
BEGIN
  v_cur  := DBMS_SQL.open_cursor;   -- dbms_sql cursors are reusable
  str2arr('CREATE OR REPLACE PACKAGE ins IS ', v_arr, 0);
  FOR t IN (SELECT   table_name
                FROM user_tables
               WHERE table_name like 'J%' -- limit output for this example
            ORDER BY table_name) LOOP
    FOR c IN (SELECT   LOWER(column_name) column_name
                  FROM user_tab_columns
                 WHERE table_name = t.table_name
              ORDER BY column_id) LOOP
      v_pl     := v_pl ||
                  v_delim ||
                  'p_' ||
                  c.column_name ||
                  ' IN ' ||
                  LOWER(t.table_name) ||
                  '.' ||
                  c.column_name ||
                  '%TYPE';
      v_delim  := ',';
    END LOOP;
    v_stmt   := REPLACE(c_ins, '~t', LOWER(t.table_name));
    v_stmt   := REPLACE(v_stmt, '~pl', v_pl);
    str2arr(v_stmt, v_arr, v_arr.COUNT, 78, CHR(10) || ',;');
    -- reset
    v_pl     := NULL;
    v_delim  := NULL;
    v_stmt   := NULL;
  END LOOP;
  str2arr('END ins;', v_arr, v_arr.COUNT);
  -- display contents
  FOR i IN 0 .. v_arr.LAST LOOP
    DBMS_OUTPUT.put_line(v_arr(i));
  END LOOP;
  DBMS_SQL.parse(v_cur, v_arr, 0, v_arr.LAST, TRUE, DBMS_SQL.native);
  DBMS_SQL.close_cursor(v_cur);
END;
/

The output from this is shown below:

---

This builds the matching package body implementing the insert routines.

DECLARE
  c_ins CONSTANT VARCHAR2(32767)
    := 'PROCEDURE ins_~t(~pl)
IS
BEGIN
  INSERT INTO ~t (
    ~cc
  ) VALUES (
    ~pc
  );
END ins_~t;
';
  v_stmt         VARCHAR2(32767);
  v_arr          DBMS_SQL.varchar2s;   -- index-by array to hold generated code
  v_cur          INTEGER;   -- cursor handle
  v_pl           VARCHAR2(32767);
  v_cc           VARCHAR2(32767);
  v_pc           VARCHAR2(32767);
  v_delim        VARCHAR2(1);
BEGIN
  v_cur  := DBMS_SQL.open_cursor;   -- dbms_sql cursors are reusable
  str2arr('CREATE OR REPLACE PACKAGE BODY ins IS ', v_arr, 0);
  FOR t IN (SELECT   table_name
                FROM user_tables
               WHERE table_name like 'J%' -- limit output for this example
            ORDER BY table_name) LOOP
    FOR c IN (SELECT   LOWER(column_name) column_name
                  FROM user_tab_columns
                 WHERE table_name = t.table_name
              ORDER BY column_id) LOOP
      v_pl     := v_pl ||
                  v_delim ||
                  'p_' ||
                  c.column_name ||
                  ' IN ' ||
                  LOWER(t.table_name) ||
                  '.' ||
                  c.column_name ||
                  '%TYPE';
      v_cc     := v_cc || v_delim || c.column_name;
      v_pc     := v_pc || v_delim || 'p_' || c.column_name;
      v_delim  := ',';
    END LOOP;
    v_stmt   := REPLACE(c_ins, '~t', LOWER(t.table_name));
    v_stmt   := REPLACE(v_stmt, '~pl', v_pl);
    v_stmt   := REPLACE(v_stmt, '~cc', v_cc);
    v_stmt   := REPLACE(v_stmt, '~pc', v_pc);
    str2arr(v_stmt, v_arr, v_arr.COUNT, 78, chr(10)||',;');
    -- reset
    v_pl     := NULL;
    v_cc     := NULL;
    v_pc     := NULL;
    v_delim  := NULL;
    v_stmt   := NULL;
  END LOOP;
  str2arr('END ins;', v_arr, v_arr.COUNT);
  -- display contents
  FOR i IN 0 .. v_arr.LAST LOOP
    DBMS_OUTPUT.put_line(v_arr(i));
  END LOOP;
  dbms_sql.parse(v_cur, v_arr, 0, v_arr.last, true, dbms_sql.native);
  dbms_sql.close_cursor(v_cur);
END;
/

The output I get for the HR schema:

When I look at my schema browser in TOAD, I see a valid package header and body.

The resulting generated code might not be all that pretty, but it woiks!

Other things to note:

Note how I controlled what to break a long line on -- just newlines, commas and semi-colons! The order of delimiters in the list may affect line breaks slightly by finding one before another.

I can control how wide to allow the code to grow.

I use the '~x' for placeholders to replace strings at. I only picked tilde because I figured it's unlikely to occur in one's code. Not! Literally.

Note how easy it was to go from the package header to the body, code-wise (actually, I developed the code for the package body first).