topic Data via ODBC from Oracle stored procedure which returns ref cursor in QlikView

Data via ODBC from Oracle stored procedure which returns ref cursor

magavi_framsteg — Thu, 26 Jan 2012 11:09:21 GMT

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

rbecher — Fri, 27 Jan 2012 13:31:40 GMT

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;

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

Data via ODBC from Oracle stored procedure which returns ref cursor

magavi_framsteg — Fri, 27 Jan 2012 13:49:30 GMT

Hi Ralf.

Thanks for your quick reply, I will certainly try this next week at my client.

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

magavi_framsteg — Fri, 02 Mar 2012 20:00:46 GMT

Hi again Ralf.

I tried your bold QV script code and got the same result as you - as expected.

However, I could not try and make the SP a function and have it return a SYS_REFCURSOR instead of a REF CURSOR, because the client already decided we could use views instead. Good for us 😃

But the problem remains, and I'm sure I will encounter the problem many times in the future.

It is very strange that the QV data layers doesn't work something like this instead:

1. Make ODBC/OLEDB (or other) connection

2. Submit query

3. If the query is something like SELECT StoredProcedure/Function from dual (and which returns some kind of (SYS)REF CURSOR), then the QV data layer should detect that the result set comes in the form of a variable CURSOR and do some magic.

3. Return result as if it was a standard query like SELECT * FROM TABLE/VIEW

From a user (developer) perspective, I should not have to write a custom macro with VB Script to take care of the "special Oracle cursors" and have the macro create the QVD for me.

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;

It seems that SQLDeveloper does just what I described above. In SQLPlus I need to declare a variable, put the SP result into the variable, then print the variable.

Thanks for your input.

To be continued...

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

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

rbecher — Fri, 02 Mar 2012 21:32:23 GMT

Hi Magnus,

thanks for your response. Maybe we can add this functionality in our JDBC Connector..

http://community.qlik.com/docs/DOC-2438

- Ralf