Oracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long strings

In a previous post, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, I wrote about long string concatenations exceeding the 32767 limit for VARCHAR2s. This article provided a query that limited the total length of the concatenation to cut itself off at 32767. This allows the user to use the advantages of LISTAGG() while returning as much data as possible.

The goal of this article is to provide an alternative that will make use of CLOBs to avoid character limits. Below is a user-defined aggregate function that is loosely based on askTom’s STRAGG() function. This function allows aggregation of very long strings, but note that this is an aggregate function, not an analytic function. It does not support the user-defined delimiter, WITHIN GROUP() clause and PARTITION BY clause that make LISTAGG() quite useful. The order in which strings are aggregated cannot be specified by the user.

SQL> CREATE OR REPLACE
  2  TYPE ClobAggregator
  3  AS OBJECT
  4  (
  5    aggregate_clob CLOB,
  6
  7    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  8      RETURN NUMBER,
  9    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 10      RETURN NUMBER,
 11    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number )
 12      RETURN NUMBER,
 13    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 14      RETURN NUMBER
 15  );
 16  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY ClobAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  6    RETURN NUMBER
  7    IS
  8      temp_clob CLOB;
  9    BEGIN
 10
 11      DBMS_LOB.CREATETEMPORARY( temp_clob, TRUE, DBMS_LOB.CALL );
 12
 13      sctx := ClobAggregator( temp_clob );
 14
 15      RETURN ODCIConst.Success;
 16
 17    END;
 18
 19    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 20    RETURN NUMBER
 21    IS
 22    BEGIN
 23
 24      IF DBMS_LOB.GETLENGTH( self.aggregate_clob ) > 0 THEN
 25        DBMS_LOB.APPEND( self.aggregate_clob, ',' );
 26      END IF;
 27
 28      DBMS_LOB.APPEND( self.aggregate_clob, value );
 29
 30      RETURN ODCIConst.Success;
 31
 32    END;
 33
 34    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number)
 35    RETURN NUMBER
 36    IS
 37    BEGIN
 38
 39      returnValue := self.aggregate_clob;
 40
 41      RETURN ODCIConst.Success;
 42
 43    END;
 44
 45    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 46    RETURN NUMBER
 47    IS
 48    BEGIN
 49
 50      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 51
 52      RETURN ODCIConst.Success;
 53
 54    END;
 55
 56  END;
 57  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION CLOBAGG( input CLOB )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING ClobAggregator;
  6  /

Function created.

Below is a small example demonstrating how the function aggregates. This function requires every row to be concatenated to an ongoing CLOB, so performance can be limited for large data sets.

SQL> SELECT CLOBAGG( value )
  2  FROM clob_test;

CLOBAGG(VALUE)
------------------------------------------------------------
Sally,Bob,James,Amelia,Sam,David,Phillip,Patrick,Scott,Bill

As a note of interest, STRAGG() was originally introduced to fill a void in string aggregation prior to the introduction of the LISTAGG() function. It gained widespread use throughout the Oracle community.

Oracle – Handling PL/SQL errors in a static and dynamic context

PL/SQL error handling is done using the EXCEPTION block of the PL/SQL procedure. The construct of this block is fairly straightforward and well documented. The intent of this article is not to explain this block, but to present two approaches to error handling. The first is a “static” approach, in which possible error codes are defined at compile time. This is the most commonly used approach. However, sometimes it is useful to define procedures that can be passed dynamic error codes to be handled. This is sometimes useful for library functions where a single block of code my raise different errors based on the conditions in the calling function. The calling function may pass in an error code that is expected into the function where it can be handled. The example below encapsulates both of these approaches:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2
  3    -- Raise an expected error
  4    PROCEDURE div_by_zero
  5    IS
  6      result NUMBER;
  7    BEGIN
  8      result := 1/0;
  9    END;
 10
 11    -- Handle an error statically
 12    PROCEDURE handle_static_error
 13    IS
 14      div_ex EXCEPTION;
 15
 16      PRAGMA EXCEPTION_INIT( div_ex, -1476 );
 17    BEGIN
 18      div_by_zero;
 19    EXCEPTION
 20      WHEN div_ex THEN
 21        DBMS_OUTPUT.PUT_LINE( 'Captured static divide by zero' );
 22    END;
 23
 24    -- Handle a passed-in error code
 25    PROCEDURE handle_dynamic_error( expected_ex NUMBER )
 26    IS
 27    BEGIN
 28      div_by_zero;
 29    EXCEPTION
 30      WHEN OTHERS THEN
 31        IF SQLCODE = expected_ex THEN
 32          DBMS_OUTPUT.PUT_LINE( 'Captured dynamic error, code='
 33            || expected_ex );
 34        ELSE
 35          RAISE;
 36        END IF;
 37    END;
 38
 39  BEGIN
 40
 41    handle_static_error;
 42    handle_dynamic_error( -1476 );
 43
 44  END;
 45  /
Captured static divide by zero
Captured dynamic error, code=-1476

PL/SQL procedure successfully completed.

The div_by_zero procedure exists only as an example error to be handled. The error code for divide by zero is -1476, which will be of interest in the following paragraphs. Let’s first focus on the handle_static_error procedure. As the name suggests, this function handles a static error code defined at compile time. In the declaration block of the procedure, note the div_ex EXCEPTION; and PRAGMA EXCEPTION_INIT( div_ex, -1476 ); statements. The first simply defines an EXCEPTION variable and gives it a name. The call to EXCEPTION_INIT is key in that it maps the error code of interest to our EXCEPTION variable. With this information in hand, it is time to look to the EXCEPTION block of the handle_static_error procedure. The WHEN div_ex THEN statement defines where the divide by zero error will be captured and handled. The DBMS_OUTPUT statement simply writes to the console as an error handler. Defining EXCEPTION variables like this is the most common approach to error handling.
The handle_dynamic_error procedure demonstrates an alternative approach to error handling. This procedure takes an error code as a parameter. In the EXCEPTION block of this procedure, note that no EXCEPTION variable is referenced. Instead, the IF SQLCODE = expected_ex THEN uses a a special PL/SQL function. SQLCODE returns the code of the most recently raised error, which is then compared to the error code passed in as a parameter to the procedure. If a match exists, the DBMS_OUTPUT line is called as the error handler.
Generally speaking, the use of EXCEPTION variables and the PRAGMA EXCEPTION_INIT statement is the preferable way to implement error handling. However, the above does provide an alternative when dynamic error codes are necessary.

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 – 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