SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Command Reference, 17 of 52
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query
where database has the following syntax:
Copies data from a query to a table in a local or remote database. COPY supports the following datatypes:
The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8. The COPY command is likely to be made obsolete in a future release.
Refer to the following list for a description of each term or clause:
FROM database
TO database
database
Specifies username[/password] @connect_identifier of the Oracle source or destination database you wish to COPY FROM or COPY TO. If you do not specify password in either the FROM clause or the TO clause, SQL*Plus will prompt you for it. SQL*Plus suppresses the display of your password response.
You must include the connect_identifier clause which consists of an Oracle Net connection string, to specify the source or destination database. The exact syntax depends upon the Oracle Net communications protocol your Oracle installation uses. For more information, refer to the Oracle Net manual appropriate for your protocol or contact your DBA.
APPEND
CREATE
INSERT
REPLACE
destination_table
(column, column, column, ...)
Specifies the names of the columns in destination_table. You must enclose a name in double quotes if it contains lowercase letters or blanks.
If you specify columns, the number of columns must equal the number of columns selected by the query. If you do not specify any columns, the copied columns will have the same names in the destination table as they had in the source if COPY creates destination_table.
USING query
To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.
The SQL*Plus SET variable LONG limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.
SQL*Plus performs a commit at the end of each successful COPY. If you set the SQL*Plus SET variable COPYCOMMIT to a positive value n, SQL*Plus performs a commit after copying every n batches of records. The SQL*Plus SET variable ARRAYSIZE determines the size of a batch.
Some operating environments require that service names be placed in double quotes.
The following command copies the entire EMPLOYEES table to a table named WESTEMPLOYEES. Note that the tables are located in two different databases. If WESTEMPLOYEES already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMPLOYEES have the same names as the columns in the source table, EMPLOYEES.
COPY FROM HR/HR@HQ TO JOHN/CHROME@WEST - REPLACE WESTEMPLOYEES - USING SELECT * FROM EMPLOYEES
The following command copies selected records from EMPLOYEES to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPLOYEE_ID and LAST_NAME, and at the destination names them EMPLOYEE_ID and SA_MAN.
COPY FROM HR/HR@ORACLE01 - CREATE SALESMEN (EMPLOYEE_ID, SA_MAN) - USING SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES - WHERE JOB_ID='SA_MAN';
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|