Exporting LOBs

If your table contains LOBs, AQT has options for exporting these in a useful way. These options are specified on the Advanced tab.

This is available for Delimited Files, Inserts, Excel and XML export formats.

When Write LOBs to files is checked, AQT will export the LOB values as files, and embed links in the export file to these BLOB files.

• for HTML and Excel exports, the export file will have hyperlinks to the LOB files.
• for XML export, only BLOBs (and not CLOBs) will be exported (the XML specification we follow only allows BLOBs to be exported to file). When this is specified, the attribute values will contain a reference to the LOB file. If Write BLOBs to files is not checked, the LOB values are encoded in Base64 and included in the export file. For large BLOBs (many megabytes) this can be slow, and can result in a very large export file.
• for Export as Insert statements, the LOB files are referenced as parameters. This is the format used by AQT for loading LOB values; the export file can therefore be used to load the LOBs into another table. However only AQT can be used for loading the LOBs like this; the export file cannot be processed by another script processor.

File extension

In order to hyperlink to a LOB file, you need to specify the file extension for the LOB files. For instance, if your LOBs were images, you would specify a file extension of jpg.

LOB file management

LOBs files are handled differently for Export as for a normal table display. For a normal table display, the LOB files are held in a temporary directory and automatically deleted when the display window is closed.

For Export, you do not want the LOB files deleted but may wish to retain them for a period of time. As a result, AQT will not delete the LOB files after the export has completed, or when AQT is closed. The LOB files will remain permanently on your disk until manually deleted by yourself, or overwritten by another export.

To give you more control of organising and managing the LOB files, Export allows you to specify the Sub Directory and the File Prefix.

• the Sub Directory is a subdirectory from the directory used for the export file. By default, the Sub Directory is files, however you may change this to a more meaningful name.
• the File Prefix gives the first part of the name of LOB file. By default this is the name of the table being exported. In this way, AQT will generate fairly unique names for the LOB files, reducing the chance that an export may override the LOB files from a different export.
• the File Prefix may contain either <schema> and/or <table>. The table schema and name will be substituted for these.

Delimited-File Export

For a delimited-file export, the Write LOBs to files option is only meaningful when the file is going to be used as input to the Oracle SQL Loader. In this case the LOB file name is included in the export file. When the control file for the SQL Loader is coded, the LOBFILE keyword is used.

For delimited-file export, you can also specify Use qualified file names. This specifies whether qualified or unqualified file names are to be used in the export file. For other types of export this option is ignored (qualified file names will always be used).