Exporting Oracle Table Data Using SQL Developer and SQLLDR

Following up on my last post about trying to export table data from oracle as SQL insert commands, I've found a way to "Unload" query results in a format that works with large CLOB column data - the "Loader" format.

Unfortunately, you cant import loader format data using SQL Developer ... just another odd quirk of Oracle that drives me nutty. To import the loader results, you have to use the command line tool "sqlldr", which should be installed as part of the oracle client.

So lets take a look at exporting the Category table data again, this time using the Loader method.

Unloading Results into Loader Format

1) Launch SQL Developer and open a new worksheet connected to the database you want to use as the source.

2) Build a SQL statement including all the columns you want to export and execute it.

3) Right click on the results pane and choose "Unload" from the right click menu to launch the "Unload Wizard".

4) On the first page of the Unload Wizard, set "Format:" to "loader" and change the "Table Name:" value to match the name of the table you are exporting. The table name must be in ALL CAPS. Keep the "Save As" option set to "Single File", then change the path to point to a new empty folder on your system (this process may create more than one file). In this case, I named the file "category.ldr". Click "Next >", Then click "Finish".

5) Browse to the path you set in step 4 and you should see the loader control file (the filename you set in step 4) and possibly a bunch of data files. Open the control file with your favorite text editor and modify as needed.

By default the Unload to loader format process creates a control file with the TRUNCATE keyword. This tells the loader to DELETE all rows before adding the loader data. If you want to keep existing data in the table, use the APPEND keyword instead.

See the SQLLDR documentation on Loading Data into Nonempty Tables for more info.

6) Open a command prompt and change the working directory to be the path you set in step #4, so you are in the same directory as the loader files.

7) Assuming you have the oracle client installed on your box, run the sqlldr command line utility to import the data back into oracle.

NOTE: Unlike SQL Developer, the sqlldr command uses your TNS Names file to make the database connection.

The sqlldr command format is:

sqlldr <username>/<password>@<TNSName> control=<ControlFile>

Loader Results

After loading the data back in via the sqlldr command, we can see all the data made it in tact, including the huge CLOB data column (14000 characters long!). So, even though its a little bit of a pain to go outside SQL developer for the import process, this appears to be the better approach to exporting and importing table data compared to the "insert" option in the Unload Wizard.