Little Gold Nugget: SQL*Plus COPY Command

7

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

APPEND Inserts records into the target table. If the target table doesn’t exist it’s created for you.
CREATE Creates a target table for you and Inserts records into it. If the target table already exists, you will receive an error.
INSERT Inserts records into the target table. If the target table doesn’t exist, you will receive an error.
REPLACE If the target table exists, it will be dropped and recreated prior to loading the table.
USING query Lets you specify which rows and columns you wish to copy.

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:

No. of Records Elapsed Time
2434089 00:39:04 *
2435006 00:18:24
2476055 00:05:46
8403 00:00:01
8623 00:00:02
1414956 00:05:24 *
20384678 01:25:17 *
91154 00:00:15 *
822102 00:02:32
108550 00:00:22 *
41938 00:00:23 *

*) 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

Share.

About Author

7 Comments

  1. Even though the COPY command is not being updated, it’s still available in the Oracle 11g database. You can find the documentation here

  2. hi,

    I have requirement to make sync of 30 tables in one database with the same 30 tables in different database on daily basis. can any one suggest me how to do it. Is Copy command the best approach or is there any other method to do it.

  3. A nice post was written on aMr. Ed’s weblog: http://www.edhanced.com/ask-mred/?q=oracle-blog

    SQLPlus COPY And Big LONGs

    Alex Nuijten at AMIS wrote about the wonderful COPY command in SQLPlus. It’s a useful command for copying LONG tables. And it’s fast, since it opens one connection for reading rows and another connection for writing rows. That makes a big difference for LONG values.

    Watch out for a bug/limitation in the Oracle 8i SQLPlus, though. It silently fails to copy over big LONG values: