oracledb / cx_Oracle how to print DBMS_OUTPUT

In oracledb (formerly cx_Oracle), you can call a PL/SQL procedure or function which uses DBMS_OUTPUT and capture the output via the following:

import oracledb
import contextlib

conn = oracledb.connect(...)
cur = conn.cursor()


with dbms_output(cur, 'my_procedure'):
    cur.callproc('my_procedure')


@contextlib.contextmanager
def dbms_output(cur, plsql_name):
    cur.callproc('DBMS_OUTPUT.ENABLE')

    try:
        yield
    finally:
        _capture_and_log_dbms_output(cur, plsql_name)
        cur.callproc('DBMS_OUTPUT.DISABLE')


def _capture_and_log_dbms_output(cur, plsql_name):
    print('\n\n---------------------')
    print(f'DBMS_OUTPUT for {plsql_name} start:'\n\n)
    try:

        line = cur.var(str)
        status = cur.var(int)
        while True:
            cur.callproc('DBMS_OUTPUT.GET_LINE', (line, status))
            if status.getvalue() != 0:
                break

            print(line.getvalue())

    except Exception as ex:
        print(f'Failed to capture and log DBMS_OUTPUT: {ex}')
    finally:
        print('\n\n---------------------')
        print(f'DBMS_OUTPUT for {plsql_name} end')

oracledb / cx_Oracle how to print DBMS_OUTPUT

Comments

Add Comment