Capturing stdout output from stored procedures with cx_Oracle

Is there a way in cx_Oracle to capture the stdout output from an oracle stored procedure? These show up when using Oracle's SQL开发者_StackOverflow社区 Developer or SQL Plus, but there does not seem to be a way to fetch it using the database drivers.

You can retrieve dbms_output with DBMS_OUTPUT.GET_LINE(buffer, status). Status is 0 on success and 1 when there's no more data.

You can also use get_lines(lines, numlines). numlines is input-output. You set it to the max number of lines and it is set to the actual number on output. You can call this in a loop and exit when the returned numlines is less than your input. lines is an output array.

Herby a code example based on redcayuga's first answer:

def dbms_lines( cursor):
    status = cursor.var( cx_Oracle.NUMBER)
    line   = cursor.var( cx_Oracle.STRING)

    lines = []
    while True:
        cursor.callproc( 'DBMS_OUTPUT.GET_LINE', (line, status))
        if status.getvalue() == 0:
            lines.append( line.getvalue())
        else:
            break

    return lines

Then run it after calling your stored procedure with:

    for line in dbms_lines( cursor):
        log.debug( line)

Whatever you put using put_line, you read using get_line; I believe this is how all these tools work, probably including the very SQL*Plus.

Note that you need to call get_line enough times to exhaust the buffer. If you don't, the unread part will be overwritten by the next put_line.

Do not forget to call

cursor.callproc("dbms_output.enable") 

before calling your actual procedure, otherwise the buffer will be empty.

So building on the other two answers here, an example would be (proc_name is your procedure - schema.package.procedure):

def execute_proc(cursor,proc_name):
    cursor.callproc("dbms_output.enable")
    cursor.callproc(proc_name)
    for line in dbms_lines( cursor):
        print( line)

Did you tried this?

>>> conn = cx_Oracle.connect('user/pw@SCHEMA')
>>> cursor = conn.cursor()
>>> output = cursor.callproc("dbms_output.put_line", ['foo',])
>>> output
['foo']

The first argument is the procedure to call and the second a sequence of arguments or a dict for bindvars.

see also: http://cx-oracle.sourceforge.net/html/cursor.html