Oracle PL/SQL – Exceptions and Native Dynamic SQL

EXECUTE IMMEDIATE statements act as separate sessions, so it is important to note that exception propagation from these statements also follows the same model. When an exception propagates beyond an EXECUTE IMMEDIATE entry point, a rollback is incurred. This may come as a surprise since normal procedure calls do not rollback, leaving transaction control in the hands of the programmer. Here is an example to demonstrate. First, some support objects:

SQL> CREATE TABLE test_tab
  2  (
  3    id NUMBER
  4  );

Table created.


SQL> CREATE OR REPLACE
  2  PROCEDURE error_proc
  3  IS
  4  BEGIN
  5
  6    INSERT INTO test_tab ( id )
  7    VALUES ( 1 );
  8
  9    RAISE_APPLICATION_ERROR( -20001, 'BOOM!' );
 10
 11  END;
 12  /

Procedure created.

Now, an example of exception propagation during a normal procedure call. Notice that, although the exception is thrown, the executed DML remains intact.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    error_proc;
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

        ID
----------
         1


SQL> ROLLBACK;

Rollback complete.

Finally, an almost identical example using Dynamic SQL. Notice that this time the DML is lost to a rollback incurred by the EXECUTE IMMEDIATE.

SQL> SELECT *
  2  FROM test_tab;

no rows selected


SQL> BEGIN
  2
  3    EXECUTE IMMEDIATE 'BEGIN error_proc; END';
  4
  5  EXCEPTION
  6    WHEN OTHERS THEN
  7      NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed.


SQL> SELECT *
  2  FROM test_tab;

no rows selected
Advertisements

Oracle – Writing a BLOB to an operating system file

Earlier, I wrote about reading a BLOB from file in an Oracle PL/SQL procedure. Here is an example of writing a BLOB out to file. Just as before, the user will need access to a DIRECTORY object that points to the OS file destination of choice. See the article on loading BLOBs for more information on this.

The write_blob_to_file procedure in the example below provides the functionality to write to file. It has been wrapped in an anonymous block to demonstrate usage:

SQL> DECLARE
  2
  3    temp_blob BLOB;
  4
  5    PROCEDURE write_blob_to_file( filename VARCHAR2, contents BLOB )
  6    AS
  7      begin_index NUMBER DEFAULT 1;
  8      read_size NUMBER;
  9      buffer RAW( 32767 );
 10      outfile UTL_FILE.FILE_TYPE;
 11    BEGIN
 12
 13      -- Open the output file
 14      outfile := UTL_FILE.FOPEN( 'TEST_DIR', filename, 'WB', 32767 );
 15
 16      -- Loop through the BLOB and keep writing in 32767 chunks
 17      WHILE begin_index <= DBMS_LOB.GETLENGTH( contents )
 18      LOOP
 19
 20        read_size := 32767;
 21        DBMS_LOB.READ( contents, read_size, begin_index, buffer );
 22        begin_index := begin_index + read_size;
 23
 24        UTL_FILE.PUT_RAW( outfile, buffer );
 25
 26      END LOOP;
 27
 28      -- Flush and close the output file
 29      UTL_FILE.FFLUSH( outfile );
 30      UTL_FILE.FCLOSE( outfile );
 31
 32    END;
 33
 34  BEGIN
 35
 36    DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION );
 37    DBMS_LOB.WRITE( temp_blob, 7, 1, UTL_RAW.CAST_TO_RAW( 'TESTING' ) );
 38
 39    write_blob_to_file( 'testout.dat', temp_blob );
 40
 41  END;
 42  /

PL/SQL procedure successfully completed.

Oracle PL/SQL – Treating collections as tables

It is possible to perform SQL manipulations on collections as if there are database tables. The TABLE() and CAST() functions can be used to accomplish this. This article will cover two ways to assemble the collection to be CASTed: PIPELINED functions and directly populating a collection.

To explore this concept, we’ll need a test object type and a nested table of those objects:

SQL> CREATE OR REPLACE
  2  TYPE person_tp IS OBJECT
  3  (
  4    first_name VARCHAR2( 100 ),
  5    last_name VARCHAR2( 100 ),
  6    age NUMBER
  7  );
  8  /

Type created.

SQL>
SQL> CREATE OR REPLACE
  2  TYPE person_table_tp AS TABLE OF person_tp;
  3  /

Type created.

Below are two example functions to generate some test data. The first is called a pipelined function. Pipelined functions efficiently assemble data into the specified return collection type without the need to maintain a collection object. The second function directly populates a collection and then returns it:

SQL> CREATE OR REPLACE
  2  FUNCTION create_person_list_pipe
  3  RETURN person_table_tp
  4  PIPELINED
  5  IS
  6    person PERSON_TP;
  7  BEGIN
  8    person := PERSON_TP( 'Robert', 'Smith', 43 );
  9    PIPE ROW( person );
 10    person := PERSON_TP( 'Jane', 'Jennings', 23 );
 11    PIPE ROW( person );
 12    person := PERSON_TP( 'Karen', 'Patreas', 51 );
 13    PIPE ROW( person );
 14  END;
 15  /

Function created.

SQL>
SQL> CREATE OR REPLACE
  2  FUNCTION create_person_list_array
  3  RETURN person_table_tp
  4  IS
  5    person PERSON_TP;
  6    person_tbl PERSON_TABLE_TP;
  7  BEGIN
  8    person_tbl := PERSON_TABLE_TP();
  9
 10    person := PERSON_TP( 'Robert', 'Smith', 43 );
 11    person_tbl.EXTEND();
 12    person_tbl( person_tbl.LAST ) := person;
 13
 14    person := PERSON_TP( 'Jane', 'Jennings', 23 );
 15    person_tbl.EXTEND();
 16    person_tbl( person_tbl.LAST ) := person;
 17
 18    person := PERSON_TP( 'Karen', 'Patreas', 51 );
 19    person_tbl.EXTEND();
 20    person_tbl( person_tbl.LAST ) := person;
 21
 22    RETURN person_tbl;
 23  END;
 24  /

Function created.

Both these functions return the same results:

SQL> SELECT create_person_list_pipe
  2  FROM dual;

CREATE_PERSON_LIST_PIPE(FIRST_NAME, LAST_NAME, AGE)
--------------------------------------------------------------------------------
PERSON_TABLE_TP(PERSON_TP('Robert', 'Smith', 43), PERSON_TP('Jane', 'Jennings',
23), PERSON_TP('Karen', 'Patreas', 51))

SQL> SELECT create_person_list_array
  2  FROM dual;

CREATE_PERSON_LIST_ARRAY(FIRST_NAME, LAST_NAME, AGE)
--------------------------------------------------------------------------------
PERSON_TABLE_TP(PERSON_TP('Robert', 'Smith', 43), PERSON_TP('Jane', 'Jennings',
23), PERSON_TP('Karen', 'Patreas', 51))

Finally, below is an anonymous block that makes use of the TABLE() and CAST() functions to perform various SQL manipulations on the collection:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    pipe_test_name VARCHAR2( 100 );
  3    array_test_name VARCHAR2( 100 );
  4    pipe_cnt NUMBER;
  5    array_cnt NUMBER;
  6  BEGIN
  7
  8    SELECT COUNT(*)
  9    INTO pipe_cnt
 10    FROM TABLE( CAST( create_person_list_pipe AS PERSON_TABLE_TP ) );
 11
 12    SELECT COUNT(*)
 13    INTO array_cnt
 14    FROM TABLE( CAST( create_person_list_array AS PERSON_TABLE_TP ) );
 15
 16    DBMS_OUTPUT.PUT_LINE( 'Pipe Count: ' || pipe_cnt );
 17    DBMS_OUTPUT.PUT_LINE( 'Array Count: ' || array_cnt );
 18
 19    SELECT first_name
 20    INTO pipe_test_name
 21    FROM TABLE( CAST( create_person_list_pipe AS PERSON_TABLE_TP ) )
 22    WHERE last_name = 'Jennings';
 23
 24    DBMS_OUTPUT.PUT_LINE( 'Pipe name test: ' || pipe_test_name );
 25
 26    SELECT first_name
 27    INTO array_test_name
 28    FROM TABLE( CAST( create_person_list_array AS PERSON_TABLE_TP ) )
 29    WHERE last_name = 'Patreas';
 30
 31    DBMS_OUTPUT.PUT_LINE( 'Array name test: ' || array_test_name );
 32
 33  END;
 34  /
Pipe Count: 3
Array Count: 3
Pipe name test: Jane
Array name test: Karen

PL/SQL procedure successfully completed.

Generating PL/SQL Scripts to Reproduce Table Data

When developing test scripts, it is sometimes convenient to have PL/SQL collections matching tables of interest so data can be repeatedly generated. For example, when developing unit tests for PL/SQL procedures, a collection can be used to load data into a table, mangle the data during testing, and then regenerated. Once a table is created and populated with the basic data for scripts, the following procedure can be used to easily generate a PL/SQL script to reproduce the data. The function parameters are:

p_table_owner = Schema of table of interest
p_table_name = Name of the table of interest
p_tbl_abbrev = A "root" used in naming the collection

CREATE OR REPLACE
PROCEDURE generate_array_insert( p_table_owner VARCHAR2, p_table_name VARCHAR2, p_tbl_abbrev VARCHAR2 )
IS
  TYPE column_tbl IS TABLE OF VARCHAR2( 200 );
  TYPE rowid_tbl IS TABLE OF ROWID;
 
  m_columns    COLUMN_TBL;
  m_datatypes  COLUMN_TBL;
  m_rowids     ROWID_TBL;
 
  m_nls_date_fmt   VARCHAR2( 200 );
  m_value          VARCHAR2( 32767 );
  m_output_line    VARCHAR2( 32767 );
  m_rowtype_tbl_tp VARCHAR2( 200 ) DEFAULT p_tbl_abbrev || '_array_t';
  m_rowtype_tbl    VARCHAR2( 200 ) DEFAULT 'g_' || p_tbl_abbrev || '_array';
  m_rowtype_rec    VARCHAR2( 200 ) DEFAULT 'm_temp_' || p_tbl_abbrev;
 
BEGIN
 
  DBMS_OUTPUT.PUT_LINE( 'DECLARE' );
  DBMS_OUTPUT.PUT_LINE( 'TYPE ' || m_rowtype_tbl_tp || ' IS TABLE OF ' || p_table_owner || '.' || p_table_name || '%ROWTYPE' || ';' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || ' ' || UPPER( m_rowtype_tbl_tp ) || ';' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_rec || ' ' || p_table_owner || '.' || p_table_name || '%ROWTYPE;' );
  DBMS_OUTPUT.PUT_LINE( 'BEGIN' );
  DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || ' := ' || m_rowtype_tbl_tp || '();' );

  SELECT value
  INTO m_nls_date_fmt
  FROM nls_session_parameters
  WHERE parameter = 'NLS_DATE_FORMAT';

  SELECT LOWER( column_name ), UPPER( data_type )
  BULK COLLECT INTO m_columns, m_datatypes
  FROM dba_tab_cols
  WHERE owner = p_table_owner
    AND table_name = p_table_name
    AND virtual_column = 'NO'
  ORDER BY column_id;
 
  EXECUTE IMMEDIATE 'SELECT rowid FROM ' || p_table_owner || '.' || p_table_name
  BULK COLLECT INTO m_rowids;
 
  FOR row_idx IN m_rowids.FIRST..m_rowids.LAST
  LOOP
   
    FOR col_idx IN m_columns.FIRST..m_columns.LAST
    LOOP
 
      EXECUTE IMMEDIATE
        'SELECT ' || m_columns( col_idx ) || ' ' ||
        'FROM ' || p_table_owner || '.' || p_table_name || ' ' ||
        'WHERE rowid = ''' || m_rowids( row_idx ) || ''''
      INTO m_value;
 
      m_output_line := m_rowtype_rec || '.' || m_columns( col_idx ) || '  :=  ' ||
        CASE
          WHEN m_datatypes( col_idx ) IN ( 'VARCHAR2', 'CHAR', 'CLOB', 'ROWID' ) THEN
            NVL( '''' || m_value || '''', 'NULL' )
          WHEN m_datatypes( col_idx ) IN ( 'DATE' ) THEN
            'TO_DATE( ''' || m_value || ''', ''' || m_nls_date_fmt || ''' )'
          ELSE
            NVL( m_value, 'NULL' )
        END || ';';
 
      DBMS_OUTPUT.PUT_LINE( m_output_line );

    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || '.EXTEND;' );
    DBMS_OUTPUT.PUT_LINE( m_rowtype_tbl || '(' || m_rowtype_tbl || '.LAST) := ' || m_rowtype_rec || ';' );   
 
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE( 'IF ' || m_rowtype_tbl || ' IS NOT EMPTY THEN' );
  DBMS_OUTPUT.PUT_LINE( 'FORALL i IN 1..' || m_rowtype_tbl || '.COUNT' );
  DBMS_OUTPUT.PUT_LINE( 'INSERT INTO ' || p_table_owner || '.' || p_table_name || ' VALUES ' || m_rowtype_tbl || '(i);' );
  DBMS_OUTPUT.PUT_LINE( 'END IF;' );
  DBMS_OUTPUT.PUT_LINE( 'END;' );
  DBMS_OUTPUT.PUT_LINE( '/' );
 
END;
/

This procedure will likely need some customization based on the datatypes and formatting of your data. Here is an example of output based on a test table:

SQL> desc testuser.test_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 COL1                                               VARCHAR2(50)
 COL2                                               DATE
 COL3                                               NUMBER


SQL> SET SERVEROUTPUT ON
SQL> select * from testuser.test_tab;
COL1                                               COL2            COL3
-------------------------------------------------- --------- ----------
First Column                                       04-FEB-13         12
Second Column                                      21-DEC-12         45


SQL> EXEC generate_array_insert( 'TESTUSER', 'TEST_TAB', 'source' );
DECLARE
TYPE source_array_t IS TABLE OF TESTUSER.TEST_TAB%ROWTYPE;
g_source_array SOURCE_ARRAY_T;
m_temp_source TESTUSER.TEST_TAB%ROWTYPE;
BEGIN
g_source_array := source_array_t();
m_temp_source.col1  :=  'First Column';
m_temp_source.col2  :=  TO_DATE( '04-FEB-2013', 'DD-MON-YYYY' );
m_temp_source.col3  :=  12;
g_source_array.EXTEND;
g_source_array(g_source_array.LAST) := m_temp_source;
m_temp_source.col1  :=  'Second Column';
m_temp_source.col2  :=  TO_DATE( '21-DEC-2012', 'DD-MON-YYYY' );
m_temp_source.col3  :=  45;
g_source_array.EXTEND;
g_source_array(g_source_array.LAST) := m_temp_source;
IF g_source_array IS NOT EMPTY THEN
FORALL i IN 1..g_source_array.COUNT
INSERT INTO TESTUSER.TEST_TAB VALUES g_source_array(i);
END IF;
END;
/

PL/SQL procedure successfully completed.

Here is the generated script with a little manual clean-up so it is more readable:

DECLARE
  TYPE source_array_t IS TABLE OF TESTUSER.TEST_TAB%ROWTYPE;

  g_source_array SOURCE_ARRAY_T;
  m_temp_source TESTUSER.TEST_TAB%ROWTYPE;
BEGIN
  g_source_array := source_array_t();

  -- Build the collection
  m_temp_source.col1  :=  'First Column';
  m_temp_source.col2  :=  TO_DATE( '04-FEB-2013', 'DD-MON-YYYY' );
  m_temp_source.col3  :=  12;
  g_source_array.EXTEND;
  g_source_array(g_source_array.LAST) := m_temp_source;

  m_temp_source.col1  :=  'Second Column';
  m_temp_source.col2  :=  TO_DATE( '21-DEC-2012', 'DD-MON-YYYY' );
  m_temp_source.col3  :=  45;
  g_source_array.EXTEND;
  g_source_array(g_source_array.LAST) := m_temp_source;

  -- Perform "tweaks" here
  -- Perform the inserts
  IF g_source_array IS NOT EMPTY THEN
    FORALL i IN 1..g_source_array.COUNT
      INSERT INTO TESTUSER.TEST_TAB VALUES g_source_array(i);
  END IF;

END;
/

This script generates a stand-alone anonymous block, but I usually separate the collection-building code from the inserts and place them in two separate functions. That way, for each test, the collection data can be adjusted before inserting.