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 Developer or SQL Plus, but there does not seem to be a way to fetch it using the database drivers.
Add a comment
|
5 Answers
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.
Comments
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)
Comments
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.
Comments
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)
Comments
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.
1 Comment
wds
That's the input parameter it's returning to you, not the result of dbms_output.put_line.