如何用python-oracledb执行PL/SQL脚本？执行报错求解

报错原因 #

你遇到的DPY-1004: no statement executed错误，核心原因是代码逻辑误解了getimplicitresults()的作用：

• getimplicitresults()仅用于获取PL/SQL中通过DBMS_SQL.RETURN_RESULT显式返回的结果集，或者通过REF CURSOR输出的数据集。
• 你的PL/SQL脚本用的是dbms_output.put_line()输出内容，这属于Oracle的服务器端输出缓冲区，并非隐式结果集，所以调用getimplicitresults()会报错。

启用Thick Mode能否解决？ #

不能。这个问题和Thin/Thick模式无关，不管切换到哪种模式，用getimplicitresults()读取dbms_output的输出都是行不通的。

正确的解决方法 #

有两种可行方案，根据需求选择：

方案1：改用返回结果集替代dbms_output #

修改PL/SQL脚本，直接通过DBMS_SQL.RETURN_RESULT输出内容，这样就能用getimplicitresults()获取：

sql = """
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN
SELECT SUBSTR(banner, INSTR(banner, 'Release')+8, 2) INTO v_version FROM v$version WHERE banner LIKE '%Oracle%';
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version > 12 THEN
    v_sql := 'select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,''[^:]+'',1,2),''[^(]+'',1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    DBMS_SQL.RETURN_RESULT(DBMS_SQL.TO_CURSOR('SELECT ''oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"'' AS output FROM DUAL'));
ELSIF v_version > 11 THEN
    v_sql := 'select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, ''12.[0-9].*''),''[^(]+'',1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    DBMS_SQL.RETURN_RESULT(DBMS_SQL.TO_CURSOR('SELECT ''oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"'' AS output FROM DUAL'));
ELSE
    v_sql := 'select max(replace(replace(replace(regexp_replace(comments, ''[^[:digit:].]''),''PSU'',''''),''64'',''''),''2021'','''')) keep (dense_rank last order by action_time) from registry$history';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    DBMS_SQL.RETURN_RESULT(DBMS_SQL.TO_CURSOR('SELECT ''oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"'' AS output FROM DUAL'));
END IF;
END;
"""

cursor.execute(sql)
for implicit_cursor in cursor.getimplicitresults():
    for row in implicit_cursor:
        print(row[0])

方案2：启用并读取dbms_output #

如果想保留原PL/SQL的dbms_output.put_line()逻辑，只需在python-oracledb中启用dbms_output缓冲区，执行脚本后读取输出即可，Thin和Thick模式都支持：

# 启用dbms_output，设置缓冲区大小为无限制
cursor.execute("BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;")

sql = """
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN
SELECT SUBSTR(banner, INSTR(banner, 'Release')+8, 2) INTO v_version FROM v$version WHERE banner LIKE '%Oracle%';
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version > 12 THEN
    v_sql := 'select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,''[^:]+'',1,2),''[^(]+'',1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
ELSIF v_version > 11 THEN
    v_sql := 'select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, ''12.[0-9].*''),''[^(]+'',1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
ELSE
    v_sql := 'select max(replace(replace(replace(regexp_replace(comments, ''[^[:digit:].]''),''PSU'',''''),''64'',''''),''2021'','''')) keep (dense_rank last order by action_time) from registry$history';
    EXECUTE IMMEDIATE v_sql INTO v_patch;
    dbms_output.put_line('oracle_sql_patch,db='||v_dbname||' dbver="'||v_patch||'"');
END IF;
END;
"""

cursor.execute(sql)

# 循环读取dbms_output的内容
cursor.execute("BEGIN DBMS_OUTPUT.GET_LINE(:line, :status); END;", line=oracledb.STRING, status=oracledb.NUMBER)
while cursor.status == 0:
    print(cursor.line)
    cursor.execute("BEGIN DBMS_OUTPUT.GET_LINE(:line, :status); END;", line=oracledb.STRING, status=oracledb.NUMBER)

内容的提问来源于stack exchange，提问作者veysel