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

      Hi.

       

      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:

      http://community.qlik.com/message/154180#154180

      http://community.qlik.com/message/154204#154204

      http://community.qlik.com/message/14521#14521

       

      Even my friend Google was unable to assist me.

      http://www.google.se/search?q=qlikview+oracle+cursor&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

       

      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

          AS

            c1 sys_refcursor;

          BEGIN

              OPEN C1 FOR

                  SELECT TABLE_NAME FROM user_tables;

              RETURN C1;

          END;

           

          Which can be called in the QlikView Load Script:

           

          tables:

          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