1. Data Management Guide
  2. CLobs in CSV Files

CLobs in CSV Files

CSVUtil supports inserting, updating, and deleting CLobs. You can:

  • Include the CLob in the CSV file (each CLob<1Mb, no newline characters or quotes)
  • In the importing CSV file, refer to an external file holding the CLob. (no size restrictions on the CLobs, newline characters allowed). Note that CLOB columns are always exported to external files.
Note: CSVUtil can only handle one CLob per record. Also, the DB column must be of type CLOB; this will not work if the column type is VARCHAR or VARCHAR2, no matter how large the field is allowed to be.

Here is a sample CSV file that inserts a CLob using the in-line method:

CLOB_TEST
SEQ,DESCR,XML
9,"LINE1",<test1>test1</test1>
10,"LINE2",<test2>test2</test2>

In this case, the "XML" column is of type CLob. When using the in-line method, each CLob:

  • Must be specified on a single line (no newline characters).
  • Must be smaller than 1 megabyte.

Here is a sample CSV file that inserts two CLobs using the external file method:

CLOB_TEST
SEQ,DESCR,EXT_FNAME,XML
11,"LINE1",myxmlfile.xml
12,"LINE2",myxmlfile2.xml

When using the external file method, you must specify a special "pseudo column" called "EXT_FNAME ". The EXT_FNAME pseudo column must be specified immediately to the left of the CLob column. In this case, you will have an extra column on line 2. So, in this case, line 2 has 4 columns, but there are only 3 columns in the data lines.

The external file method must be used when inserting CLobs containing newline characters, or when inserting CLobs greater than 1 megabyte.

Note: To include the external files with the main CSV file, the ZIP encapsulation (described in more detail below) must be used so all the files may delivered to the Oracle Transportation Management server in a single HTTP transfer.