我如何在Python中獲取dbms_Output

我正在嘗試在 python 中運行一個 sql 程序。運行正常，但我沒有得到我在 oracle 中使用 sql 開發人員獲得的 dbms 輸出。有誰知道我如何也可以獲得 dbms 輸出。這是我如何呼叫該程序的代碼：

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

uj5u.com熱心網友回復：

請參閱向您展示如何執行此操作的示例。我也會在這里復制它：

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

uj5u.com熱心網友回復：

如果您使用的是 cx_Oracle ( https://oracle.github.io/python-cx_Oracle/ )，那么代碼可能是這樣的：

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    

詢問熊貓 - 如果您添加

import pandas as pd

并將代碼的結尾更改為

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

標籤：Python 甲骨文 CX-甲骨文

上一篇：多個洗掉觸發器陳述句不起作用

下一篇：OOTBOracleIFERROR函式用于處理導致查詢錯誤的行