Python cx_Oracle invalid option

Question

I'm trying to use Python to create a table and insert some values

cur = con.cursor()

remove_table = '''
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE {}';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
'''
create_table_workclasses = '''
CREATE TABLE WORKCLASSES (
WORKCLASSES_NAME VARCHAR2(32) NOT NULL
)
'''
insert_workclasses = '''
INSERT ALL
INTO WORKCLASSES VALUES ('Private');
INTO WORKCLASSES VALUES ('Selfemp-inc')
INTO WORKCLASSES VALUES ('Federal-gov')
SELECT * FROM dual;
'''

cur.execute(remove_table.format('WORKCLASSES'))
cur.execute(create_table_workclasses)
cur.execute(insert_workclasses)

cur.close()
con.close()

Getting an error of

cur.execute(create_table_workclasses)
cx_Oracle.DatabaseError: ORA-00922: missing or invalid option

The thing is that query works just fine in SQL Developer!

The newline character maybe affecting the query in `create_table_workclasses` . `'\nCREATE TABLE WORKCLASSES (\nWORKCLASSES_NAME VARCHAR2(32) NOT NULL\n)\n'` `NOT NULL\n` is an invalid option. — Oluwafemi Sule, Jun 16 '17 at 18:09
The create table statement works just fine with cx_Oracle, too -- at least as far as you have displayed it here! I get no such errors! — Anthony Tuininga, Jun 19 '17 at 16:43

pmi · Answer 1 · 2017-06-16T19:02:04.750

I would suggest You to study following post: Python style - line continuation with strings?

Basically it says when You use ''' it substitutes the newline in source code for \n.

So the possible solution is to use double quotation single character " together with separator \ for new lines.

E.g.:

remove_table = "BEGIN \
EXECUTE IMMEDIATE 'DROP TABLE {}'; \
  EXCEPTION WHEN OTHERS \
  THEN IF SQLCODE != -942 THEN \
    RAISE; \
  END IF; \
END;"

If You're space-pedant please consider spaces on the beginning of the new lines. They'll add to the final string too

Just in case there is yet another way that keeps code indent but uses even more separators and quotations.

remove_table = "BEGIN " \
"EXECUTE IMMEDIATE 'DROP TABLE TEST';" \
"  EXCEPTION WHEN OTHERS " \
"    THEN IF SQLCODE != -942 THEN " \
"      RAISE;" \
"    END IF;" \
"END;"

Python cx_Oracle invalid option

1 Answers1