如何通过ODBC检索Oracle数据库功能的结果? [英] How to retrieve result of Oracle database function via ODBC?

问题描述

我在通过ODBC调用Oracle存储函数(不是过程)时遇到问题.

I have problem with calling Oracle stored function (not procedure) via ODBC.

我的函数真的很简单，它仅连接两个字符串.我可以通过以下方式调用它:

My function is really simple, it just concatenates two strings. I can call it via:

rs = c.execute("SELECT add_str('yogi', 'bubu') FROM dual")
for row in c.fetchall():
    print(row[0])

但是这种类型的调用数据库函数不适用于更改数据库的函数.所以我尝试了这个:

But such type of calling database function will not work for functions that changes database. So I tried this:

c.execute("{ ? = call add_str('ala', 'bubu') }");

但是我得到了

Error: HY000: The driver did not supply an error!

在ODBC跟踪文件中，它看起来像:

In ODBC trace file it looks like:

python.exe odbc a20-e68 ENTER SQLExecDirect 
        HSTMT               00A02CE0
        UCHAR *             0x00AA6CE4 [      -3] "{ ? = call add_str('ala', 'bubu') }\ 0"
        SDWORD                    -3

python.exe odbc a20-e68 EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)
        HSTMT               00A02CE0
        UCHAR *             0x00AA6CE4 [      -3] "{ ? = call add_str('ala', 'bubu') }\ 0"
        SDWORD                    -3

python.exe odbc a20-e68 ENTER SQLGetDiagRecW 
        SQLSMALLINT                  3 
        SQLHANDLE           00A02CE0
        SQLSMALLINT                  1 
        SQLWCHAR *          0x0021F7BC (NYI) 
        SQLINTEGER *        0x0021F808
        SQLWCHAR *          0x00A035F8 (NYI) 
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x0021F818

python.exe odbc a20-e68 EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  3 
        SQLHANDLE           00A02CE0
        SQLSMALLINT                  1 
        SQLWCHAR *          0x0021F7BC (NYI) 
        SQLINTEGER *        0x0021F808
        SQLWCHAR *          0x00A035F8 (NYI) 
        SQLSMALLINT               1023 
        SQLSMALLINT *       0x0021F818

使用JDBC/Jython可以进行类似的操作:

Similar operation is possible with JDBC/Jython:

proc = db.prepareCall("{ ? = call add_str('j_bubu', 'j_yogi') }");
proc.registerOutParameter(1, Types.VARCHAR)
r = proc.execute();
print(proc.getString(1))

，因此应该可以使用ODBC& Python.我已经检查过，如果我使用JDBC-ODBC桥，那么Jython可以像本地JDBC驱动程序一样通过ODBC调用数据库功能.

so it should be possible to do similar thing using ODBC & Python. I have checked that if I used JDBC-ODBC bridge then Jython can call database function via ODBC just like native JDBC driver.

我的环境:

数据库:Oracle Database 11g企业版11.2.0.1.0版-64位生产

Database: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

ODBC驱动程序:11.01.00.06

ODBC driver: 11.01.00.06

SQORA32.DLL FileVersion:11.1.0.6.0

SQORA32.DLL FileVersion: 11.1.0.6.0

我尝试了ActiveState Python 2.7和pyodbc模块中的odbc模块.

I tried odbc module from ActiveState Python 2.7 and pyodbc module.

我的问题:

是否可以通过其ODBC驱动程序调用Oracle数据库函数(而不是过程)?以及如何检索其结果?

Is there any way of calling Oracle database function (not procedure) via their ODBC driver? And how to retrieve its result?

推荐答案

Google调出了此页面:

Google brought up this page:

http://www.gossamer-threads.com/lists/python/python/71449

... 我认为ODBC不支持返回值的存储过程 直接地.仅作为游标的一部分. ...

... I don't think ODBC supports stored procedures returning a value directly. Only as part of a cursor. ...

基本上，您不能直接调用该函数.但是，您可以改为使用OUT参数创建过程.

Basically, you can't call the function directly. You could, however, create a procedure with an OUT parameter instead.

这篇关于如何通过ODBC检索Oracle数据库功能的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！