CODEWITHSUNDEEP
pythonoracle-databasepython-2.7cx-oracle
Lee Murray
Lee Murray

Reputation: 489

Executing SQL files with multiple queries on Python 2.7 using cx_oracle

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

Upvotes: 1

Views: 8413

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

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

Upvotes: 1

Related Questions