Why use the 'copy' command?

[Mr.Hanky]

We use it because our developers are not allowed to use import/export. And as Dave pointed out you can set up commits within using ARRAYSIZE and COPYCOMMIT.


Here is some info on copy that I prepared (with the help of "Oracle SQL*Plus, the definitive guide, O'Reilly) a while ago for some developers.


SET ARRAY[SIZE] May be abbreviated to array.
This parameter sets the number of rows fetched at one time. The default is 15. The allowed range is from 1 to 5000.

SET COPYC[OMMIT] May be abbreviated to copyc.
The copycommit setting works in conjunction with the arraysize setting. The arraysize setting controls the number of rows in a batch. The copycommit setting controls how many batches are copied before committing. The number or rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT.

Example
SET ARRAYSIZE 15
SET COPYCOMMIT 10
COPY TO my_id@my_big_arse_database.com -
INSERT my_id.table_name -
USING SELECT * from table_name;

Since the arraysize setting is 15 and the copycommit setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).

COPY
The COPY command allows you to use SQL* Plus as a conduit for transferring data between two Oracle databases:

COPY {FROM connection | TO connection}
{APPEND | CREATE | INSERT | REPLACE}
destination_table [(column_list)]
USING select_statement

Note; When using the COPY command In SQL*Plus you must code a hyphen (-) after each line to continue to the next line. The last line will end with a semi-colon (
COPY is the command.

FROM/TO
If you are connected to the source database , use the TO option to specify the destination database. If you are connected to the target database, use the FROM option to specify the source of the data.

Connection
Is the login information to use when connection to the other database.
Your_id@your_big_arse_database.com

APP[END]
Causes SQL*Plus to insert the copied rows into the destination table, creating it first if necessary.

CRE[ATE]
Causes SQL*Plus to copy the data only if the destination table is a new table.
If the destination table already exists, the COPY command will abort.

INSERT
Causes SQL*Plus to insert the copied rows into the destination table only if it already exists. If the destination table is a new table, the COPY command will abort.

REP[LACE]
Causes SQL*Plus to drop the destination table if it currently exists. A new table is then created, and the data is copied.

Destination_table
Is the name of the table to which you want to copy.

Column_list
Specifies column names to use when the COPY command creates a new destination table. This is a comma delimited list, and the number of column names must match the number of columns in the SELECT statement.

Select_statement
Is a SELECT statement that returns the data you want to COPY.


Can you feel the love in here?

MH