Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ariel_insua
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

Tags (2)
3 Replies
Gysbert_Wassenaar
Not applicable

Re: Oracle procedure with refcursor

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
Not applicable

Re: Oracle procedure with refcursor

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
Not applicable

Re: Oracle procedure with refcursor

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;

/