Parse SQL file with PL/SQL and DML/DDL using cx_Oracle in python

Question

I have an SQL file which I want to parse and execute in oracle using cx_Oracle python library. The SQL file contains both classic DML/DDL and PL/SQL, eg. it can look like this:

create.sql:

-- This is some ; malicious comment
CREATE TABLE FOO(id numeric);

BEGIN
  INSERT INTO FOO VALUES(1);
  INSERT INTO FOO VALUES(2);
  INSERT INTO FOO VALUES(3);
END;
/
CREATE TABLE BAR(id numeric);

if I use this file in SQLDeveloper or SQL*Plus, it will be split into 3 queries and executed.

However, cx_Oracle.connect(...).cursor().execute(...) can take only ONE query at a time, not an entire file. I cannot simply split the string using string.split(';') (as suggested here execute a sql script file from cx_oracle? ), because both the comment will be split (and will cause an error) and the PL/SQL block will not be executed as single command, thus causing an error.

On the Oracle forum ( https://forums.oracle.com/forums/thread.jspa?threadID=841025 ) I've found that cx_Oracle itself does not support such thing as parse entire file. My question is -- is there a tool to do this for me? Eg. a python library I can call to split my file into queries?

Edit: The best solutions seems to use SQL*Plus directly. I've used this code:

# open the file
f = open(file_path, 'r')
data = f.read()
f.close()

# add EXIT at the end so that SQL*Plus ends (there is no --no-interactive :(
data = "%s\n\nEXIT" % data

# write result to a temp file (required, SQL*Plus takes a file name argument)
f = open('tmp.file', 'w')
f.write(data)
f.close()

# execute SQL*Plus
output = subprocess.check_output(['sqlplus', '%s/%s@%s' % (db_user, db_password, db_address), '@', 'tmp.file'])

# if an error was found in the result, raise an Exception
if output.find('ERROR at line') != -1:
    raise Exception('%s\n\nStack:%s' % ('ERROR found in SQLPlus result', output))

Same problem here. Basically, Oracle is braindead and doesn't actually have any built-in ability to parse multi-statement SQL scripts, so SQLPlus and SQL Developer and TOAD all implement their *own parsers :-( — Dan Lenski
– Dan Lenski, Commented Jan 8, 2016 at 3:28

Ben · Accepted Answer · 2012-04-13 08:46:10Z

4

It's possible to execute multiple statements at the same time but it's semi-hacky. You need to wrap your statements and execute them one at a time.

>>> import cx_Oracle
>>>
>>> a = cx_Oracle.connect('schema/pw@db')
>>> curs = a.cursor()
>>> SQL = (("""create table tmp_test ( a date )"""),
... ("""insert into tmp_test values ( sysdate )""")
... )
>>> for i in SQL:
...     print i
...
create table tmp_test ( a date )
insert into tmp_test values ( sysdate )
>>> for i in SQL:
...     curs.execute(i)
...
>>> a.commit()
>>>

As you've noted this doesn't solve the semi-colon problem, for which there is no easy answer. As I see it you have 3 options:

Write an over-complicated parser, which I don't think is a good option at all.
Do not execute SQL scripts from Python; have the code in either separate SQL scripts so the parsing is easy, in a separate Python file, embedded in your Python code, in a procedure in the database... etc. This is probably my preferred option.

Use subprocess and call the script that way. This is the simplest and quickest option but doesn't use cx_Oracle at all.

>>> import subprocess
>>> cmdline = ['sqlplus','schema/pw@db','@','tmp_test.sql']
>>> subprocess.call(cmdline)

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 13 09:40:41 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL>
SQL> CREATE TABLE FOO(id number);

Table created.

SQL>
SQL> BEGIN
  2    INSERT INTO FOO VALUES(1);
  3    INSERT INTO FOO VALUES(2);
  4    INSERT INTO FOO VALUES(3);
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> CREATE TABLE BAR(id number);

Table created.

SQL>
SQL> quit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
0
>>>

answered Apr 13, 2012 at 8:46

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ondrej Skalicka Over a year ago

Hi, the subprocess.call seems like a reasonable solution (I cannot use the second choice as I can't control the SQL file). However, when I run sqlplus as subprocess, it will wait for the "QUIT" command. My Python script needs to be non-interactive (there will be several SQL files executed this way). How would you change it so it won't wait for the stdin?

Ben Over a year ago

@Savannah, can you not add the quit to the SQL scripts? Whoever generates them should be ensuring that they exit.

Ondrej Skalicka Over a year ago

I've added something like data = "%s\n\nEXIT" % data to the script I run and it works now.

Neil Vass Over a year ago

Using subprocess.Popen instead of call will let you end the session from Python, without having to add the exit command to the SQL scripts - see stackoverflow.com/a/12797593/607861

Collectives™ on Stack Overflow

Parse SQL file with PL/SQL and DML/DDL using cx_Oracle in python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related