Dynamically Renaming Key Constraints with System-Generated Names

We recently ran into an issue at work where a large number of primary key and unique key constraints had not been named, but instead, allowed to have system-generated names (which appear as SYS_Cxxxxxx in the data dictionary). I decided to develop a package to dynamically rename these particular constraints, for a given table or en masse for all tables having such unnamed key constraints.

The impetus for doing this is that we support many clients with our database-centric application. Should a customer experience an error caused by a restricted operation on a primary or unique key (such as a DUP_VAL_ON_INDEX exception), it’d be nice to have the actual name of the key constraint that was violated. The constraint and index names should really be consistent across all customers. Additionally, system-generated names change whenever a schema in exported and imported. Naming the constraints keeps the names properly consistent, and falls into the proper realm of adherence to naming conventions.

Some premises/observations/caveats of note:

• We desire to change system-generated key constraints to a standard form, such as pk_ (up to 30 chars) and have the index name match as well.
• Some table names are too long to follow the simple PK_ pattern. The key name is therefore truncated to 30 chars. If that results in a name collision, chop it to 29 and add a digit. If there is still a name collision, increment the trailing digit until one is available (up to 9 tries).
• All currently developer-named key constraints have index names that match.
• Some key constraints with system-generated names may have underlying indexes that are developer-designed. This occurs when Oracle decides an existing index is suitable for supporting the constraint. These are not modified.
• While a table can have at most one (1) primary key, it can have one or more unique keys.
• For regular tables, we can simply rename the constraint and underlying index.
• We can't rename the underlying index for Global Temporary Tables (GTT's); instead, disable the constraint and drop the index, enable and recreate the index.
• I’m only doing this for the current schema.

That said, I began by prototyping a query to locate system-generated primary key constraints named the same as the underlying system-generated index names:

SELECT   uc.table_name, uc.constraint_name, uc.index_name
FROM user_constraints uc
WHERE uc.constraint_type IN ('P', 'U')
AND uc.constraint_name LIKE 'SYS_C%'
AND uc.index_name LIKE 'SYS_C%'
ORDER BY uc.table_name;

From my list of caveats above, I also need to add whether this is a global temp table. So I add a scalar subquery to the Select clause:

SELECT   uc.table_name, uc.constraint_name, uc.index_name
,(SELECT ut.TEMPORARY
FROM user_tables ut
WHERE ut.table_name = uc.table_name) TEMPORARY
FROM user_constraints uc
WHERE uc.constraint_type IN ('P', 'U')
AND uc.constraint_name LIKE 'SYS_C%'
AND uc.index_name LIKE 'SYS_C%'
ORDER BY uc.table_name;

I need to know whether the new name already exists. If it does, I have to implement the incremental numbering suffix scheme. Since I may be using the query in more than one place, I should encapsulate it in a function for reuse.

-- does given constraint exist?
-- should only be one of the given name
FUNCTION cnt_constraint(
p_constraint_name IN user_constraints.constraint_name%TYPE
)
RETURN pls_integer
IS
v_cnt_exist pls_integer;
BEGIN
SELECT   COUNT( * ) cnt
INTO     v_cnt_exist
FROM     user_constraints uc
WHERE    uc.constraint_name = p_constraint_name;

RETURN (v_cnt_exist);
END cnt_constraint;

This will return either a 1 or 0 – does or does not exist.

I’m building a package, so the interface (in the package header) is a good place to begin.

--  Optional tablename or NULL for all
PROCEDURE rename_key(p_tablename IN user_constraints.table_name%TYPE);

Here is my assembled program, with comments:

PROCEDURE rename_key(
p_tablename IN user_constraints.table_name%TYPE)
IS
v_stmt VARCHAR2(4000);  -- the dynamic statement
v_new_key_name VARCHAR2(30);  -- new constraint name created
v_cnt PLS_INTEGER := 0;
v_error PLS_INTEGER := 0;
v_loop_cnt PLS_INTEGER;
-- normalize input name
v_table_name user_constraints.table_name%TYPE := UPPER(p_tablename);
BEGIN
-- a cursor FOR loop is adequate for the task at hand
-- this will process all system-named primary/unique keys/indexes found
-- for the given table or all tables if null
FOR rec IN (SELECT   uc.table_name, uc.constraint_type, uc.constraint_name
,uc.index_name
,(SELECT ut.TEMPORARY
FROM   user_tables ut
WHERE  ut.table_name = uc.table_name) TEMPORARY
FROM     user_constraints uc
WHERE    uc.constraint_type IN('P', 'U')
AND      uc.constraint_name LIKE 'SYS_C%'
AND      uc.index_name LIKE 'SYS_C%'
AND      (v_table_name IS NULL OR uc.table_name = v_table_name)
ORDER BY uc.table_name) LOOP
BEGIN
-- build the constraint name according to the naming convention
-- PK_ or UK_, up to 30 chars (the limit)
v_new_key_name := SUBSTR(rec.constraint_type || 'K_' || rec.table_name
,1
,30);
v_loop_cnt := 0;

-- does the new name already exist?
-- if so, chop the last character and add a digit
-- up to 9 tries (should be enough)
WHILE(cnt_constraint(v_new_key_name) > 0 AND v_loop_cnt <= 9) LOOP
DBMS_OUTPUT.put_line(v_new_key_name || ' already exists; adjusting...');
v_loop_cnt := v_loop_cnt + 1;
v_new_key_name := SUBSTR(rec.constraint_type || 'K_' || rec.table_name
,1
,29) ||
TO_CHAR(v_loop_cnt, 'FM9');
DBMS_OUTPUT.put_line('Trying ' || v_new_key_name || '...');
END LOOP;

-- assemble the alter table-rename constraint statement
v_stmt := 'alter table ' ||
rec.table_name ||
' rename constraint ' ||
rec.constraint_name ||
' to ' ||
v_new_key_name;
-- always a good idea to see what you've built
DBMS_OUTPUT.put_line(v_stmt || ';');

EXECUTE IMMEDIATE v_stmt;  -- make it so!

-- special handling for GTT's
IF (rec.TEMPORARY = 'N') THEN
-- regular tables don't need special handling
-- assemble the alter index-rename statement
v_stmt := 'alter index ' ||
rec.index_name ||
' rename to ' ||
v_new_key_name;
DBMS_OUTPUT.put_line(v_stmt || ';');

EXECUTE IMMEDIATE v_stmt;  -- rename index
ELSE   -- can't rename index on global temp table
-- first, disable newly renamed constraint and whack index
v_stmt := 'alter table ' ||
rec.table_name ||
' disable constraint ' || v_new_key_name ||
' drop index';
DBMS_OUTPUT.put_line(v_stmt || ';');

EXECUTE IMMEDIATE v_stmt;

-- re-enable newly renamed constraint, building a new index
v_stmt := 'alter table ' ||
rec.table_name ||
' enable constraint ' || v_new_key_name ||
' using index';
DBMS_OUTPUT.put_line(v_stmt || ';');

EXECUTE IMMEDIATE v_stmt;
END IF;

v_cnt := v_cnt + 1;
EXCEPTION
-- handling errors on each loop iteration allows me 
-- to continue renaming other constraints even if one fails
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
v_error := v_error + 1;
END;
END LOOP;

-- housekeeping messages
DBMS_OUTPUT.put_line(v_cnt || ' Key constraints and their indexes adjusted.');
DBMS_OUTPUT.put_line(v_error || ' errors occurred.');
END rename_key;
/


-- now let's try it out
BEGIN
rename_key(null);
END;
/

Here is my output:

alter table UNK_GTT rename constraint SYS_C004797 to PK_UNK_GTT;

alter table UNK_GTT disable constraint PK_UNK_GTT drop index;

alter table UNK_GTT enable constraint PK_UNK_GTT using index;

alter table UNK_GTT rename constraint SYS_C004798 to UK_UNK_GTT;

alter table UNK_GTT disable constraint UK_UNK_GTT drop index;

alter table UNK_GTT enable constraint UK_UNK_GTT using index;

PK_UNK_PKEY already exists; adjusting...

Trying PK_UNK_PKEY1...

alter table UNK_PKEY rename constraint SYS_C004799 to PK_UNK_PKEY1;

alter index SYS_C004799 rename to PK_UNK_PKEY1;

alter table UNK_UKEY rename constraint SYS_C004800 to UK_UNK_UKEY;

alter index SYS_C004800 rename to UK_UNK_UKEY;

4 Key constraints and their indexes adjusted.

0 errors occurred.

Running it again, I should see no additional work done.

0 Key constraints and their indexes adjusted.

0 errors occurred.

I should also verify no system-named key constraints or indexes now exist.

SELECT uc.table_name, uc.constraint_name, uc.constraint_type
FROM   user_constraints uc
WHERE  uc.constraint_type IN('P', 'U') AND uc.constraint_name LIKE 'SYS_C%';

SELECT ui.table_name, ui.index_name
FROM   user_indexes ui
WHERE  ui.index_name LIKE 'SYS_C%';

If there were, it’s probably because one or the other key constraint and index weren’t system-named.

Here is my test case setup. I deliberately did not name the key constraints, and I needed a key constraint name that would conflict with a to-be generated one.

DROP TABLE unk_gtt CASCADE CONSTRAINTS;

CREATE GLOBAL TEMPORARY TABLE unk_gtt
(
ID   INTEGER,
str  VARCHAR2(100),
id2  INTEGER
)
ON COMMIT DELETE ROWS;

ALTER TABLE unk_gtt ADD (
PRIMARY KEY
(ID),
UNIQUE (id2));

DROP TABLE unk_pkey CASCADE CONSTRAINTS;

CREATE TABLE unk_pkey
(
ID   INTEGER,
str  VARCHAR2(100)
);

ALTER TABLE unk_pkey ADD (
PRIMARY KEY
(ID)
);

DROP TABLE unk_ukey CASCADE CONSTRAINTS;

CREATE TABLE unk_ukey
(
ID   INTEGER,
str  VARCHAR2(100)
);

ALTER TABLE unk_ukey ADD (
UNIQUE (ID)
);

DROP TABLE unk_overlap;

CREATE TABLE unk_overlap
(
ID   INTEGER,
str  VARCHAR2(100)
);

ALTER TABLE unk_overlap
ADD CONSTRAINT pk_unk_pkey
PRIMARY KEY (ID);

ALTER TABLE unk_overlap ADD (
FOREIGN KEY (id) 
REFERENCES unk_ukey (id));

Note the foreign key on unk_overlap shows that the pkey/ukey constraints can be renamed even if a foreign key exists on them. Changing the name of the key constraint doesn't impact the fkey; likewise, the fkey in place doesn't prevent you from renaming the key constraint.

Dynamic Method 4 – Fetching a Variable Number of Columns in PL/SQL

Oracle defines four methods for performing dynamic SQL, DML and DDL statements. These are:

A non-query statement (e.g., “ALTER”, “UPDATE”) with no host/bind variables

A non-query statement with a known number of bind variables

A query statement with a known number of SELECT clause items and input bind variables

A query statement with an unknown number of SELECT clause itemas and/or input bind variables

Precompiler languages like Pro*C allow for ANSI-compatible and (the antiquated) non-ANSI methods for implementing SQL/DML/DDL to any degree of dynamism. Both styles generally require a very good understanding of the methodology, as well as moderate to advanced programming skill.

PL/SQL offers two distinct and non-interchangeable methods of performing dynamic SQL: Package DBMS_SQL and the language feature EXECUTE IMMEDIATE. Both approaches have their pros and cons, as well as limitations. In this article, I show a technique for dynamically executing queries that return a variable number of columns using EXECUTE IMMEDIATE.

In order to return varying sets of columns at runtime, we have to decide where to put them. We need a data structure that can easily handle one or more column values returned for each row, across multiple rows. In Oracle9iR2 and above, we can use a collection of collections. Here, I define two schema-level collections.

DROP TYPE nt_table_row; -- drop 1st because of dependency
-- stores one or column values (a single row)
CREATE OR REPLACE
TYPE nt_column_value IS TABLE OF VARCHAR2(4000);
/
-- stores one or more rows
CREATE OR REPLACE
TYPE nt_table_row IS TABLE OF nt_column_value;
/

The intent is for nt_column_value to capture each column’s value in each successive array element, like this:Figure 1, Mapping columns to array elements
Then each one of these is attached to an nt_table_row element, like this:Figure 2, Storing a list of column values as rows
The goal is to build a dynamic statement that fetches each column in the SELECT clause into each successive element of nt_column_value, then assign a collection of column values to each successive element of nt_table_row to represent each row.
The collection nt_column_value is always loaded with the same number of columns for each row fetched, so we can extend it to the correct number of elements once and use it over and over. When it is assigned to one of the nt_table_row elements, a copy of it is stored. We can then overwrite the nt_column_value values with the next row fetched, and store those in the next element.

If I was to prototype this as a typical anonymous block, it might look something like this:

declare
  cursor cur is
  select
    column1
   ,column2
   ,column3
    ... 
   ,columnN
  from some_table
  ;
  arr_row nt_table_row := nt_table_row();
  arr_column_value nt_column_value := nt_column_value();
begin
  arr_column_value.extend(num_cols); /* always this many columns */
  open cur;
  loop
    fetch cur into
      arr_column_value(1)
     ,arr_column_value(2)
     ,arr_column_value(3)
     ...
     ,arr_column_value(N);
    exit when cur%notfound;
    arr_row.extend;
    arr_row(arr_row.count) := arr_column_value;
  end loop;
  close cur;
end;

This isn’t bad, but the looping and fetching and element-at-a-time extension doesn’t seem very efficient. I’d prefer to do bulk fetching, if possible. Fortunately, I can have the query return an nt_column_value collection directly for each row. The SELECT clause becomes:

select
  nt_column_value(
    column1
   ,column2
   ,column3
    ... 
   ,column
  )

Then I can bulk collect the cursor directly into an nt_table_row collection:

  open cur;
  fetch cur bulk collect into arr_row;
  close cur;

And I get the collection extension and assignment for free.

Ultimately, this anonymous block has to be done dynamically. I need to generalize it into a template form. I use placeholders to indicate sections of code that has to be replaced at runtime. As a convention, I use a tilde (~) followed by a single character for each placeholder. Things like column lists, table name and other integral components must be “hard-coded” into the statement; you can’t use bind variables for those. So now my dynamic block looks like this:

  v_stmt VARCHAR2(32767) :=
'declare
  cursor cur is
  select
   nt_column_value(~c)
  from ~t
  ;
  arr_row nt_table_row := nt_table_row();
begin
  open cur;
  fetch cur bulk collect into arr_row;
  close cur;
  :a := arr_row;
end;';

The placeholder ~c is the column list. The placeholder for the table is ~t. I replace them with the REPLACE() function:

  -- build dynamic block
  v_stmt := REPLACE(v_stmt, '~c', v_column_list);
  v_stmt := REPLACE(v_stmt, '~t', v_table_name);

where the v_column_list and v_table_name strings have yet to be constructed.

I want to actually return the collection to the calling context, so the bind variable :a is needed to provide the interface. When calling with Execute Immediate, the collection is returned via an OUT bind variable:

  EXECUTE IMMEDIATE v_stmt
  USING OUT arr_row;

In order to construct the column list, I need to go to the Data Dictionary and look them up for the given table. I prefer to provide each bind value as a cursor parameter because this programming style localizes the scope of the parameter name right where it’s used. If I don’t pass it in, I don’t reference it in my cursor declaration.

For purposes of illustration, let’s just stick with the basic data types. Other data types, such as LONG or LOB columns, require special handling, particularly if they contain values over 32K. There are other unusual data types I don’t want to consider at this time, such as object types, timestamps, etc. My cursor looks like this:

  -- ignore object types, LONG, LOB's etc.
  CURSOR cur_columns?(p_table_name user_tab_columns.table_name%TYPE)
  IS
  SELECT utc.column_name, utc.data_type
  FROM user_tab_columns utc
  WHERE utc.table_name = p_table_name
    AND utc.data_type IN ('VARCHAR2','CHAR','DATE','NUMBER','FLOAT')
  ORDER BY utc.column_id
  ;

I typically declare cursors up front, rather than embed them implicitly directly into the executable code, because I get more flexibility with explicit cursors. I can use them anywhere an implicit cursor would work, and then some. I have much more control with explicit cursors. I can reuse them easily. I can still fetch from them one at a time or do bulk fetches. About the only time I use implicit cursors is when I expect them to return exactly one row, or it’s a DML statement.

I need to declare some variables to hold the pieces of my lists:

v_sep VARCHAR2(1);   -- column name separator
v_column_expr VARCHAR2(60); -- a single column expression
v_column_list VARCHAR2(32767); -- the delimited list of columns
v_data_type_list VARCHAR2(32767); -- delimited list of data types
v_date_format VARCHAR2(22) := 'DD-MON-YYYY'; -- whatever you want

Now I can assemble the lists. I want each column expression to always return a string, since that’s what I’m fetching them into. It’s always best to do data type conversions explicitly. This is especially true for DATE columns, where the return string is strongly dependent on session-specific settings like nls_date_format.

  -- build lists for dynamic block
  FOR rec IN cur_columns(UPPER(v_table_name)) LOOP
    v_column_expr :=
     (CASE 
      WHEN rec.data_type IN ('VARCHAR2','CHAR') THEN
           rec.column_name
      WHEN rec.data_type = 'DATE' THEN
           'to_char(' || rec.column_name || ',c_date_format)'
      WHEN rec.data_type IN ('NUMBER','FLOAT') THEN
           'to_char(' || rec.column_name || ')'
      ELSE rec.column_name
      END)
    ;
    v_column_list := v_column_list || v_sep || v_column_expr;
    v_data_type_list := v_data_type_list || v_sep || rec.data_type;
    v_sep := ',';
  END LOOP;

c_date_format is a constant string that is going to be declared in my dynamic block. The actual value for it will be passed in via a bind variable, so that the caller can set it as desired.

In 9iR2, I tried putting the CASE expression directly into the v_column_list concatenation and assignment, but ran into a known bug that corrupted the string (and was fixed in 10g). That’s the only reason I used the intermediate variable v_column_expr.

I put all of this together into a prototype as a proof of concept. I typically do this prior to embarking on building packages, to make sure that what I’m attempting will work. I have a couple of parallel arrays, arr_column_name and arr_data_type, to hold the column names and data types respectively, for display purposes. The variable v_limit is used simply to restrict the number of rows returned from the dynamic statement. Dynamically building a WHERE clause presents many more challenges than I will deal with here.

DECLARE
  -- ignore object types, LONG, LOB's etc.
  CURSOR cur_columns(p_table_name user_tab_columns.table_name%TYPE)
  IS
  SELECT utc.column_name, utc.data_type
  FROM user_tab_columns utc
  WHERE utc.table_name = p_table_name
    AND utc.data_type IN ('VARCHAR2','CHAR','DATE','NUMBER','FLOAT')
  ORDER BY utc.column_id
  ;
  v_sep VARCHAR2(1);
  v_column_list VARCHAR2(32767);
  v_date_format VARCHAR2(22) := 'DD-MON-YYYY'; -- whatever you want
  v_limit PLS_INTEGER := 10; -- for illustration, limit #rows returned

  arr_row nt_table_row := nt_table_row(); -- 2D array
  -- parallel arrays for display usage
  arr_column_name nt_column_value := nt_column_value();
  arr_data_type   nt_column_value := nt_column_value();
  v_table_name user_tables.table_name%TYPE := 'dept';
  v_stmt VARCHAR2(32767) :=
'declare
  c_date_format constant varchar2(22) := :f;
  c_num_rows constant pls_integer := :l;
  cursor cur is
  select
      nt_column_value(~c)
  from ~t
  where rownum <= c_num_rows
  ;
  arr_row nt_table_row := nt_table_row();
begin
  put_long_line(
          ''c_date_format=['' || c_date_format ||
          ''] c_num_rows=['' || c_num_rows ||
          '']'');
  open cur;
  fetch cur bulk collect into arr_row;
  close cur;
  :a := arr_row;
end;';
BEGIN
   -- build lists for dynamic block
   FOR rec IN cur_columns(UPPER(v_table_name)) LOOP
 --    v_column_expr :=
     v_column_list := v_column_list || v_sep ||
       (CASE 
        WHEN rec.data_type IN ('VARCHAR2','CHAR') THEN
           rec.column_name
        WHEN rec.data_type = 'DATE' THEN
           'to_char(' || rec.column_name || ',c_date_format)'
        WHEN rec.data_type IN ('NUMBER','FLOAT') THEN
           'to_char(' || rec.column_name || ')'
        ELSE rec.column_name
        END)
     ;
     -- these are just for display purposes
     arr_column_name.EXTEND;
     arr_column_name(arr_column_name.COUNT) := rec.column_name;
     arr_data_type.EXTEND;
     arr_data_type(arr_data_type.COUNT) := rec.data_type;
     v_sep := ',';
 END LOOP;

  IF (v_column_list IS NOT NULL) THEN
         -- build dynamic block
      v_stmt := REPLACE(v_stmt, '~c', v_column_list);
      v_stmt := REPLACE(v_stmt, '~t', v_table_name);
      
      put_long_line(v_stmt);
      EXECUTE IMMEDIATE v_stmt
      USING
       v_date_format
            ,v_limit
            ,OUT arr_row
          ;
    put_long_line('Display results...');
    IF (arr_row.count > 0) THEN
      FOR i IN arr_row.FIRST .. arr_row.LAST LOOP
        put_long_line('Row #' || i || ':');
        FOR j IN arr_row(i).FIRST .. arr_row(i).LAST LOOP
          put_long_line(
            arr_column_name(j) || '(' ||
            arr_data_type(j) || ')=[' ||
            arr_row(i)(j) || ']'
            );
        END LOOP;
      END LOOP;
    ELSE
      put_long_line('No data found in table ' || v_table_name);
    END IF;
   ELSE
    put_long_line('Table not found: [' || v_table_name || ']');
  END IF;
END;
/

Executing this program in the SCOTT schema returns the following output to the console. The dynamically constructed anonymous block is displayed, followed by the rows returned:

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).

How to generate and compile a very large PL/SQL package

I wrote a procedure a long time ago (put_long_line ) as a substitute for DBMS_OUTPUT.put_line, the latter which had a 255-byte line limit before 10g. Put_long_line handles strings up to 32K, breaking them up into multiple lines on whitespace, including any delimiters you choose to include (commas, for example).

Several years later, I needed some way to dynamically generate and compile a very large PL/SQL package. The Oracle-supplied package DBMS_SQL has an array form of parse() so that you can have a PL/SQL block up to 2³² lines, each 256 bytes long (matching the exposed package type DBMS_SQL.varchar2s, an index-by array of varchar2(256)). So I took put_long_line and retrofitted it to format a 32K string and populate an index-by array for parsing, instead of buffering it for printing.

This code uses the sample HR schema that comes with Oracle.

CREATE OR REPLACE PROCEDURE str2arr(
     p_source_string   IN VARCHAR2                      -- the source text string
     -------------------------------------------------------------------------
     -- p_output_array is the destination array, and is compatible with DBMS_SQL.parse
     --
     --   NOTE that DBMS_OUTPUT.put_line array has a 255 byte limit, but
     --   ---- DBMS_SQL.parse array has a 256 char limit.  We'll fix the line 
     --        limit at 255 for compatibility reasons.
     -------------------------------------------------------------------------
    ,p_output_array   IN OUT NOCOPY DBMS_SQL.varchar2s
     -------------------------------------------------------------------------
     -- p_array_element is the array element to start writing to
     --
     --   NOTE when the array is partially filled (provided you started with
     --   ---- offset=0) you can use array.COUNT to pick up at the subsequent 
     --        element.
     -------------------------------------------------------------------------
    ,p_array_element IN BINARY_INTEGER DEFAULT 0
     -------------------------------------------------------------------------
     -- p_line_length is the line limit, must be between 10 and 255 (the default)
     -------------------------------------------------------------------------
    ,p_line_length    IN NUMBER DEFAULT 255
     -------------------------------------------------------------------------
     -- p_whitespace is the set of delimiter chars to consider as whitespace
     --
     --   NOTE like put_long_line, actual whitespace (newline, space, tab) are
     --   ---- consumed when breaking up a line on a whitespace char, but any
     --        other char depicted as whitespace must actually be written.
     -------------------------------------------------------------------------
    ,p_whitespace   IN VARCHAR2 DEFAULT CHR(10)||CHR(9)||' ;,()=')
  IS
  ---------------------------------------------------------------------------
  -- Copy the contents of a string to an index-by array, in a manner similar 
  --     to put_long_line.
  --
  -- Lines are normally broken up on embedded newlines.  If one is not found
  --     before the requested end of line, the line is broken up on the last
  --     whitespace character found (as identified by the set of delimiter 
  --     characters.)
  ---------------------------------------------------------------------------

    c_newline CONSTANT VARCHAR2(1) := CHR(10);                 -- newline character (OS-independent)
    v_array_element BINARY_INTEGER := p_array_element;         -- used to increment array offset
    v_start_pos     INTEGER        := 1 ;                      -- start of string to print
    v_sub_end       INTEGER        ;                           -- end of substring to print
    v_str_end       INTEGER        := LENGTH(p_source_string); -- end of string to print
    v_newline_pos   INTEGER        ;                           -- point where newline found
    v_string_length INTEGER        := GREATEST(
                                               LEAST(p_line_length ,255 )
                                              , 10);           -- 10 <= len <= 255!
    
  BEGIN
    IF (v_str_end <= v_string_length) THEN  -- short enough to write in one chunk
      p_output_array(v_array_element) := p_source_string;
    ELSE  -- must break up string
        WHILE (v_start_pos <= v_str_end) LOOP
            v_newline_pos := INSTR(
                                     SUBSTR(p_source_string, v_start_pos, v_string_length)
                                   , c_newline
                                  )  
                           + v_start_pos - 1;
            ------------------------------------------------------------------------
            IF (v_newline_pos >= v_start_pos) THEN  -- found a newline to break on
                p_output_array(v_array_element) := SUBSTR( p_source_string
                                                         , v_start_pos
                                                         , v_newline_pos-v_start_pos
                                                         );
                v_array_element := v_array_element + 1;  -- next array offset
                v_start_pos     := v_newline_pos   + 1;  -- skip past newline
            ELSE  -- no newline exists in chunk; look for whitespace
                -- next chunk not EOS
                v_sub_end := LEAST(v_start_pos+v_string_length-1, v_str_end);
                ---------------------------------------------------------------
                IF (v_sub_end < v_str_end) THEN  -- intermediate chunk
                    FOR i IN REVERSE v_start_pos .. v_sub_end LOOP
                        ------------------------------------------------------------------
                        IF (INSTR( p_whitespace
                                 , SUBSTR( p_source_string, i , 1)
                                 ) != 0) THEN
                            v_sub_end := i;  -- found suitable breaking point
                            EXIT;  -- break out of loop
                        END IF;
                        ------------------------------------------------------------------
                    END LOOP;  -- find breaking point
                ELSE  -- this is the last chunk
                    v_sub_end := v_sub_end + 1;  -- point just past EOS
                END IF;  -- last chunk?
                ---------------------------------------------------------------
                p_output_array(v_array_element) := SUBSTR( p_source_string
                                                         , v_start_pos
                                                         , v_sub_end-v_start_pos+1);
                v_array_element := v_array_element + 1;  -- next array offset
                v_start_pos     := v_sub_end       + 1;  -- next chunk
            END IF;  -- find newline to break on
            ------------------------------------------------------------------------
        END LOOP;  -- writing chunks
    END IF;  -- short enough or break up string?
END str2arr;
/

I added an offset parameter so that on multiple calls, I can pick up where I left off in the array and concatenate in new rows as needed.

---

So for example, I can generate a package containing collection types based on all the tables in the current schema:

Declare
    v_array  DBMS_SQL.varchar2s;  -- index-by array to hold generated code
    v_cursor integer;             -- cursor handle

Begin
    v_cursor := DBMS_SQL.open_cursor;  -- DBMS_SQL cursors are reusable

    --+------------------------------------------------------------------
    --| Write the first line of the code to be generated to the array
    --| passing the first 3 parameters, and defaulting on the line length
    --| and the values For whitespace.
    --+------------------------------------------------------------------
    str2arr( 'create or replace package typs is ' --p_source_string
            , v_array                             --p_output_array
            , 0);                                 --p_array_element

    --+------------------------------------------------------------------
    --| Write a line of code to the array For each table in User_Tables. 
    --| Again, default on the line length and whitespace values.
    --+------------------------------------------------------------------
    For rec in (  select lower(table_name) table_name
                    from user_tables 
                order by table_name) 
    Loop
        str2arr( '  type typ_'   || rec.table_name || 
                 ' is table of ' || rec.table_name || 
                 '%rowtype;'                          --p_source_string
               , v_array                              --p_output_array
               , v_array.count);                      --p_array_element
    End Loop;  
    
    --+------------------------------------------------------------------
    --| Write the last line of the code to generate to the array.
    --+------------------------------------------------------------------
    str2arr( 'End typs;'                              --p_source_string
           , v_array                                  --p_output_array
           , v_array.count);                          --p_array_element

    --+------------------------------------------------------------------
    --| Display contents of array
    --+------------------------------------------------------------------
    For i in 0..v_array.last Loop
        dbms_output.put_line(v_array(i));
    End Loop;

    --+------------------------------------------------------------------
    --| The act of parsing DDL creates the object
    --+------------------------------------------------------------------
    DBMS_SQL.parse( v_cursor        -- the cursor in which to parse the statement
                  , v_array         -- Sql statement to parse
                  , 0               -- lower bound for elements
                  , v_array.last    -- upper bound for elements
                  , true            -- line feed after each element
                  , DBMS_SQL.native -- normal behavior for the database
                  );
    DBMS_SQL.close_cursor(v_cursor);
End;
/

This transformation of an old utility into a new one took 15 minutes. I leveraged it for an entirely new purpose. You can recycle old code!

Click here to read the Oracle documentation about
DBMS_SQL
which allows the use of dynamic SQL to parse DML or DDL statements.

Finding Circular References in a Table

Here's how to use a hierarchical query to detect circular references when inserting new rows into a self-referencing table.

If the entry causes a circular reference, I want to force a ROLLBACK.

PROCEDURE ...
IS
  e_infinite_loop EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);   -- CONNECT BY loop in user data 

  v_cnt PLS_INTEGER; 

BEGIN

  SAVEPOINT circ;

  INSERT INTO dependency_link
              (   parent_id,   child_id
              )
  VALUES      ( p_parent_id, p_child_id
              );
              
--+-----------------------------------------------------------
--| You need to actually traverse the tree in order to 
--| detect circular dependencies.
--+-----------------------------------------------------------

            SELECT COUNT (*) cnt
              INTO v_cnt
              FROM dependency_link dl
        START WITH dl.child_id  = p_child_id
  CONNECT BY PRIOR dl.parent_id = dl.child_id;

EXCEPTION
  WHEN e_infinite_loop THEN
    put_long_line ('Loop detected!');

    ROLLBACK TO SAVEPOINT circ;
...

Specifying Join Criteria in a Natural Join

Since 9i, the Natural Join feature joins tables on all columns of the same name. You don't need to specify join criteria in the WHERE clause. In the following example, both tables have keys named 'PK':

DROP TABLE MASTER;
CREATE TABLE MASTER (pk NUMBER(10), mstr VARCHAR2(100));
-- primary key only needed to ENFORCE data integrity
ALTER TABLE MASTER ADD PRIMARY KEY (pk);

DROP TABLE slave;
CREATE TABLE slave (pk NUMBER(10), sstr VARCHAR2(50));

-- primary key only needed to ENFORCE data integrity
ALTER TABLE slave ADD primary KEY (pk);
-- foreign only needed to ENFORCE referential integrity
--ALTER TABLE slave add foreign key (pk) REFERENCES MASTER (pk);
--ALTER TABLE slave DROP constraint SYS_C002722;

INSERT INTO MASTER VALUES (1, 'M1');
INSERT INTO MASTER VALUES (2, 'M2');
INSERT INTO MASTER VALUES (3, 'M3');

INSERT INTO SLAVE VALUES (1,'S1-1');
INSERT INTO SLAVE VALUES (2,'S2-2');
INSERT INTO SLAVE VALUES (3,'S3-3');

COMMIT;

SELECT *
FROM MASTER NATURAL JOIN SLAVE
--WHERE slave.sstr LIKE 'S2%';

Note we did NOT get a Cartesian product -- only those rows matching.

If I had named the string columns the same in both tables, the SQL engine would have tried to match rows by that column as well.