how can i get the dbms_Output in Python

Question

I am trying to run a sql procedure in python. The running works but I don't get the dbms output that I get in oracle with the sql developer. Does anyone know how i can also get the dbms output. Here is my code how I call the procedure:

cursor.callproc('search', ('math', 'paris'))

Note that architecturally, this is very weird (and probably wrong). Returning data to an application via dbms_output is possible but very cumbersome and very fragile (things start breaking as soon as you call another procedure that writes to dbms_output for debugging purposes). It would be vastly more sensible (and common and stable) to have your procedure do something like return a sys_refcursor as an output parameter and have the client application fetch data from that. — Justin Cave
– Justin Cave, Commented Jun 7, 2022 at 16:17
can i use the output to continue working with dataframes pandas. So for example if i want to say if 'no offer' then break condition — sqll
– sqll, Commented Jun 7, 2022 at 17:31
@sqll - Can you? Sure. You can do just about anything. But if you build your application like this, you're constantly going to be in a position of trying to fit a round peg into a square hole. It's going to be vastly easier to pull the data from a sys_refcursor into a pandas dataframe than to pull some random dbms_output in. — Justin Cave
– Justin Cave, Commented Jun 7, 2022 at 18:34

Anthony Tuininga · Accepted Answer · 2022-06-07 15:24:39Z

1

See the sample that shows you how to do that. I will replicate it here, too:

import oracledb
import sample_env

# determine whether to use python-oracledb thin mode or thick mode
if not sample_env.get_is_thin():
    oracledb.init_oracle_client(lib_dir=sample_env.get_oracle_client())

connection = oracledb.connect(sample_env.get_main_connect_string())
cursor = connection.cursor()

# enable DBMS_OUTPUT
cursor.callproc("dbms_output.enable")

# execute some PL/SQL that generates output with DBMS_OUTPUT.PUT_LINE
cursor.execute("""
        begin
            dbms_output.put_line('This is the oracledb manual');
            dbms_output.put_line('');
            dbms_output.put_line('Demonstrating use of DBMS_OUTPUT');
        end;""")

# or for your case specifically
cursor.callproc("seach", ("math", "paris"))

# tune this size for your application
chunk_size = 10

# create variables to hold the output
lines_var = cursor.arrayvar(str, chunk_size)
num_lines_var = cursor.var(int)
num_lines_var.setvalue(0, chunk_size)

# fetch the text that was added by PL/SQL
while True:
    cursor.callproc("dbms_output.get_lines", (lines_var, num_lines_var))
    num_lines = num_lines_var.getvalue()
    lines = lines_var.getvalue()[:num_lines]
    for line in lines:
        print(line or "")
    if num_lines < chunk_size:
        break

edited Jun 7, 2022 at 15:24

answered Jun 7, 2022 at 14:55

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sqll Over a year ago

But the output that i would like is this : The subject, the city, the name, and the price. For example like this: Math Paris Michael Schuhmacher 13/h

sqll Over a year ago

can you show this with my example please, because i tried this before but dont get it

Anthony Tuininga Over a year ago

The important point is that you have to call dbms_output.enable() first, then call your stored procedure (instead of the anonymous PL/SQL block in our example), and then call the dbms_output.get_lines() method as shown at the bottom.

Anthony Tuininga Over a year ago

I presume that your procedure is callging dbms_output.put_line() somewhere?

MT0 Over a year ago

@sqll DBMS_OUTPUT writes the output to a buffer internal to the database. It does not write it to the application that calls the procedure. What you need to do is enable the buffer then call your procedure, which writes to the buffer, and then when it is done you need your application to read from the buffer. A client application like SQL Developer does the first and last steps automatically for you (most of the time); Python does not and you will need to do it.

|

d r · Accepted Answer · 2022-06-07 18:39:29Z

0

If you are using cx_Oracle ( https://oracle.github.io/python-cx_Oracle/ ) then the code could be like this:

import cx_Oracle
#   make a connection & create cursor
conn = cx_Oracle.connect('username', 'password', 'db')
cur = conn.cursor()

#   variable to colect serveroutputs into
dbmsRet = ''

#   SET SERVEROUTPUT ON 
cur.callproc("dbms_output.enable")

#   Pl/SQL Block
mPlSql = """-- testing serveroutput --
Declare 
    TestMsg VarChar2(50); 
Begin    
    TestMsg := 'Test no. 1'; 
    DBMS_OUTPUT.PUT_LINE(TestMsg); 
    TestMsg := Chr(9) || TestMsg || Chr(10) || Chr(9) || 'Test no. 2'; 
    DBMS_OUTPUT.PUT_LINE(TestMsg); 
End;
"""

#   Execute
mCmd = "cur.execute(mPlSql)"
exec(mCmd)

chunk = 100
# create variables to hold the output
mLine = cur.arrayvar(str, chunk)
mNumLines = cur.var(int)
mNumLines.setvalue(0, chunk)

# fetch the text that was added by PL/SQL
while True:
    cur.callproc("dbms_output.get_lines", (mLine, mNumLines))
    num_lines = int(mNumLines.getvalue())
    lines = mLine.getvalue()[:num_lines]
    for line in lines:
        dbmsRet = dbmsRet + line + '\n'
    if num_lines < chunk:
        break

#   got it 
print(dbmsRet)

#   R e s u l t :
#   Test no. 1
#       Test no. 1
#       Test no. 2

Asking about pandas - if you add

import pandas as pd

and change the end of the code to

    myList = []
    for line in lines:
        myList.append(line)
        dbmsRet = dbmsRet + line + '\n'
    if num_lines < chunk:
        break

#   got it 
df = pd.DataFrame(myList)
print(df)
print(type(df))

#   The Result would be
#                              0
#  0                  Test no. 1
#  1  \tTest no. 1\n\tTest no. 2
#  <class 'pandas.core.frame.DataFrame'>

edited Jun 7, 2022 at 18:39

answered Jun 7, 2022 at 16:00

d r

8,6622 gold badges9 silver badges19 bronze badges

2 Comments

sqll Over a year ago

can i use the output to continue working with dataframes pandas. So for example if i want to say if 'no offer' then break condition

d r Over a year ago

You can do whatever you want with it. In this answer I just collected the DBMS_OUTPUT lines in a string variable (dbmsRet) and print it. To use it for pandas df you should consider collecting it as a csv or list or whatever suites you the most.

akshay_sushir · Accepted Answer · 2023-06-15 11:24:58Z

0

How to call oracle procedure from python and print output to console? If you are using cx_Oracle then try given example:

import cx_Oracle

 # Connect to the Oracle database.
connection = cx_Oracle.connect("user/password@localhost:1521/database")

# Create a cursor object.
cursor = connection.cursor()

# Use "dbms_output.enable" instead of "set serveroutput on;" to print output in console
cursor.callproc("dbms_output.enable")

procedure_sql = '''
create or replace PROCEDURE MIGRATION_PROCEDURE
YOUR_SQL_QUERY...
'''

# create procedure
cursor.execute(procedure_sql)

# call procedure using callproc() method
cursor.callproc('MIGRATION_PROCEDURE')

# set variables to capture output in str or int format
statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)

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


# Close the cursor and connection
cursor.close()
conn.close()

answered Jun 15, 2023 at 11:24

akshay_sushir

1,96116 silver badges11 bronze badges

1 Comment

Christopher Jones Over a year ago

The driver documentation Using DBMS_OUTPUT discusses this and shows code. And there's a runnable example dbms_output.py on GitHub. For the cx_Oracle / python-oracledb name change, see the release announcement.

Collectives™ on Stack Overflow

how can i get the dbms_Output in Python

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related