Creating 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. The pathname 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 pathname.

field1|ROW('abcd', NULL|SET{1}|10|/work/photo.jpg|20|
   /work/text.txt

Figure 67 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).

Figure 68 shows a sample format for the file in Figure 67.

Figure 68. Delimited Format with Extended Data Type Entries

If you unload using a format that is similar to Figure 68, the unloaded data might resemble Figure 69.. You can use this data to load the same file again, instead of using the pathname.

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 Figure 69 will be written to /tmp. To change the default, modify the path in the PLOAD_LO_PATH environment variable.