Create a delimited format with extended data types

Extended data types include the following data types:
  • User defined (distinct and opaque)
  • Collection
  • CLOB and BLOB
  • Row

In a delimited format, CLOB and BLOB data is always written to a file. CLOB data can be ASCII or hexadecimal data. BLOB data can be binary data. The path name of the file, the file size, and the offset are embedded in the data file during unload. However, when you perform a load, you only need to specify the path name.

The following sample data-file record shows a data record that has a field of character data (field1), a row-type field (ROW('abcd', NULL)), a collection-type field (SET{1}), an integer field (10), a BLOB-type field (/work/data/photo.jpg), an integer field (20), and a CLOB-type field (work/data/text.txt). 
field1|ROW(abcd, NULL|SET{1}|10|/work/photo.jpg|20|
/work/text.txt
The following figure shows a sample format for the sample data-file record that was previously illustrated.
Figure 1. Delimited format with extended data type entries
begin figure description - This figure is described in the surrounding text. - end figure description
If you unload by using a format that is similar to Delimited format with extended data type entries , the unloaded data might resemble the following sample data-file record. You can use this data to load the same file again, instead of using the path name.
Figure 2. Sample data-file record that includes extended data types
field1|ROW(abcd, NULL|SET{1 }|10|0,51f4,blob67e9.8ac|20|
0,c 692,clob67e9.9ad

By default, the clob67e9.8ad and blob67e9.8ac files in Sample data-file record that includes extended data types are written to /tmp. To change the default, modify the path in the PLOAD_LO_PATH environment variable.