Little Gold Nugget: SQL*Plus COPY Command Oracle Headquarters Redwood Shores1 e1698667100526

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

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

7 Comments

  1. Alex Nuijten November 1, 2007
  2. Alex Nuijten January 11, 2006
  3. jhreddy January 10, 2006
  4. Alex Nuijten January 3, 2006
  5. Shubha January 3, 2006
  6. Alex Nuijten October 12, 2005
  7. Lucas February 26, 2005