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))

Answer 1

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:

1. Write an over-complicated parser, which I don't think is a good option at all.

2. 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.

3. 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
   >>>