We have an old Oracle 10 database which we currently cannot upgrade. (We are looking into replacing the application, but that might take another 2 years.)
We need to transfer data from an Oracle 12 database to this old database and back. As database links between Oracle 10 and Oracle 12 may/will stop working after June 23, we are investigating alternatives.
One which looked promising was the 'COPY' command. (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SQL-Plus-COPY-command.html)
But I cannot get it to work. On the Oracle 12 db, I logged in as user2 and used a command similar to this one (the list of columns is longer though):
SQL> COPY
FROM user1/passwd1@OLDO10DB
TO user2/passwd2@NEWO12DB
INSERT table_in_o12 (
t_col1, t_col2, t_col3
) USING
SELECT col1, col2, col3 FROM table_in_o10
/
COPY
*
ERROR at line 1:
ORA-00900: invalid SQL statement
I know the documentation says that the COPY command will be deprecated, but the command *is* still there:
SQL> COPY
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db> : database string, e.g., hr/your_password@d:chicago-mktg
<opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.
Can anyone see what might be wrong with this command?
I know Oracle 10 is no longer supported, but other than writing the records to a file and reading the file back in, I see no other alternative.
(Though if you know one, that information too would be more than welcome.)