Exporting Oracle Table Data Using SQL Developer - SQL Insert Results
Unloading Results To Insert Statements
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.
NOTE: In most cases you can use "SELECT * FROM MyTable" but if you need to do any special character escaping you would do that here in the select statement. For example, the Unloading process escapes single quotes for varchar2 columns, but not CLOB columns. If you want to unload a CLOB (with less than 4000 characters) and use it in an insert statement, you need to escape the single quotes with something like "REPLACE(MyClobCol,'''','''''') AS MyClobCol". Clobs over 4000 characters need to be handled using bind params ... something for a later discussion!
3) Right click on the results pane and choose "Unload" from the right click menu to launch the "Unload Wizard". (and yes, I agree, unload is a horrible name for an export tool!)
4) On the first page of the Unload Wizard, set "Format:" to "Insert" and change the "Table Name:" value to match the name of the table you are exporting. The table name must be in ALL CAPS (I'll explain why when we look at the Unload results). Change the "Save As" value to "Worksheet" and click "Next >", Then click "Finish".
5)The Unload results appear in a new worksheet for you to review.
Review of Unload Results
If you set the table name in the Unload Wizard, you'll see something similar to the following, which is what you want.
We can test out our results by clearing out our test table and running this script to re-create our records.
So in most cases this is all you need to do to move select rows of data from a given table on one database server to another database server (with the same schema on the destination database server, etc).
Unloading Gotcha - Using Mixed Case Table Name in Unload Wizard
If you unintentionally typed the table name in lower or mixed case in the Unload Wizard, you'll probably end up with insert statements that will throw errors.
Check this out, the unload process double quotes the table names in the results - which means now you are using case sensitive table name matching. By default, oracle table names are stored in all uppercase format, so you'll throw a "table or view does not exist" error. Its just another one of those strange Oracle quirks that had me going crazy for a while.
Unloading Gotcha - CLOB data larger than 4000 characters
Yeah. In this case, straight SQL wont work for you. The only way I know to move clob data using SQL is via a bind param, which means you need to manually fix the Unloading results to do this. Its a pain. I don't like it. When you run into this problem, you should probably start looking at alternatives to using the Unload Wizard ...
... try the "Loader" format as described in my next post Exporting Oracle Table Data Using SQL Developer and SQLLDR.