Using SQL*PLUS COPY from inside cx_Oracle?

3
votes

We need to copy records between two Oracle database schemas.

Manually, we can do this using the SQL*PLUS COPY command:

http://www.oracleutilities.com/SQLPLus/copy.html

However, we'd like to automate this using cx_Oracle if possible (we also need to do some other things - e.g. SSH interactions, hence the use of Python and cx_Oracle).

However, if I try to execute COPY inside cx_Oracle, it doesn't seem to like the command:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement

I'm guessing this is because COPY is a SQL*PLUS specific command, as opposed to part of the standard SQL spec?

Anyhow, is there any way of getting the COPY command (or any other SQL*PLUS specific extensions) to work under cx_Oracle?

I believe it's possible to use INSERT INTO...SELECT to achieve something similar, however that requires a database link if the two databases in question are on separate Oracle instances or hosts, and we don't always have permission to create those links.

Cheers, Victor

2
pythonsqloraclesqlpluscx-oracle
SQLPlus commands are parsed and executed by SQLPLus. So there is no way to use them with any other API. - a_horse_with_no_name
Hmm, first - put another way - is there any way to achieve the same thing as SQL*PLUS (copy across database table records, without using a database link?) - victorhooi
Copying the data from a database to Python and then back to a database is going to be lots of network traffic. It's the same with SQL*Plus COPY, so really look for how to achieve other solutions like DB links. If you do want to use Python, then make sure you check cx-oracle.readthedocs.io/en/latest/user_guide/tuning.html and cx-oracle.readthedocs.io/en/latest/user_guide/… - Christopher Jones

2 Answers

0
votes

You have two options, as far as I see it:

  1. Use Popen to execution SQL text/file, as shown here: Not able to execute sql command through a session created using POPEN in python

  2. Open two sessions, get data into List/buffer and insert it into the target session (using binding).

0
votes

The utility run_all_sql_dir.py executes all sql files from the specified directory and creates one log files.The utility generates a script from a set of sql files and uses sqlplus for execution. In the script you can configure NLS variables:

NLS_DATE_FORMAT = "\'DD.MM.YYYY HH24:MI:SS\'"
NLS_NUMERIC_CHARACTERS = "\'.,\'"
NLS_LANG = 'AMERICAN_AMERICA.CL8MSWIN1251'

, autocommit,

AUTO_COMMIT = "OFF"

, silent sqlplus

# silent sqlplus = "-s"  silent off sqlplus = ""
silent_sqlplus = "-s"

stop the script on error or continue execution

WHENEVER = 'WHENEVER SQLERROR EXIT SQL.SQLCODE'.

-u Specify the username for example SCOTT

-p Specify the password for example TIGER

-c Specify the connect_string(TNS alias) or easy connect string for connect to oracle database

-d Specify the directory for executing sql scripts.

-l Specify the logfile for output log.

for example

run_all_sql_dir.py -u scott -p tiger -c 192.168.0.166:1521/test -d C:\Users\Dmitry\PycharmProjects\count_char\sql  -l log_sql.log