How to Execute PL/SQL Scripts With python-oracledb

Question

I have a script that was being executed with sqlplus until now, and I want to execute it with python. I checked the python-oracledb documentation but still couldn't figure it out.

What I tried doing is something like this:

sql = """
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN

SELECT SUBSTR(banner, INSTR(banner, 'Release')+8, 2) INTO v_version FROM v$version WHERE banner LIKE '%Oracle%';
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version > 12 THEN
    v_sql := 'select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,''[^:]+'',1,2),''[^(]+'',1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
  ELSIF v_version > 11 THEN
    v_sql := 'select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, ''12.[0-9].*''),''[^(]+'',1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
  ELSE
    v_sql := 'select max(replace(replace(replace(regexp_replace(comments, ''[^[:digit:].]''),''PSU'',''''),''64'',''''),''2021'','''')) keep (dense_rank last order by action_time) from registry$history';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
  END IF;
END;"""

cursor.execute(sql)
for implicit_cursor in cursor.getimplicitresults():
    for row in implicit_cursor:
        print(row)

and the error I am getting is:

Traceback (most recent call last):
  File "ora_runner.py", line 91, in <module>
    Query(un, pw, cs, False)
  File "ora_runner.py", line 83, in Query
    for implicit_cursor in cursor.getimplicitresults():
  File "/usr/local/lib64/python3.6/site-packages/oracledb/cursor.py", line 551, in getimplicitresults
    return self._impl.get_implicit_results(self.connection)
  File "src/oracledb/impl/thin/cursor.pyx", line 185, in oracledb.thin_impl.ThinCursorImpl.get_implicit_results
  File "/usr/local/lib64/python3.6/site-packages/oracledb/errors.py", line 111, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.InterfaceError: DPY-1004: no statement executed

Might enabling Thick Mode help?

Answer 1

You are receiving the error in question because the block of PL/SQL you are executing does not contain any implicit results. Essentially, you are attempting to read data when there is no data to read.

I note that your PL/SQL block contains calls to dbms_output.put_line. Please be assured that calling dbms_output.put_line does not write anything to implicit results, so cursor.getimplicitresults() will not be able to return any output written in such a way.

While dbms_output can be convenient to use in SQL*Plus, it is less convenient in other situations. This is because dbms_output keeps a cache of lines written to it, and SQL*Plus (and possibly other tools too) will fetch and display these lines for you if you ask it to. Outside of SQL*Plus, you will have to retrieve these lines yourself. It's not impossible, it can be done, but it's only worth doing if you're stuck with using dbms_output, and here you are not.

Instead, I would recommend using a couple of OUT bind variables to return the values from your PL/SQL block. I've also removed the calls to dbms_output.put_line as they achieve nothing:

sql = """
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN

SELECT SUBSTR(banner, INSTR(banner, 'Release')+8, 2) INTO v_version FROM v$version WHERE banner LIKE '%Oracle%';
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version > 12 THEN
    v_sql := 'select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,''[^:]+'',1,2),''[^(]+'',1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSIF v_version > 11 THEN
    v_sql := 'select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, ''12.[0-9].*''),''[^(]+'',1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSE
    v_sql := 'select max(replace(replace(replace(regexp_replace(comments, ''[^[:digit:].]''),''PSU'',''''),''64'',''''),''2021'','''')) keep (dense_rank last order by action_time) from registry$history';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  END IF;
  :bind_dbname := v_dbname;
  :bind_patch := v_patch;
END;"""

bind_dbname = cursor.var(str)
bind_patch = cursor.var(str)
cursor.execute(sql, bind_dbname=bind_dbname, bind_patch=bind_patch)
print(bind_dbname.getvalue())
print(bind_patch.getvalue())

I ran this against my Oracle 18c XE database, and the last two lines printed out XE and None. (The latter was to be expected, as registry$sqlpatch contains no rows.)