Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Oracle procedure with refcursor

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 *;

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

And anything works, any idea?

Thanks in advance.

Ariel

3 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
offjunior
Creator
Creator

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.

Configuração do Script.jpg

Step 2:

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

"Mode is write"

String Conexão ODBC.jpg

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');"

Chamada da Procedure.jpg

I think this should work, I have helped.

Hug!

maxgro
MVP
MVP

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

AS

  c1 t_cursor;

BEGIN

    OPEN C1 FOR

        SELECT TABLE_NAME FROM user_tables where table_name like p_table;

    RETURN C1;

END;

   END pkg1;

/