CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
sqll
sqll

Reputation: 11

how can i get the dbms_Output in Python

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'))

Upvotes: 0

Views: 2552

Answers (3)

akshay_sushir
akshay_sushir

Reputation: 1841

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()

Upvotes: 0

d r
d r

Reputation: 7786

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'>

Upvotes: 0

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

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

Upvotes: 1

Related Questions