Oracle procedure with refcursor

[Anonymous]

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

[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.

[offjunior]

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!

[maxgro]

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;

/