How to use COPY table command of Oracle DBMS using JDBC?

Question

I'm trying copy table from one database to another(On different machines), and using JDBC Template to execute query, but this request is specific to Oracle:

COPY FROM username1/passwd1@//192.168.3.17:1521/PROD_SERVICE to username2/passwd2@//192.168.4.17:1521/SANDBOX_SERVICE INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C);

And I get error:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

How can I use specific to Oracle syntax in JDBC?

If you mean the SQL*Pus COPY command, it's been deprecated since around 2001 and doesn't support any new features added since 1998. You'd need to invoke SQL*Plus somehow to use it - it's not part of Oracle SQL. — William Robertson
– William Robertson, Commented Jul 18, 2020 at 17:52
@WilliamRobertson Does Oracle have command to copy table which I can use in JDBC? — kaylil_01
– kaylil_01, Commented Jul 18, 2020 at 18:15
cant you do, "create table xyz as select * from <source _table>? — OldProgrammer
– OldProgrammer, Commented Jul 18, 2020 at 19:41
Why can't you use RMAN (or expdp or exp) to create a backup of the table and then restore from the backup on the other machine? — MT0
– MT0, Commented Jul 18, 2020 at 22:53
@OldProgrammer because I need to copy table to another machine. — kaylil_01
– kaylil_01, Commented Jul 19, 2020 at 8:32

Jon Heller · Accepted Answer · 2020-07-19 18:43:00Z

Like some of the comments have already clarified, COPY is a sqlplus command, and it has been deprecated for a while. You cannot use it inside JAVA, because this command is not part of the SQL engine, it's just a kind of additional feature available only in sqlplus. It is still available, but only for backwards compatibility.

If you want to copy a table using Java, you need to understand first some things:

Java, or any external engine for that matter, can't connect at the same time to both databases. Either it connects to one or to the other.
You need to have a kind of bridge between both databases, so that your Java program is only acting as trigger.
Copying tables between databases is something related to the database, so you should think in using tools provided by your database engine. You have some options, like Datapump or RMAN, although I consider Datapump the best suitable for your scenario.

However, if you insist in using Java, first you need to have a database link between both databases. Then you can use Java to invoke an insert from one database to another.

https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

If you don't want to depend on thsnames entries in the server, here an example of database links:

CREATE DATABASE LINK to_my_remote_user 
   CONNECT TO remote_user IDENTIFIED BY password
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=remote_server)(PORT=remote_port))
            (CONNECT_DATA=(SERVICE_NAME=remote_service_name))
          )';

Once you have the dblink created, then you can connect from java to the database where the link is available and copy the data to the remote database

INSERT INTO remote_user.remote_table@to_my_remote_user 
select * from local_user.local_table ;

Important: Normally dblinks are not allowed on Production systems, because they increase security risks. Also remember that DDL operations over a database link require an extra step, such as using the procedure DBMS_UTILITY.EXEC_DDL_STATEMENT@dblink('create table ...);

Another option outside of Java is using SQL Developer copy feature. Although I only recommend it for small tables. If you want to use it with big tables, it will probably hang. You can read here an good example :

copy from one database to another using oracle sql developer - connection failed

Collectives™ on Stack Overflow

How to use COPY table command of Oracle DBMS using JDBC?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related