Search notes:

SQL*Plus: COPY

The SQL*Plus copy command copies the result of an select statement to another database.

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} [destTableName] { (column) } USING select-statement

The behavior on the destination database is optionally specified with one of the following verbs:

append: Append the selected rows to an existing table, or create a table and insert the rows if the table does not exist
create: Create the destination table and insert the selected rows - fails if table already exists
insert: Insert the selected rows into the destination table - fails if table does not exist
replace: deletes (truncates?) rows in destination table before inserting selected rows. If table does not exist, it is created.

Supported datatypes

Only five data types are supported:

char
date
long (but be sure to set a reasonable value for the long variable)
number
varchar2

Trying to copy another datatype (notably clob or blob!) results in the error message CPY-0012: Datatype cannot be copied.

ARRAYSIZE

The SQL*Plus variable arraysize specifies the number of rows that SQL*Plus fetches from the source data base and copies to the destination database.

Transaction handling

When SQL*Plus has sucessfully copied the selected rows, it commits the transaction

In addtion, the SQL*Plus variable copycommit can be set in which case each batch (of size arraysize) will be commited.

CPY-0007: Select list has fewer columns than destination table

For append operations or insert operations with an already existing table, the number of selected columns must correspond to the number of columns in the select statement after the using clause, otherwise SQL*Plus throws a CPY-0007 error.

SQL*Plus: COPY

Supported datatypes

ARRAYSIZE

Transaction handling

CPY-0007: Select list has fewer columns than destination table

See also