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

Oracle – Loading a BLOB from File

Here is a simple example of loading BLOB content from disk. First, the operating user will need a grant to enable the creation of an Oracle DIRECTORY object: GRANT CREATE ANY DIRECTORY TO testuser;

Next, the user will need to create the DIRECTORY object that points to the location of the source file on disk (in this case, C:\dbload\): CREATE OR REPLACE DIRECTORY test_dir AS 'C:\dbload';

Finally, below is an example of loading test_blob.dat from disk and inserting it into the test_blob_tab table:

SQL> DESC test_blob_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA                                               BLOB

SQL> DECLARE
  2
  3    insert_blob BLOB;
  4
  5    FUNCTION load_blob( filename VARCHAR2 )
  6    RETURN BLOB
  7    AS
  8      temp_blob BLOB;
  9      file BFILE;
 10      src_offset NUMBER DEFAULT 1;
 11      dest_offset NUMBER DEFAULT 1;
 12    BEGIN
 13
 14      DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION );
 15
 16      file := BFILENAME( 'TEST_DIR', filename );
 17      DBMS_LOB.OPEN( file, DBMS_LOB.LOB_READONLY );
 18
 19      DBMS_LOB.LOADBLOBFROMFILE( temp_blob, file,
 20        DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset );
 21
 22      DBMS_LOB.CLOSE( file );
 23
 24
 25      RETURN temp_blob;
 26
 27    END;
 28
 29  BEGIN
 30
 31    insert_blob := load_blob( 'test_blob.dat' );
 32    INSERT INTO test_blob_tab ( data ) VALUES ( insert_blob );
 33
 34  END;
 35  /

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.

Oracle – Working with NLS_DATE_FORMAT

In Oracle SQL, it can get a little tiring constantly using the TO_DATE() and TO_CHAR() functions when working with dates. NLS_DATE_FORMAT can be very handy when working with the DATE datatype. By setting a format for NLS_DATE_FORMAT, the string format for dates will be automatically handled. Let’s say we want to work with dates consistently in a ‘DD-MON-YYYY HH24:MI:SS' format:

SQL> desc test_tab;
 Name                  Null?    Type
 --------------------- -------- ----------------------------
 DATE_COL                       DATE

SQL> SELECT date_col FROM test_tab;

DATE_COL
---------
05-MAR-14

SQL> SELECT TO_CHAR( date_col, 'DD-MON-YYYY HH24:MI:SS' ) FROM test_tab;

TO_CHAR(DATE_COL,'DD-MON-YYYY
-----------------------------
05-MAR-2014 23:12:22

SQL> INSERT INTO test_tab VALUES ( '04-FEB-2013 03:21:11' );
INSERT INTO test_tab VALUES ( '04-FEB-2013 03:21:11' )
                              *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO test_tab
 2 VALUES ( TO_DATE( '04-FEB-2013 03:21:11', 'DD-MON-YYYY HH24:MI:SS' ) );

1 row created.

Without using TO_DATE() and TO_CHAR(), we are not able to work in our desired format. Let’s make life a little easier and use the ALTER SESSION SET NLS_DATE_FORMAT command:

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> SELECT date_col FROM test_tab;

DATE_COL
--------------------
05-MAR-2014 23:12:22

SQL> INSERT INTO test_tab
 2 VALUES ( '04-FEB-2013 03:21:11' );

1 row created.

To find what the current NLS_DATE_FORMAT string is, use
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT':

SQL> SELECT value
  2  FROM nls_session_parameters
  3  WHERE parameter = 'NLS_DATE_FORMAT';

VALUE
----------------------------------------------------------
DD-MON-YYYY HH24:MI:SS

Oracle – REPLACE() for CLOBs

In earlier versions of Oracle, the REPLACE() function did not work natively with CLOBs. By implicitly casting to VARCHAR2s, this function would work, but failed when a CLOB exceeded the maximum VARCHAR2 length. REPLACE() now works directly with CLOBs.

Although it is now antiquated, the function below is an example of one approach to perform a search and replace on a CLOB without actually using the REPLACE() function. It may be useful on older instances of Oracle, but is also a good example of some of the basic DBMS_LOB functions that are still commonly used. It is also a launch-point for more advanced processing.

As a side note, I have seen a number of functions that perform similarly to this function, by simply breaking the CLOB into a series of VARCHAR2 strings and performing a REPLACE() on each of the chunks. The issue with this approach is that it does not address boundary conditions when the search string is split between two of these chunks.

CREATE OR REPLACE
FUNCTION clob_replace( i_src CLOB, i_match VARCHAR2, i_rep VARCHAR2 )
RETURN CLOB
IS
  MAX_VARCHAR2 NUMBER DEFAULT 15000;  -- depending on your character set, 32767 may not be your actual number of available characters

  l_ret_clob CLOB;                    -- temporary CLOB to be returned
  l_next_idx NUMBER;                  -- used to traverse the source CLOB, captures the next instance of the match string
  l_search_begin NUMBER DEFAULT 1;    -- used to traverse the source CLOB, indicates the starting point of the next search
  l_parse_begin NUMBER DEFAULT 1;     -- used to copy date from the source CLOB, indicates the beginning of the copy window
  l_parse_end NUMBER;                 -- used to copy date from the source CLOB, indicates the beginning of the end window

BEGIN

  -- Create a temporary CLOB to be returned
  DBMS_LOB.CREATETEMPORARY( l_ret_clob, TRUE );

  LOOP

    l_next_idx := DBMS_LOB.INSTR( i_src, i_match, l_search_begin, 1 );

    -- Exit once we do not find another occurance of the match string
    EXIT WHEN l_next_idx = 0;

    -- Special handling if we find a match at the first character of the source string
    IF l_next_idx = 1 THEN

      l_parse_begin := LENGTH( i_match ) + 1;

      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

    ELSE
 
      -- Copy source data up to the match
      l_parse_end := l_next_idx - 1;
      DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );
      
      -- Replace the match string
      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

      -- Move the beginning of the next parse window past our match string
      l_parse_begin := l_parse_end + LENGTH( i_match ) + 1;

    END IF;

    -- Move the beginning of the search window past our match string
    l_search_begin := l_next_idx + LENGTH( i_match );

  END LOOP;


  -- Special handling for the trailing final characters of the source CLOB
  IF DBMS_LOB.GETLENGTH( i_src ) > ( l_parse_end  + LENGTH( i_match ) ) THEN

    l_parse_begin := l_parse_end  + LENGTH( i_match ) + 1;
    l_parse_end := DBMS_LOB.GETLENGTH( i_src );
    DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );

  END IF;

  RETURN l_ret_clob;

END;
/