title: How to retrieve result of Oracle database function via ODBC?
body: <p>I have problem with calling Oracle stored function (not procedure) via ODBC.</p>
  
  <p>My function is really simple, it just concatenates two strings. I can call it via:</p>
  
  <pre><code>rs = c.execute("SELECT add_str('yogi', 'bubu') FROM dual")
  for row in c.fetchall():
      print(row[0])
  </code></pre>
  
  <p>But such type of calling database function will not work for functions that changes database. So I tried this:</p>
  
  <pre><code>c.execute("{ ? = call add_str('ala', 'bubu') }");
  </code></pre>
  
  <p>But I got:</p>
  
  <pre><code>Error: HY000: The driver did not supply an error!
  </code></pre>
  
  <p>In ODBC trace file it looks like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Similar operation is possible with <code>JDBC/Jython</code>:</p>
  
  <pre><code>proc = db.prepareCall("{ ? = call add_str('j_bubu', 'j_yogi') }");
  proc.registerOutParameter(1, Types.VARCHAR)
  r = proc.execute();
  print(proc.getString(1))
  </code></pre>
  
  <p>so it should be possible to do similar thing using ODBC &amp; Python. I have checked that if I used JDBC-ODBC bridge then Jython can call database function via ODBC just like native JDBC driver.</p>
  
  <p>My environment:</p>
  
  <p>Database: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production</p>
  
  <p>ODBC driver: 11.01.00.06</p>
  
  <p>SQORA32.DLL FileVersion: 11.1.0.6.0</p>
  
  <p>I tried <code>odbc</code> module from ActiveState Python 2.7 and <code>pyodbc</code> module.</p>
  
  <p>My question:</p>
  
  <p>Is there any way of calling Oracle database function (not procedure) via their ODBC driver? And how to retrieve its result?</p>