CODEWITHSUNDEEP
pythonoracle-databaseplsqlcx-oracle
Mohd Faheem
Mohd Faheem

Reputation: 93

cx_Oracle.DatabaseError: ORA-00922: missing or invalid option when trying to execute pl/sql using python

AM trying to execute the PL/SQL script which am constructing at the run time but getting 

cx_Oracle.DatabaseError: ORA-00922: missing or invalid option

Looks like some formatting issue with the script as it is showing as STRING but still not sure how to resolve it.

Below is the code that am trying:

script = '''Set serveroutput on;
    DECLARE
    V_req pls_integer;
    BEGIN
    V_req := infomediary_nse.request(
            p_inApp_id  => 100,
            p_inPayload => XMLTYPE(
       '<tag>hello</tag>'
      )
      );
      END;
      /'''

dbconnection = cx_Oracle.connect(ConnectionString)
str, err = dbconnection.cursor().execute(script)

Upvotes: 2

Views: 1376

Answers (1)

Aleksej
Aleksej

Reputation: 22969

set serveroutput on

is not a PL/SQL command, but a SQL*Plus one, so you can only use it in SQL*PLus.

Even the final / should be removed, because it also is SQL*Plus specific.

This should work:

script = '''DECLARE
    V_req pls_integer;
    BEGIN
    V_req := infomediary_nse.request(
            p_inApp_id  => 100,
            p_inPayload => XMLTYPE(
       '<tag>hello</tag>'
      )
      );
      END;'''

If you used set serveroutput on to get the result from DBMS_OUTPUT calls, you can have a look at this.

For example, this:

import cx_Oracle
conn = cx_Oracle.connect(..., ..., ...)
c = conn.cursor()

vSql = '''begin
           dbms_output.put_line('Hello!');
          end;
'''
c.callproc("dbms_output.enable")
c.execute(vSql)

statusVar = c.var(cx_Oracle.NUMBER)
lineVar = c.var(cx_Oracle.STRING)

while True:
  c.callproc("dbms_output.get_line", (lineVar, statusVar))
  if statusVar.getvalue() != 0:
    break
  print (lineVar.getvalue())

conn.close()

gives:

E:\Python>python testOracle.py
Hello!

Upvotes: 3

Related Questions