Little Gold Nugget: SQL*Plus COPY Command

Although not a “new” command in SQL*Plus, it’s very cool.
Recently I had to move some data from one table to another in a different database. The Source table structure included a LONG column. In the Target Database the column was a CLOB.
Luckily for me Oracle provides a TO_LOB function. However… LONG columns and Database Links don’t mix. There must be another way…
There are restrictions to the TO_LOB function, taken from the documentation:

You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement

Of course I tried that, but like I said earlier LONG columns and Database Links don’t mix.
PL/SQL could be used, in this case you don’t need the TO_LOB function. Surprisingly Native Dynamic SQL (Execute Immediate) allows you to “Insert into CLOB select LONG from table” but straight SQL does not.
PL/SQL could be used, but the performance is poor…

If I could just get the Source Table over to the Target Database without too much fuss….

Once the table is “on the other side”, meaning in the Target Database, I could use the TO_LOB function to fill up the Target Table.
Database Link doesn’t work… PL/SQL is not a great option… EXP/IMP, I would have to bother the DBA… what about COPY? (act surprised now, like you didn’t see this coming :))

The Syntax

What does COPY do? Well, it copies… that’s obvious. Is it SQL? No, it’s SQL*Plus.

Syntax (I wish I could find a nice picture to show you):
COPY {FROM username[/password]@database_specification| TO username[/password]@database_specification| FROM username[/password]@database_specification TO username[/password]@database_specification} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column …)] USING query
I think the username, password and database_specification are clear.
With the FROM and TO keywords you control the Source and Destination databases. The next command can be either APPEND, CREATE, INSERT, REPLACE

One thing you gotta keep in mind though, COPY is a one-line command. You cannot hit the “Enter” key and continue on the next line. If you do hit the “Enter” key too soon, when you’re not done typing all necessary commands, you will receive an error.
You can continue typing on the next line if you use the “continue” character, that is the hyphen (-)
I just found out, if you type COPY in SQL*Plus and nothing else, it will some you the syntax to use.

COPY is really cool!

The COPY command is great. Just to show you a little example:
copy from scott/tiger@orcl -
to tiger/scott@lcro INSERT new_emp -
(EMPNO -
,ENAME-
,SAL-
,COMM -
) -
using select EMPNO -
,ENAME -
,SAL -
,COMM -
from emp
As you can see, it’s easy to use. The syntax is pretty straightforward. And it’s fast too! I ran some test with different loads, and here are the results:

*) LONG column in the Source Table
The settings I used in SQL*Plus were: ARRAYSIZE 1000, COPYCOMMIT 10, LONG 9000. The results shown here concerned different tables with varying amount of columns.
Elapsed time included the use of the TO_LOB function described earlier to load the target tables.

All in all: COPY is COOL!

References

Error Messages
Documentation