Page 131
The syntax of the COPY command is:
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
The username[/password] clause represents the Oracle username and password you want to COPY FROM and TO. In the FROM clause, username/password identifies the source of the data; in the TO clause, username/password identifies the destination. If you do not specify a password in either the FROM clause or the TO clause, SQL*Plus will prompt you for it. SQL*Plus will not display the user's response to these prompts.
The database_specification clause is a database link name, a SQL*Net V1 connection string, or a SQL*Net V2 service name. A database_specification clause must be provided in the COPY command. In the FROM clause, database_specification represents the database at the source; in the TO clause, database_specification represents the database at the destination.
The destination_table is the table you want to create or to which you want to add data, and [(column, column, column, ...)] specifies the names of the columns in destination_table.
If you specify columns, the number of columns must be the same as 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 table if COPY creates destination_table.
The USING query parameter specifies a SELECT statement that extracts the rows and columns that the COPY command copies.
The FROM username[/password]@database_specification section defines the username, password, and database that contains the data to be copied. If you omit the FROM clause, the source defaults to the database to which SQL*Plus is connected. You must include a FROM clause to specify a source database other than the default.
The TO username[/password]@database_specification section specifies the database containing the destination table. If you omit the TO clause, the destination defaults to the database to which SQL*Plus is connected. You must include a TO clause to specify a destination database other than the default.
Page 132
Several parameters control how the COPY command copies data from one table to another. The parameters are the following:
Three SQL*Plus SET variables control the behavior of the COPY command:
LONG determines the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.
By default, SQL*Plus performs a commit at the end of each successful COPY command. If you set the SQL*Plus SET variable COPYCOMMIT to a positive integer value n, SQL*Plus performs a commit after copying every n batches of records. The SET variable ARRAYSIZE determines the size of a batch.
NOTE |
When using the COPY command, some operating environments (most notably VAX/VMS and OpenVMS) require that database specifications be placed in double quotation marks. |
NOTE |
If any of the column names specified in the (column, column,...) clause of the COPY command contains lowercase letters or blanks, the column name must be enclosed in double quotation marks. |
NOTE |
Be careful when using the COPY command to copy tables that contain NUMBER columns! These columns become DECIMAL with a precision of 38 in the destination database. |
There are many situations in which the DBA or a regular user needs to perform SQL statements on many database objects (tables, indexes, and so forth). For example, the DBA may
Page 133
periodically need to load data from a PRODUCTION database into a DEVELOPMENT or TEST database. Before loading the data, he or she may need to delete the data from all the tables in the DEVELOPMENT or TEST database. Doing it on a table-by-table basis could be very tedious, especially if there are a few hundred tables in the database. One simple and fast solution in this situation is to use SQL*Plus to create the required SQL statements, save them into an operating system file, and run the file from SQL*Plus.
For example, to delete all the rows from all the tables belonging to user SCOTT, SYSTEM can use the SQL in Listing 8.11.
Listing 8.11 SQL Generates SQL
SQL> CONNECT SYSTEM Enter password: ******* Connected. SQL> SET PAGESIZE 0 SQL> SET HEADING OFF SQL> SET FEEDBACK OFF SQL> SET VERIFY OFF SQL> SET ECHO OFF SQL> SPOOL DELTBL.SQL SQL> SELECT `DELETE `||TABLE_NAME||';' 2 FROM DBA_TABLES 3 WHERE OWNER='SCOTT'; DELETE ACCTS; DELETE ACCT_ADDRS; DELETE BONUS; DELETE CITIES; DELETE COMPANY_SUMMARY; DELETE CUSTOMER; DELETE DEPT; DELETE DUMMY; DELETE EMP; DELETE FUNDS; DELETE FUND_CONTRIB; DELETE FUND_XACT; DELETE F_EMPCOMP; DELETE F_XACT_TYPE; DELETE INVINFO; DELETE INVREQUEST; DELETE ITEM; DELETE ORD; DELETE ORDER_HISTORY; DELETE PRICE; DELETE PRODUCT; DELETE SALES_REVENUE; DELETE SALGRADE; DELETE STOCK_HISTORY; SQL> SPOOL OFF