topic Oracle procedure with refcursor in QlikView

Oracle procedure with refcursor

Anonymous — Tue, 02 Dec 2014 19:29:40 GMT

Hi all,

I need execute an Oracle SP with refcursor as output, something like this:

SCHEMA.SP_NAME (Par1, Par2, CURSOR_OUTPUT);

I've tried all of these:

LOAD *;

SQL CALL SCHEMA.SP_NAME ('$(var1)', '$(var2)', '$(var3)');
SQL CALL SCHEMA.SP_NAME ('$(var1)', '$(var2)');
select SCHEMA.SQL CALL SP_NAME('$(var1)', '$(var2)') as output from SCHEMA;
SQL {? = CALL SCHEMA.SP_NAME('$(var1)', '$(var2)', '$(var3)') } ;
SQL select SCHEMA.SP_NAME('$(var1)', '$(var2)', '$(var3)') from dual;

And anything works, any idea?

Thanks in advance.

Ariel

Re: Oracle procedure with refcursor

Gysbert_Wassenaar — Wed, 03 Dec 2014 12:08:44 GMT

Qlikview can't do anything with a cursor reference. I think you'll have to wrap that SP in another SP that runs through the cursor and returns a set of records instead of a cursor.

Re: Oracle procedure with refcursor

offjunior — Thu, 23 Apr 2015 16:43:31 GMT

Hello Friend!

On the operation of the cursor I do not know if it will work as our friend quoted. Now about calling procedures from within QlikView, if you have not achieved, you can try the way that I will describe below.

Step 1:

Set your script editor and it will open the databases in read and write mode.

Step 2:

Correctly write your connection string, it is ODBC or OLEDB, add, before or after the password the following content

"Mode is write"

Step 3:

Write correctly the call of Procedure that you want to call, in my example will look like this ...

"SQL call user_bi.prc_custo_padrão_real('4.2014','007');"

I think this should work, I have helped.

Hug!

Re: Oracle procedure with refcursor

maxgro — Thu, 23 Apr 2015 19:35:59 GMT

this is an old post, anyway, with an ODBC connection this works in my environment

// QlikView

set vTable='%E%';

Table:

LOAD *;

SQL {? = CALL pkg1.f_get_tables2('$(vTable)') } ;

// Oracle

CREATE OR REPLACE PACKAGE TEST.pkg1 AS

TYPE t_cursor IS REF CURSOR;

function f_get_tables2 (p_table varchar2) return t_cursor;

END pkg1;

CREATE OR REPLACE PACKAGE BODY TEST.pkg1 AS

function f_get_tables2 (p_table varchar2) return t_cursor

c1 t_cursor;

BEGIN

OPEN C1 FOR

SELECT TABLE_NAME FROM user_tables where table_name like p_table;

RETURN C1;

END;

END pkg1;