Non-Printable Characters

One of our vendors rejected a file we sent them because it had an non-printable ASCII character in it (one record out of tens of thousands). Once I tracked down the offending customer row from the file offset they provided, it seemed like a good idea to see what other similar data might also have non-printable characters embedded in them.

The printable and some non-printable characters are given in the ASCII table. The latter can vary by the character set in use (particularly the ones over 127d). You can find a number of these on the internet:

http://www.asciitable.com/

One way to detect non-printable characters is to strip them out of the string and compare that to the untranslated string; if they’re different, then I’ve found one.

I assembled a string of unprintable characters following a space. This is the source format string. The target format string only contains a space.

I also wanted to display the offset in the string of the offending character, and its decimal equivalent value.

DECLARE

  v_strip VARCHAR2(32767) := ' ';

  v_ascii INTEGER;

  CURSOR cur

  IS

    SELECT c.cust_num

          ,c.full_name

          ,TRANSLATE(c.full_name, v_strip, ' ') t_full_name

    FROM cust c

    WHERE TRANSLATE(c.full_name, v_strip, ' ') != c.full_name

    ORDER BY c.cust_num;

BEGIN

  -- list of non-printable characters

  FOR i IN 0 .. 31 LOOP

    v_strip := v_strip || CHR(i);

  END LOOP;

  FOR i IN 127 .. 255 LOOP

    v_strip := v_strip || CHR(i);

  END LOOP;

  -- display full names with embedded non-printable chars

  FOR rec IN cur LOOP

    DBMS_OUTPUT.put_line(   rec.cust_num

                         || ' ['

                         || rec.full_name

                         || ']['

                         || rec.t_full_name

                         || ']');

    FOR i IN 1 .. LENGTH(rec.full_name) LOOP

      v_ascii := ASCII(SUBSTR(rec.full_name, i, 1));

      IF (v_ascii <= 31 OR v_ascii >:= 127) THEN

        DBMS_OUTPUT.put_line('offset=' || i || ' [' || v_ascii || ']');

      END IF;

    END LOOP;

    DBMS_OUTPUT.put_line(' ');

  END LOOP;

END;

/

1009999380 [MARY ELLEN BROTH][MARY ELLEN  BROTH]

offset=12 [127]

1009999363 [ANAZZASIO¿¿¿        JOSEPH              ][ANAZZASIO        JOSEPH              ]

offset=10 [225]

offset=11 [225]

offset=12 [225]

1009999901 [SEWELL                PATRICK        R              ][SEWELL             PATRICK        R              ]

offset=7 [9]

1009999385 [WEINMAN             JACKIE         _              ][WEINMAN             JACKIE                       ]

offset=36 [0]

Various programming bugs in the source system probably accounts for these non-printable characters. For example, a NUL (null) character might have been embedded by an errant C program, that inadvertently included the string termination character in a memcpy call. The Backspace character might have been included when the data entry clerk tried to erase a misspelling, but the keyboard interpretation settings allowed the Backspace to get captured along with the rest of the string. 0x255 is a common maximum code used as a guard value. Most UI’s are programmed to jump to the next field on a Tab command, but that’s not always the case, as seen here.

Once the offending rows have been identified, they can be cleaned up via a batch program by simply stripping them out. Or perhaps human intervention is required to call up the customer and ask how to correctly spell their name!

Addendum to "Check for Existence" Article

Here is another use for the technique shown in Check for Existence.

A coworker recently asserted all of our tables have both a surrogate key (an otherwise meaningless numerical value obtained from a sequence object, used to uniquely identify a row) and a business key (or keys, typically one or more unique key constraints, each consisting of one or more columns, that uniquely identify a row based on meaningful values). To test his assertion, I looked for tables that have a primary key AND at least one unique key constraint.

Find Tables that have a Primary Key AND at least One Unique Key as well

SELECT iv.table_name, iv.constraint_list
FROM (SELECT ut.table_name
            ,(SELECT CAST(
                       MULTISET(SELECT uc.constraint_name
                                FROM user_constraints uc
                                WHERE uc.table_name = ut.table_name
                                AND   uc.constraint_type IN ('P', 'U')) AS varchar2_nt
                     )
              FROM DUAL)
               constraint_list
      FROM   user_tables ut
           INNER JOIN
             user_constraints uc2
           ON uc2.table_name = ut.table_name
      WHERE uc2.constraint_type = 'P') iv -- can be exactly 0 or 1 pkey per table
WHERE CARDINALITY(iv.constraint_list) > 1
ORDER BY iv.table_name;

Out of 540 tables in our schema, I found just 175 tables having both a surrogate and business key.

I also looked for tables that don't have either a unique or primary key defined. A unique key can fill in for a primary key, and even be used as the target for a foreign key constraint. I used this query to find such tables:

Tables that Don't have a Pkey or Ukey

SELECT table_name
FROM user_tables ut
WHERE NOT EXISTS
        (SELECT 1
         FROM user_constraints uc
         WHERE uc.table_name = ut.table_name
         AND   uc.constraint_type IN ('P', 'U'));

Turns out there are 13 tables that don't have a primary or unique key. These are our Global Temporary Tables, which by definition are small and contain extremely volatile data (goes away when the session closes).

Locking a Section of Code

Sometimes, it’s necessary to guarantee exclusive access to a section of code (sometimes referred to as a “region”), typically for the duration of a procedure or function call. This is often necessary to prevent a race condition from occurring between two or more sessions. For example, a procedure might need to query a table before modifying some of its rows. Because the query and subsequent DML statements occur in series, it’s conceivable that another session might come along in between and change the state of the row before the first session completes its mission. So to prevent such a situation from arising, we should lock the section of code that executes these two statements.

In PL/SQL, we can use the Oracle-supplied package DBMS_LOCK to provide this mechanism. When the section of code is locked, only one session is guaranteed to be in that code section; all other sessions are queued up. When the session holding the lock performs a COMMIT or ROLLBACK, the lock is released, and the next session in the queue gains access.

Examining the Oracle documentation for DBMS_LOCK.request()

We see there are some important things to take note.

First, we need to obtain a lock handle via DBMS_LOCK.allocate_unique. My personal preference is to provide a lock name that’s the same as the routine. It needs to be unique anyway and it’s a handy way to associate the lock with the routine.

 c_lock_name CONSTANT VARCHAR2(30) := 'addorget_cust';

In all of my stored subprograms that do locking, you’ll always find the same variable or constant name used. Consistency makes it that much easier to maintain these programs.

Now we just need to get a lock handle and store it locally. It’ll be used when making the lock request.

 v_lock_handle VARCHAR2(128);
…
  DBMS_LOCK.allocate_unique(c_lock_name, v_lock_handle);

Now we’re about ready to request a lock; but first we have some important design decisions to make. Here is the signature for the packaged function DBMS_LOCK.request:

DBMS_LOCK.REQUEST(
   id                 IN  INTEGER ||
   lockhandle         IN  VARCHAR2,
   lockmode           IN  INTEGER DEFAULT X_MODE,
   timeout            IN  INTEGER DEFAULT MAXWAIT,
   release_on_commit  IN  BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

We do wish to obtain an exclusive lock. But it’s rather a bad idea to “wait forever” (the package constant maxwait waits forever) on a lock request. Frankly, if we can’t get a lock within just a few seconds, we’ve got bigger design problems. The whole point to a code section lock is to get in and get out of it as fast as possible. Locks produce waits, and the last thing we wish to do is slow down the entire system. So if we don’t get the lock pretty quickly, we’ll need a way to go to the end of the queue and retry – up to a point (again, not wishing to find ourselves in a long-running loop). The “contract” all of these stored procedures share in common is they will release the lock as soon as possible, by performing a COMMIT or ROLLBACK.

So I’m going to need some more variables to track what’s going on with my lock request:

   -- locking parameters
    c_timeout CONSTANT INTEGER := 2; -- seconds
    c_max_loop_counter CONSTANT PLS_INTEGER := 5; -- sanity check
    v_req_ret INTEGER := -1; -- initialized to meaningless non-zero/null value
    v_loop_counter PLS_INTEGER := 0; -- # of retries
    v_count INTEGER := 0;

If my request fails, I’ll retry (in a loop), up to a point. After that, I’m going to bail (and in all of the production code I’ve ever used this in, I’ve never stressed it enough to require more than a single retry – but that’s the way to code it).

Now I’m ready to make my lock request:

     v_req_ret :=
        DBMS_LOCK.request(v_lock_handle
                         ,timeout => c_timeout
                         ,release_on_commit => TRUE
                         );

Note I start off using Positional Notation, and quickly switch to Named Notation so I can skip the lockmode parameter (accepting its default value).

The documentation describes the request’s return value:

0	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Don't own lock specified by id or lockhandle
5	Illegal lock handle

I will have to deal with each scenario:

     CASE v_req_ret
        WHEN 0
        THEN
          NULL; -- success!
        WHEN 1
        THEN -- timeout; retry up to max times
          v_loop_counter := v_loop_counter + 1;
        WHEN 2
        THEN -- deadlock
          ROLLBACK;
          v_loop_counter := v_loop_counter + 1;
        WHEN 3
        THEN -- parameter error; bail
          raise_application_error(-20002, 'Lock failed: parameter error.');
        WHEN 4
        THEN -- already own lock specified by id or lockhandle
          v_req_ret := 0; -- success from a previous run (should never happen)
        WHEN 5
        THEN -- illegal lock handle (allocate failed)
          raise_application_error(-20002
                                 ,'Lock failed: illegal lock handle.'
                                 );
        ELSE
          raise_application_error(
            -20002
           ,'Lock failed: unknown error [' || v_req_ret || '].'
          );
      END CASE;

I’ll do this in a loop until the request return code is zero (meaning success), up to c_max_loop_counter times.

Now quite frankly, the last thing I should do is have code like this repeated in a number of like-minded routines. The code is intended to be tightly integrated. Therefore, it should be in its own stored procedure. Then my routines that need locking can do so with a single procedure call, so long as they all adhere to the defined contract – get the lock, do your thing, then release it as soon as you can. Here is my completed stored (actually packaged) procedure, obtain_lock:

 -- obtain a lock for this region of code.
  -- release the lock with a commit or rollback.
  PROCEDURE obtain_lock(p_lockname IN VARCHAR2)
  IS
    -- locking parameters
    c_timeout CONSTANT INTEGER := 2; -- seconds
    c_max_loop_counter CONSTANT PLS_INTEGER := 5; -- sanity check
    v_lock_handle VARCHAR2(128);
    v_req_ret INTEGER := -1; -- initialized to meaningless non-zero/null value
    v_loop_counter PLS_INTEGER := 0; -- # of retries
    v_count INTEGER := 0;
  BEGIN
    -- lock section of code
    DBMS_LOCK.allocate_unique(p_lockname, v_lock_handle);

    WHILE (v_req_ret != 0) LOOP
      v_req_ret :=
        DBMS_LOCK.request(v_lock_handle
                         ,timeout => c_timeout
                         ,release_on_commit => TRUE
                         );

      CASE v_req_ret
        WHEN 0
        THEN
          NULL; -- success!
        WHEN 1
        THEN -- timeout; retry up to max times
          v_loop_counter := v_loop_counter + 1;
        WHEN 2
        THEN -- deadlock
          ROLLBACK;
          v_loop_counter := v_loop_counter + 1;
        WHEN 3
        THEN -- parameter error; bail
          raise_application_error(-20002, 'Lock failed: parameter error.');
        WHEN 4
        THEN -- already own lock specified by id or lockhandle
          v_req_ret := 0; -- success from a previous run (should never happen)
        WHEN 5
        THEN -- illegal lock handle (allocate failed)
          raise_application_error(-20002
                                 ,'Lock failed: illegal lock handle.'
                                 );
        ELSE
          raise_application_error(
            -20002
           ,'Lock failed: unknown error [' || v_req_ret || '].'
          );
      END CASE;

      IF (v_loop_counter > 0)
      THEN -- need to retry acquiring lock
        put_long_line(   'Lock not acquired with message #'
                      || v_req_ret
                      || '. This is retry #'
                      || v_loop_counter);
        v_req_ret := -1; -- reset

        IF (v_loop_counter = c_max_loop_counter)
        THEN
          raise_application_error(
            -20001
           ,'Failed ' || c_max_loop_counter || ' retries to get lock.'
          );
        END IF;
      END IF;
    END LOOP;
  -- got the lock; this is the protected code region
  END obtain_lock;

This really simplifies the use of this feature. For example:

-- given the customer name,
-- looks up the customer ID and returns it.
-- if none is found, it creates one.
CREATE OR REPLACE PROCEDURE addorget_cust(
  p_cust_name IN customer.cust_name%TYPE
 ,p_cust_id   OUT customer.cust_id%TYPE
)
IS
  c_lock_name CONSTANT VARCHAR2(30) := 'addorget_cust';
BEGIN
  obtain_lock(c_lock_name);

  BEGIN
    SELECT c.cust_id
    INTO p_cust_id
    FROM customer c
    WHERE c.cust_name = p_cust_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN -- OK to add
      INSERT INTO customer(cust_name)
      VALUES (p_cust_name)
      RETURNING cust_id
      INTO p_cust_id;
  END;

  COMMIT;  -- release the lock
END addorget_cust;
/

Short and sweet.

Daydreaming

                Daydreaming is the act of mentally removing yourself from your present surroundings. While your body sits and lingers before a desk or typewriter, your mind wanders through the universe, transgressing the borders of time, space and reality.

                Daydreaming has historical precedence. Were it not for Julius Caesar's dreams of the conquest of foreign lands the Roman Empire would not have reached to the farthest corners of the Known World. Albert Einstein's theories began as a daydream of young Albert riding a beam of light through space. In fact, some of our civilization's greatest advances in engineering, medicine, and physics began with the conscious dream.

                Imagination is the key to creative dreaming. With even an average imagination, you can go anywhere, do anything – overcome anyone! Be courageous! Be daring! All from the relative safety of your armchair.

                There are good reasons for pursuing daydream activity. It allows you to escape the boredom and drudgery of a repetitive task for a few minutes, releasing the tension and stress that build up naturally. During a daydream, the pressures and worries of daily living are forgotten. Released from these distractions your mind pursues your work with renewed vigor.

                Your creative talent needs a daily workout. By flexing your mental muscles, your capacity to handle complex problems becomes greater. Your thinking becomes more resilient, continually seeking possible solutions, regardless of how bizarre many may seem. By giving flight to your fancy the improbable becomes possible. An intuitive leap may yield a new solution. These are the rewards for a few minutes spent daydreaming.

                The ability to daydream comes naturally. First you should get comfortable. Take off that stuffy jacket or sweater. Next, relax your body. Free your mind of the problems at hand. Tune out the world and tune into yourself, your feelings. Finally, let your imagination roam. Associate freely various thoughts and moods. Soon your mind will be exploring the deeper, subtle meanings behind the ordinary world we tend to take for granted.

                Picture yourself in your new, bright red '84 Corvette, screaming up the highway toward northern Maine, where the moose hunting is best. Perhaps you've wondered what parachuting into the Super Bowl during half-time would be like. Take that jump! Or, you may enjoy a leisurely space walk with your astronaut buddies. Your imagination knows no bounds.

                Now you know what daydreaming is all about. What are you waiting for?! Loosen that tie and kick off your shoes! Now, give your imagination a good workout!

Check for the Existence of a Particular Set of Column Names

How do I check for the existence of a particular set of column names amongst all tables?
It's straight-forward how to find one column name:

SELECT utc.table_name
FROM user_tab_columns utc
WHERE utc.column_name = 'FUND_ID'
ORDER BY utc.table_name;

TABLE_NAME
------------------------------
ANNUITY_ALLOCATION
ANNUITY_FEE
ASSIGNMENT
BENCHMARK_PROCESSING
BENCHMARK_WEIGHT
CATEGORY_LINK
COLLECTION_LINK
COMPOSITE_LINK
DAILY_ASSET
DAILY_PROFILE
DAILY_PROFILE_EXTRA
...

If I need to find two such column names, I suppose I could do a self-join:

SELECT t1.table_name, t1.column_name, t2.column_name
FROM   user_tab_columns t1
     INNER JOIN
       user_tab_columns t2
     ON t1.table_name = t2.table_name
WHERE t1.column_name = 'FUND_ID'
AND   t2.column_name = 'SECURITY_ID'
ORDER BY t1.table_name;

TABLE_NAME                     COLUMN_NAME          COLUMN_NAME
------------------------------ -------------------- --------------------
EXCEPTION_STORE                FUND_ID              SECURITY_ID
SECURITY                       FUND_ID              SECURITY_ID
V_DATA_EXCEPTION               FUND_ID              SECURITY_ID
V_HOLDING_RATE                 FUND_ID              SECURITY_ID
V_HOLDING_REPORT               FUND_ID              SECURITY_ID
V_TRANS_REPORT                 FUND_ID              SECURITY_ID

But how I do extend this to 3, 4 or 5 columns? Simply adding self-joins doesn't seem to scale particularly well.

What I really need is to obtain the set of all column names as a single column per table name, then somehow cross-reference those against a set of desired column names.

Fortunately, Oracle supports the concept of a multiset. This operator allows me to quickly perform set operations between two sets.

To do this, I first need a schema-level collection type:

CREATE OR REPLACE TYPE varchar2_nt IS TABLE OF VARCHAR2(4000);
/

4,000 bytes of course being the maximum allowable string size in SQL.

Now, how do I get the set of all column names for each table? Like this:

SELECT ut.table_name
      ,(SELECT CAST(
                 MULTISET(SELECT utc.column_name
                          FROM user_tab_columns utc
                          WHERE utc.table_name = ut.table_name) AS varchar2_nt
               )
        FROM DUAL)
         column_list
FROM user_tables ut
ORDER BY ut.table_name;

TABLE_NAME                     COLUMN_LIST
------------------------------ ----------------------------------------
ACCOUNT                        VARCHAR2_NT('IS_NONAGS', 'ACCOUNT_ID', '
                               EXTERNAL_ID', 'NAME', 'DESCRIPTION', 'AC
                               COUNT_CATEGORY', 'RECONCILIATION_RULE',
                               'PERIOD_BASED', 'FORCE_SHARE_CLASS')

ACCOUNT_GROUP                  VARCHAR2_NT('ACCOUNT_GROUP_ID', 'GROUP_N
                               AME')

ACCOUNT_GROUP_LINK             VARCHAR2_NT('ACCOUNT_GROUP_ID', 'ACCOUNT
                               _ID', 'SORT_ORDER')

The input to MULTISET in this situation is a query. If the query returns no rows, you get an empty collection. The subquery-in-a-column is called a scalar subquery. It must return precisely one value (or NULL). The collection of column names is that one value!

Now that I have a collection of column names per table, how can I scan it for one, two or more column names all at once?

I can perform the intersection of two such collections. The constructor for my schema-level type can be used to provide a hard-coded list, for example:

varchar2_nt('FUND_ID', 'SECURITY_ID', 'END_DATE')

Now I can do a MULTISET INTERSECT on this set and each column name set:

SELECT ut.table_name
      ,(SELECT CAST(
                 MULTISET(SELECT utc.column_name
                          FROM user_tab_columns utc
                          WHERE utc.table_name = ut.table_name) AS varchar2_nt
               )
        FROM DUAL)
         MULTISET INTERSECT varchar2_nt(
                              'FUND_ID'
                             ,'SECURITY_ID'
                             ,'END_DATE'
                            )
         column_list
FROM user_tables ut
ORDER BY ut.table_name;

TABLE_NAME                     COLUMN_LIST
------------------------------ ----------------------------------------
ACCOUNT                        VARCHAR2_NT()
ACCOUNT_GROUP                  VARCHAR2_NT()
ACCOUNT_GROUP_LINK             VARCHAR2_NT()
ACCOUNT_MAP                    VARCHAR2_NT()
ACCOUNT_SUMMARY                VARCHAR2_NT()
ACCOUNT_VECTORS                VARCHAR2_NT()
ACCOUNT_VIEW                   VARCHAR2_NT()
ALERT                          VARCHAR2_NT()
ALERT_USER                     VARCHAR2_NT()
ANNUITY_ALLOCATION             VARCHAR2_NT('FUND_ID')
ANNUITY_FEE                    VARCHAR2_NT('FUND_ID')

This unfortunately gives me an empty collection for those tables without any of these columns, and partial results for 1, 2, or 3 columns. I need some way to discern the fully populated collections from the rest.

For this, I can use the CARDINALITY function. I want only those tables that have all three columns:

SELECT *
FROM (SELECT ut.table_name
            ,(SELECT CARDINALITY(
                       CAST(
                         MULTISET(SELECT utc.column_name
                                  FROM user_tab_columns utc
                                  WHERE utc.table_name = ut.table_name) AS varchar2_nt
                       )
                         MULTISET INTERSECT varchar2_nt(
                                              'PORTFOLIO_ID'
                                             ,'SECURITY_ID'
                                             ,'END_DATE'
                                            )
                     )
              FROM DUAL)
               num_columns
      FROM user_tables ut) t
WHERE t.num_columns = 3
ORDER BY t.table_name;

TABLE_NAME                     NUM_COLUMNS
------------------------------ -----------
SECURITY_ENHANCEMENT                     3

If I were doing this in a PL/SQL block, I'd avoid the hard-coded constant by defining the collection once:

DECLARE
  arr_collist varchar2_nt
    := varchar2_nt(
         'PORTFOLIO_ID'
        ,'SECURITY_ID'
        ,'END_DATE'
       );
BEGIN
  FOR rec
    IN (SELECT *
        FROM (SELECT ut.table_name
                    ,(SELECT CARDINALITY(
                               CAST(
                                 MULTISET(
                                   SELECT utc.column_name
                                   FROM user_tab_columns utc
                                   WHERE utc.table_name = ut.table_name
                                 ) AS varchar2_nt
                               )
                                 MULTISET INTERSECT arr_collist
                             )
                      FROM DUAL)
                       num_columns
              FROM user_tables ut) t
        WHERE t.num_columns = CARDINALITY(arr_collist)
        ORDER BY t.table_name) LOOP
    DBMS_OUTPUT.put_line(rec.table_name);
  END LOOP;
END;
/
SECURITY_ENHANCEMENT

Now suppose I want to see which tables share the most number of common column names. I can take the intersection of the self-join of all column lists and compare each table's column list to one another; then sort by the number of common column names.

SELECT table_name1
      ,table_name2
      ,(column_list1 MULTISET INTERSECT column_list2) column_list3
      ,CARDINALITY(column_list1 MULTISET INTERSECT column_list2) card
FROM (SELECT ut1.table_name table_name1
            ,(SELECT CAST(
                       MULTISET(SELECT utc.column_name
                                FROM user_tab_columns utc
                                WHERE utc.table_name = ut1.table_name) AS varchar2_nt
                     )
              FROM DUAL)
               column_list1
            ,t2.table_name2
            ,t2.column_list2
      FROM   user_tables ut1
           INNER JOIN
             (SELECT ut2.table_name table_name2
                    ,(SELECT CAST(
                               MULTISET(
                                 SELECT utc.column_name
                                 FROM user_tab_columns utc
                                 WHERE utc.table_name = ut2.table_name
                               ) AS varchar2_nt
                             )
                      FROM DUAL)
                       column_list2
              FROM user_tables ut2) t2
           ON ut1.table_name != t2.table_name2)
WHERE CARDINALITY((column_list1 MULTISET INTERSECT column_list2)) > 0
ORDER BY card DESC;

TABLE_NAME1                    TABLE_NAME2
------------------------------ ------------------------------
COLUMN_LIST3
--------------------------------------------------------------------------------
      CARD
----------
MONTHLY_RETURN                 MONTHLY_RETURN_MATRIX
VARCHAR2_NT('FUND_ID', 'PROFILE_DATE', 'ROR_1M_C_NOLOAD', 'ROR_1M_G_NOLOAD', 'RO
R_2M_C_NOLOAD', 'ROR_3M_C_NOLOAD', 'ROR_4M_C_NOLOAD', 'ROR_5M_C_NOLOAD', 'ROR_6M
_C_NOLOAD', 'ROR_7M_C_NOLOAD', 'ROR_8M_C_NOLOAD', 'ROR_9M_C_NOLOAD', 'ROR_10M_C_
NOLOAD', 'ROR_11M_C_NOLOAD', 'ROR_YTD_C_NOLOAD', 'ROR_YTD_G_NOLOAD', 'ROR_1Y_C_N
OLOAD', 'ROR_2Y_A_NOLOAD', 'ROR_2Y_C_NOLOAD', 'ROR_3Y_A_NOLOAD', 'ROR_3Y_C_NOLOA
D', 'ROR_4Y_A_NOLOAD', 'ROR_4Y_C_NOLOAD', 'ROR_5Y_A_NOLOAD', 'ROR_5Y_C_NOLOAD',
'ROR_6Y_A_NOLOAD', 'ROR_7Y_A_NOLOAD', 'ROR_8Y_A_NOLOAD', 'ROR_9Y_A_NOLOAD', 'ROR
_10Y_A_NOLOAD', 'ROR_10Y_C_NOLOAD', 'ROR_15Y_C_NOLOAD', 'ROR_15Y_A_NOLOAD', 'ROR
_20Y_C_NOLOAD', 'ROR_20Y_A_NOLOAD', 'ROR_25Y_C_NOLOAD', 'ROR_25Y_A_NOLOAD', 'ROR
_INCP_C_NOLOAD', 'ROR_INCP_A_NOLOAD', 'ROR_ADV_INCP_C_NOLOAD', 'ROR_ADV_INCP_A_N
OLOAD', 'ROR_USER_INCP_C_NOLOAD', 'ROR_USER_INCP_A_NOLOAD', 'ROR_PERF_INCP_C_NOL
OAD', 'ROR_PERF_INCP_A_NOLOAD', 'ROR_1M_C_LOAD', 'ROR_2M_C_LOAD', 'ROR_3M_C_LOAD
', 'ROR_4M_C_LOAD', 'ROR_5M_C_LOAD', 'ROR_6M_C_LOAD', 'ROR_7M_C_LOAD', 'ROR_8M_C
_LOAD', 'ROR_9M_C_LOAD', 'ROR_10M_C_LOAD', 'ROR_11M_C_LOAD', 'ROR_YTD_C_LOAD', '
ROR_1Y_C_LOAD', 'ROR_2Y_A_LOAD', 'ROR_2Y_C_LOAD', 'ROR_3Y_A_LOAD', 'ROR_3Y_C_LOA
D', 'ROR_4Y_A_LOAD', 'ROR_4Y_C_LOAD', 'ROR_5Y_A_LOAD', 'ROR_5Y_C_LOAD', 'ROR_6Y_
A_LOAD', 'ROR_7Y_A_LOAD', 'ROR_8Y_A_LOAD', 'ROR_9Y_A_LOAD', 'ROR_10Y_A_LOAD', 'R
OR_10Y_C_LOAD', 'ROR_15Y_C_LOAD', 'ROR_15Y_A_LOAD', 'ROR_20Y_C_LOAD', 'ROR_20Y_A
_LOAD', 'ROR_25Y_C_LOAD', 'ROR_25Y_A_LOAD', 'ROR_INCP_C_LOAD', 'ROR_INCP_A_LOAD'
, 'ROR_ADV_INCP_C_LOAD', 'ROR_ADV_INCP_A_LOAD', 'ROR_USER_INCP_C_LOAD', 'ROR_USE
R_INCP_A_LOAD', 'ROR_PERF_INCP_C_LOAD', 'ROR_PERF_INCP_A_LOAD', 'RECORD_GUID', '
CREATORDB_ID', 'REVISION_NBR', 'GENERATION_ID', 'REFERENCE_COUNT', 'SYNC_SESSION
_ID')
        92

I do get the same pair of tables listed twice, first as table_name1-table_name2 and then as table_name2-table_name1.

I can also use the MULTISET and related operators and functions directly in assignment statements in PL/SQL. For example:

DECLARE
  x varchar2_nt := varchar2_nt();
BEGIN
  x :=
    varchar2_nt(
      'a'
     ,'b'
     ,'b'
     ,'c'
    )
      MULTISET INTERSECT DISTINCT varchar2_nt(
                                    'b'
                                   ,'c'
                                   ,'c'
                                   ,'d'
                                  );

  IF (x.COUNT > 0) THEN
    DBMS_OUTPUT.put_line('There are ' || x.COUNT || ' common elements:');

    FOR i IN x.FIRST .. x.LAST LOOP
      DBMS_OUTPUT.put_line(x(i));
    END LOOP;
  ELSE
    DBMS_OUTPUT.put_line('There are no common elements');
  END IF;
END;
/

There are 2 common elements:

b
c

If you play around with this, you'll see the intersection of a non-empty set and an empty set is an empty set.

In addition to INTERSECT [DISTINCT], there's UNION [DISTINCT] and EXCEPT [DISTINCT].

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: