Workaround for LOB datatype support

Resolving The Problem

The suggested work around is to save the LOB into a file and put a pointer to that file within the Netezza database. However, if a substring of the LOB is acceptable within the Netezza database, you can pull a portion of the LOB into a VARCHAR or NVARCHAR column.

When pulling data from an Oracle database, the use of the DBMS_LOB package will allow you to extract a portion of the LOB. Oracle limits VARCHAR2 types to 4,000 characters. Within PL/SQL the limit is higher, however the data will be coming outside of PL/SQL. To keep the value in character and not a LOB format, you must limit the extraction to 4,000 characters.

To return the first 4,000 characters from a LOB in Oracle using the DBMS_LOB package, use the SUBSTR function. The call would look similar to this:

SELECT ..., DBMS_LOB.SUBSTR(<col>, 4000, 1), ... FROM ....

Assuming the LOB being selected from is a character LOB (CLOB), the resulting column will be a VARCHAR2.

This method of extracting a portion of the LOB can be useful when loading data to Netezza using an ETL tool.