Executing SQL files with multiple queries on Python 2.7 using cx_oracle

Question

I'm having real problems running some .sql files via cx_oracle. for example test_table2.sql below runs perfectly if I run through Oracle Developer.

declare
c int;
begin
select count(*) into c from user_tables where table_name = upper('TEST2');
if c = 1 then
  execute immediate 'drop table TEST2';
end if;

EXECUTE IMMEDIATE 'CREATE TABLE MURRAYLR.test2 as 
select * from Dans_Table';

EXECUTE IMMEDIATE'CREATE TABLE MURRAYLR.test1
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50)
)';

end;

The Python code normally work for simple queries, but as soon as I try to do a check for existing tables script it gives me errors. See below

Python 2.7.11 code

import sys
import cx_Oracle

connection = cx_Oracle.connect('user','password','serv')
cursor = connection.cursor()

filename="C:\Users\desktop\Test_table2.sql"

f = open(filename)
full_sql = f.read()
sql_commands = full_sql.replace('\n', '').split(';')[:-1]

for sql_command in sql_commands:
cursor.execute(sql_command)

connection.close()

Error Message

cursor.execute(sql_command) DatabaseError: ORA-06550: line 1, column 15: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

:= . ( @ % ; not null range default character

Frank Schmitt · Accepted Answer · 2016-04-13 10:48:01Z

1

I haven't tried to run your code (for lack of a Python installation with cxOracle), but it looks like you're splitting your anonymous PL/SQL block into multiple separate statements:

sql_commands = full_sql.replace('\n', '').split(';')[:-1]

Therefore, the first SQL command you're trying to execute is

declare
  c int;

which doesn't make any sense. Since you already put all of your commands in a single anonymous PL/SQL block, you don't need to split it at all - just run the whole block as a single command, and you should be fine.

UPDATE

Complete solution (untested):

import sys
import cx_Oracle

connection = cx_Oracle.connect('user','password','serv')
cursor = connection.cursor()

filename="C:\Users\desktop\Test_table2.sql"

f = open(filename)
full_sql = f.read()
cursor.execute(full_sql)

connection.close()

edited Apr 13, 2016 at 10:48

answered Apr 13, 2016 at 10:20

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

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

3 Comments

Lee Murray Over a year ago

Frank, I'm not sure I understand your solution. Can you gave me a hand with the code?

Frank Schmitt Over a year ago

@LeeMurray See my updated answer; I couldn't test it, but it should work (possibly with slight modifications, I haven't been doing much Python lately)

ArtBajji Over a year ago

Say there is a / at the end of the anonymous block. After the /, there is a grant statement on that table followed by another /. How do we handle this slightly modified situation?

Collectives™ on Stack Overflow

Executing SQL files with multiple queries on Python 2.7 using cx_oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related