0

How can rows be inserted into a table from a table in a remote database?

We currently have a stored procedure which does this using a database link. However, we are having to delete the link because our company policy doesn't allow their usage.

begin
    ...
    execute immediate 'insert into '|| table_name
                     || ' (select * from schema_name.'|| table_name ||'@link)';
    ...
end;

I'm unable to use the COPY command as it appears to be not recognized.

COPY FROM username/pwd@SID
  insert into table_name using (select *
                                from table_name);
Error report:
SQL Error: ORA-00926: missing VALUES keyword
00926. 00000 -  "missing VALUES keyword"
*Cause:    
*Action:

According to this SQL Plus page, COPY command is now obsolete.

mauryat
  • 1,610
  • 5
  • 29
  • 53

1 Answers1

4

Your Query syntax is slightly wrong, and you just need to specify INSERT/REPLACE/CREATE .. and INTO is NOT needed.

COPY is not obsolete, but it ends up with some encoding issues. You would use the line continuation character to get around that.

COPY 
  FROM username/pwd@SID 
  TO username/pass@SID2
  insert 
 table_name 
 using 
    select * from schema_name.table_name;

You can also, download the table data into a text file, and use SQL*Loader to load the data into the another Database.

I prefer the SQL*Loader option! Since maintenance is easy!

For download,
- you can either use SPOOL with delimiter option as mentioned Here
- Write a ProC/PLSQL to output the data into a File (Method 4 in ProC, OR select column names from dba_columns))

Community
  • 1
  • 1
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • +1 for Sql *Loader. There is also [data pump](http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm) that can be used with the same effect. – abhi Feb 19 '14 at 22:15
  • @OracleUser Are there any other options other than COPY? I learned that COPY is an SQL Plus command and hence cannot be used in a stored procedure. The stored procedure is on a cron job and hence the SQL Loader option doesn't fit the situation. – mauryat Feb 20 '14 at 21:13