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