Replication of legacy Oracle data tables with SQL *plus

You may be running an Oracle 10g Release 2 database server, but supporting some applications may have been written very early. Data tables created prior to Oracle 8i typically have a long data type to store large text.

With the introduction of the large Object (LOB) data type, the long and long raw data types are discarded. If you are copying a data table that uses a Long data type, then using the CREATE table as SELECT syntax will not complete the task, you will return an error: Ora-00997:illegal use of a Long data type.

You can export the table and then import it, but doing a lot of work. The copy command in Sql*plus can still use a single command to complete replication of such data tables. The following is the format for using the Copy command (note: Use the short line "-" to wrap).

COPY from User/pw@dblink to User/pw@dblink CREATE tablename-
USING select-statement;

However, the functionality of the Oracle 8.0 Copy command is frozen because it can only replicate data tables that consist of the following data types: Char,date,long,number and VARCHAR2. The addition of new data types in the 8i version is not supported by the latter, which is a completely inherited method.

Sql> CREATE TABLE my_views as 2 SELECT * from User_views; SELECT * FROM User_views * ERROR in line 2:ora-00997:illegal use of LONG datatype sql> SET LONG 100000 SQL

> COPY from HR/HR@ORCL CREATE my_views->using select * from User_views; 
Array fetch/bind size is 15. (ArraySize is 15) would commit when done.
(Copycommit is 0) 
Maximum long size is 100000.
(Long is 100000)
   Table My_views created.   
1 rows selected from HR@ORCL.   
1 rows inserted into my_views.
1 rows committed into My_views at DEFAULT HOST connection. Sql> descmy_views;    Name Null?                    Type----------------------view_name not NULL VARCHAR2 () text_length number (TEXT) LONG type_text_length Number (type_text) VARCHAR2 (4000) oid_text_length NU Mber (Oid_text) VARCHAR2 (4000) View_type_owner VARCHAR2 (View_type)2 () Superview_name VARCHAR2 () sql> spool off 

List A

The error of using CREATE table and the method of successfully using the Copy command are shown in List A.