使用python-oracledb执行PL/SQL脚本的方法

huangapple
go评论67阅读模式
英文:

How to Execute PL/SQL Scripts With python-oracledb

问题

I have a script that was being executed with sqlplus until now, and I want to execute it with python. I checked the python-oracledb documentation but still couldn't figure it out.

What I tried doing is something like this:

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)
for implicit_cursor in cursor.getimplicitresults():
    for row in implicit_cursor:
        print(row)

and the error I am getting is:

Traceback (most recent call last):
  File "ora_runner.py", line 91, in <module>
    Query(un, pw, cs, False)
  File "ora_runner.py", line 83, in Query
    for implicit_cursor in cursor.getimplicitresults():
  File "/usr/local/lib64/python3.6/site-packages/oracledb/cursor.py", line 551, in getimplicitresults
    return self._impl.get_implicit_results(this.connection)
  File "src/oracledb/impl/thin/cursor.pyx", line 185, in oracledb.thin_impl.ThinCursorImpl.get_implicit_results
  File "/usr/local/lib64/python3.6/site-packages/oracledb/errors.py", line 111, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.InterfaceError: DPY-1004: no statement executed

Might enabling Thick Mode help?

<details>
<summary>英文:</summary>

I have a script that was being executed with sqlplus until now, and I want to execute it with python. I checked the python-oracledb documentation but still couldn&#39;t figure it out. 

What I tried doing is something like this:
```python
sql = &quot;&quot;&quot;
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN

SELECT SUBSTR(banner, INSTR(banner, &#39;Release&#39;)+8, 2) INTO v_version FROM v$version WHERE banner LIKE &#39;%Oracle%&#39;;
SELECT UPPER(name) INTO v_dbname FROM v$database;

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

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

and the error I am getting is:

Traceback (most recent call last):
  File &quot;ora_runner.py&quot;, line 91, in &lt;module&gt;
    Query(un, pw, cs, False)
  File &quot;ora_runner.py&quot;, line 83, in Query
    for implicit_cursor in cursor.getimplicitresults():
  File &quot;/usr/local/lib64/python3.6/site-packages/oracledb/cursor.py&quot;, line 551, in getimplicitresults
    return self._impl.get_implicit_results(self.connection)
  File &quot;src/oracledb/impl/thin/cursor.pyx&quot;, line 185, in oracledb.thin_impl.ThinCursorImpl.get_implicit_results
  File &quot;/usr/local/lib64/python3.6/site-packages/oracledb/errors.py&quot;, line 111, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.InterfaceError: DPY-1004: no statement executed

Might enabling Thick Mode help?

答案1

得分: 2

以下是您要翻译的内容：

"你之所以收到这个错误，是因为你正在执行的PL/SQL块中不包含任何隐式结果。基本上，你试图读取数据，但没有数据可供读取。

我注意到你的PL/SQL块中包含了对 dbms_output.put_line 的调用。请放心，调用 dbms_output.put_line 不会将任何内容写入隐式结果，因此 cursor.getimplicitresults() 无法返回以这种方式编写的任何输出。

虽然在SQL*Plus中使用 dbms_output 可能很方便，但在其他情况下可能不太方便。这是因为 dbms_output 保留了写入它的行的缓存，如果你要求的话，SQL*Plus（可能还有其他工具）将为你获取并显示这些行。在SQL*Plus之外，你将不得不自己检索这些行。这并非不可能，可以做到，但只有在你被迫使用 dbms_output 时才值得这样做，而在这种情况下你不需要。

相反，我建议使用一对OUT绑定变量来返回你的PL/SQL块中的值。我还删除了对 dbms_output.put_line 的调用，因为它们没有任何作用。

然后，我建议使用一对OUT绑定变量来返回你的PL/SQL块中的值。我还删除了对 dbms_output.put_line 的调用，因为它们没有任何作用："

sql = &quot;&quot;&quot;
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN

SELECT SUBSTR(banner, INSTR(banner, &#39;Release&#39;)+8, 2) INTO v_version FROM v$version WHERE banner LIKE &#39;%Oracle%&#39;;
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version &gt; 12 THEN
    v_sql := &#39;select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,&#39;&#39;[^:]+&#39;&#39;,1,2),&#39;&#39;[^(]+&#39;&#39;,1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSIF v_version &gt; 11 THEN
    v_sql := &#39;select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, &#39;&#39;12.[0-9].*&#39;&#39;),&#39;&#39;[^(]+&#39;&#39;,1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSE
    v_sql := &#39;select max(replace(replace(replace(regexp_replace(comments, &#39;&#39;[^[:digit:].]&#39;&#39;),&#39;&#39;PSU&#39;&#39;,&#39;&#39;&#39;&#39;),&#39;&#39;64&#39;&#39;,&#39;&#39;&#39;&#39;),&#39;&#39;2021&#39;&#39;,&#39;&#39;&#39;&#39;)) keep (dense_rank last order by action_time) from registry$history&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  END IF;
  :bind_dbname := v_dbname;
  :bind_patch := v_patch;
END;&quot;&quot;&quot;

bind_dbname = cursor.var(str)
bind_patch = cursor.var(str)
cursor.execute(sql, bind_dbname=bind_dbname, bind_patch=bind_patch)
print(bind_dbname.getvalue())
print(bind_patch.getvalue())

我在我的Oracle 18c XE数据库上运行了这个示例，最后两行打印出了 XE 和 None。（后者是可以预期的，因为 registry$sqlpatch 中没有行。）

英文:

You are receiving the error in question because the block of PL/SQL you are executing does not contain any implicit results. Essentially, you are attempting to read data when there is no data to read.

I note that your PL/SQL block contains calls to dbms_output.put_line. Please be assured that calling dbms_output.put_line does not write anything to implicit results, so cursor.getimplicitresults() will not be able to return any output written in such a way.

While dbms_output can be convenient to use in SQL*Plus, it is less convenient in other situations. This is because dbms_output keeps a cache of lines written to it, and SQL*Plus (and possibly other tools too) will fetch and display these lines for you if you ask it to. Outside of SQL*Plus, you will have to retrieve these lines yourself. It's not impossible, it can be done, but it's only worth doing if you're stuck with using dbms_output, and here you are not.

Instead, I would recommend using a couple of OUT bind variables to return the values from your PL/SQL block. I've also removed the calls to dbms_output.put_line as they achieve nothing:

sql = &quot;&quot;&quot;
DECLARE
v_version            VARCHAR(32);
v_dbname             VARCHAR(32);
v_patch              VARCHAR(32);
v_sql                VARCHAR(255);
BEGIN

SELECT SUBSTR(banner, INSTR(banner, &#39;Release&#39;)+8, 2) INTO v_version FROM v$version WHERE banner LIKE &#39;%Oracle%&#39;;
SELECT UPPER(name) INTO v_dbname FROM v$database;

IF v_version &gt; 12 THEN
    v_sql := &#39;select max(TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(description,&#39;&#39;[^:]+&#39;&#39;,1,2),&#39;&#39;[^(]+&#39;&#39;,1,1))) keep (dense_rank last order by action_time) from registry$sqlpatch&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSIF v_version &gt; 11 THEN
    v_sql := &#39;select max(REGEXP_SUBSTR(REGEXP_SUBSTR(description, &#39;&#39;12.[0-9].*&#39;&#39;),&#39;&#39;[^(]+&#39;&#39;,1,1)) keep (dense_rank last order by action_time) from registry$sqlpatch where bundle_series is not null&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  ELSE
    v_sql := &#39;select max(replace(replace(replace(regexp_replace(comments, &#39;&#39;[^[:digit:].]&#39;&#39;),&#39;&#39;PSU&#39;&#39;,&#39;&#39;&#39;&#39;),&#39;&#39;64&#39;&#39;,&#39;&#39;&#39;&#39;),&#39;&#39;2021&#39;&#39;,&#39;&#39;&#39;&#39;)) keep (dense_rank last order by action_time) from registry$history&#39;;
    EXECUTE IMMEDIATE v_sql INTO v_patch;
  END IF;
  :bind_dbname := v_dbname;
  :bind_patch := v_patch;
END;&quot;&quot;&quot;

bind_dbname = cursor.var(str)
bind_patch = cursor.var(str)
cursor.execute(sql, bind_dbname=bind_dbname, bind_patch=bind_patch)
print(bind_dbname.getvalue())
print(bind_patch.getvalue())

I ran this against my Oracle 18c XE database, and the last two lines printed out XE and None. (The latter was to be expected, as registry$sqlpatch contains no rows.)

huangapple
  • 本文由 发表于 2023年4月10日 19:56:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/75976886.html
  • oracle
  • plsql
  • python
  • python-oracledb
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定