0

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') }");

But I got:

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

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

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

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.

My environment:

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

ODBC driver: 11.01.00.06

SQORA32.DLL FileVersion: 11.1.0.6.0

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

My question:

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

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Have you tried the select statement from SQL*Plus/SQL Navigator etc to make sure that it actually works. Could be a bug in the function. – Michael Ballent Jul 18 '12 at 20:56
  • Function works if I call it from JDBC with `registerOutParameter()` so the problem is in ODBC driver or in Python code that wraps ODBC functions. – Michał Niklas Jul 19 '12 at 05:09
  • Making test with JDBC-ODBC bridge I see that it probably is not problem with ODBC driver but in Python code that wraps ODBC functions. – Michał Niklas Jul 19 '12 at 07:01

1 Answers1

0

Google brought up this page:

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

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

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

Adam Hawkes
  • 7,218
  • 30
  • 57
  • In my JDBC/Jython test program I changed driver from native to JDBC-ODBC bridge and it works! So the problem is probably not in the ODBC driver but in Python code that wraps ODBC library. – Michał Niklas Jul 19 '12 at 06:51