title: Parse SQL file with PL/SQL and DML/DDL using cx_Oracle in python
body: <p>I have an SQL file which I want to parse and execute in oracle using <code>cx_Oracle</code> python library. The SQL file contains both classic DML/DDL and PL/SQL, eg. it can look like this:</p>
  
  <p><code>create.sql</code>:</p>
  
  <pre><code>-- 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);
  </code></pre>
  
  <p>if I use this file in SQLDeveloper or SQL*Plus, it will be split into 3 queries and executed. </p>
  
  <p>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 <code>string.split(';')</code> (as suggested here <a href="https://stackoverflow.com/questions/7532534/execute-a-sql-script-file-from-cx-oracle">execute a sql script file from cx_oracle?</a> ), 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.</p>
  
  <p>On the Oracle forum ( <a href="https://forums.oracle.com/forums/thread.jspa?threadID=841025" rel="nofollow noreferrer">https://forums.oracle.com/forums/thread.jspa?threadID=841025</a> ) 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?</p>
  
  <p>Edit: The best solutions seems to use SQL*Plus directly. I've used this code:</p>
  
  <pre class="lang-py prettyprint-override"><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))
  </code></pre>