4 Replies Latest reply: Mar 2, 2012 4:32 PM by Ralf Becher RSS

    Data via ODBC from Oracle stored procedure which returns ref cursor

    Magnus ÅVITSLAND



      My client wants to load data via ODBC from an Oracle stored procedure which returns a ref cursor.

      Is that possible?


      The client uses:

      • Windows Server 2008 R2 x64
      • QlikView v11 IR
      • Oracle 10 and ODBC drivers
      • Oracle 11and ODBC drivers


      I successfully manage to load data from the procedure when using the Oracle native tool SQLPlus by doing like this:

      • Declare variable
      • Fill the variable with recordset from query result
      • Print variable content


      Any help would be highly appreciated!


      I have tried all from the following threads without any bit of luck:





      Even my friend Google was unable to assist me.



      It seems strange that it should be so different in Oracle.

      Oracle is in fact one of the bigger DBMS:s in the world.

      And ref cursors are used commonly.



      Kind regards


      Magnus Åvitsland

      BI Consultant

      Framsteg Business Intelligence Corp.

        • Re: Data via ODBC from Oracle stored procedure which returns ref cursor
          Ralf Becher

          Hi Magnus,


          this is a very interesting topic I have also asked around and had no solution. Now I dig a bit deeper into it and I've got a solution which might be interesting for you but has the following limitations:


          1. only a function call is working (not a SP)

          2. it's using sys_refcursor instead of type REF CURSOR


          I've build the following test function:


          // Oracle PL/SQL:

          create or replace

          function f_get_tables return sys_refcursor


            c1 sys_refcursor;


              OPEN C1 FOR

                  SELECT TABLE_NAME FROM user_tables;

              RETURN C1;



          Which can be called in the QlikView Load Script:



          SQL { ? = call f_get_tables() };



          Usually this should work also with a stored procedure but in this case I've got an

          ORA-01008: not all variables bound..


          Also interesting is that this statement (on sqldeveloper) gives back all values from all rows in one XML-like string:


          select f_get_tables() from dual;




          - Ralf