Oracle Procedure

[danosoft]

Hi i have this procedure in oracle in my schema:

CREATE OR REPLACE PROCEDURE PROVADANO (prc out sys_refcursor) AS

BEGIN

   open prc for

    SELECT *  FROM "ETRM_XXX"."V_XXX" aa

END PROVADANO;

how can i call this in qlikview?

i tried with:

SQL EXECUTE "MYSCHEMA"."PROVADANO";

or SQL EXECUTE "PROVADANO";

and with

SQL CALL "MYSCHEMA"."PROVADANO";

or

SQL CALL "PROVADANO";

but nothing it say not find the procedure, where i wrong?

thanks

[maxgro]

this worked for me, with Oracle ODBC

// Qlik

t:

LOAD *;

SQL

select

{? = CALL MYFUNC  };

// Oracle

CREATE OR REPLACE FUNCTION MYFUNC

    RETURN SYS_REFCURSOR

IS

      lv_ret_refcursor  SYS_REFCURSOR;

BEGIN

    OPEN lv_ret_refcursor FOR

    SELECT 'SUCCESS' as result    FROM DUAL;

    RETURN lv_ret_refcursor;

END ;

[danosoft]

Like i wrote: i NOT have problem with functions, those work for me too, i have problem with PROCEDURE

[danosoft]

Like i wrote, CALL function work with FUNCTIONS in oracle, i have problem with the PROCEDURE

[jonathandienst]

Remember that the entire text between the SQL statement and the closing semi-colon is sent verbatim to the Oracle server and there executed by Oracle.I would test this call/exec in a tool like TOAD, preferably using the same login as your QV server account. Once you have it working there, copy the working SQL statement back to QV.

[danosoft]

Thanks Jonathan i think you find the way.... for sqlnavigator i need to execute it with those statement:

variable prc refcursor;
Execute "ETRM_COMMON"."PROVADANO"(:PRC);

"Procedura PL/SQL completata correttamente."

and it work OK from sql navigator

I think the problem is i need to create the first instruction in QlikView too, how can i call that statement in Qlik (with declaration of prc refcursor and the execute?

Thanks

[maxgro · Accepted solution]

Oracle procedure

CREATE OR REPLACE PROCEDURE test2 (prc out sys_refcursor) AS

BEGIN

   open prc for

    SELECT sysdate  FROM dual

    union select sysdate+1 from dual;

END ;

Qlik

t2:

SQL

{CALL test2  };

Result

[jonathandienst]

>> think the problem is i need to create the first instruction in QlikView too,

I expect that you do need to include it. I am not currently using Oracle (its about 10 years ago that I last used Oracle), and I am a little rusty. Is the semi-colon required after the variable declaration? If it is, your bext approach may be waht Massimo proposed. If not, then you could say:

SQL

variable prc refcursor

Execute "ETRM_COMMON"."PROVADANO"(:PRC);