topic Re: Oracle Procedure in QlikView

Oracle Procedure

danosoft — Mon, 26 Jan 2026 18:19:17 GMT

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

SQL CALL "PROVADANO";

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

thanks

Re: Oracle Procedure

vishsaggi — Wed, 11 Oct 2017 15:07:31 GMT

IS your qlikview user given permission on that server. Like just grant permission like here:

How to Call stored procedure in Qlikview

GRANT EXEC ON dbo.xxxxxxx TO <UserName>

Re: Oracle Procedure

danosoft — Wed, 11 Oct 2017 15:45:24 GMT

yes, but it say:

SQL##f - SqlState: 37000, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Syntax error or access violation.

SQL Exec "MYSCHEMA"."PROVADANO"

SQL##f - SqlState: S1000, ErrorCode: 6576, ErrorMsg: [Oracle][ODBC][Ora]ORA-06576: not a valid function or procedure name

SQL CALL "MYSCHEMA"."PROVADANO"

Re: Oracle Procedure

vishsaggi — Wed, 11 Oct 2017 18:08:12 GMT

Ask your DBA guys to Grant EXECCUTE permission to this qlikview user account so tell them to run like

GRANT EXEC ON MYSCHEME.PROVADANO TO <qlikviewuseraccountname>

I doubt you cant use CALL function here coz you want to execute Stored Procedure not to call a snippet of code.

Re: Oracle Procedure

danosoft — Thu, 12 Oct 2017 06:50:26 GMT

I already did that, my code is a PROCEDURE in Oracle but when i run:

SQL Exec "ETRM_COMMON"."PROVADANO";;

it do this error

SQL##f - SqlState: 37000, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Syntax error or access violation.

SQL Exec "MYSCHEMA"."PROVADANO";

if I tried to call a funcion like the before procedure (it have the same code) with command:

LOAD *;

SQL {? = CALL MYSCHEMA.PROVADANOFN() } ;

all is ok, it start

so what the problem with a Procedure, why it say that error?

Re: Oracle Procedure

vishsaggi — Thu, 12 Oct 2017 13:43:22 GMT

The error is clearly saying Syntax or Access Violation. So i believe there is not any syntax and it would be access violation. However, did you check below?

And in User Preferences using Ctrl+Alt+U -> Check Script (Allow Database write and execute statements)

Re: Oracle Procedure

danosoft — Thu, 12 Oct 2017 13:56:02 GMT

the second image yes is ok, in my document. but the first image where i can find those options?

Re: Oracle Procedure

vishsaggi — Thu, 12 Oct 2017 14:15:43 GMT

When you go to edit script in the bottom you can see that settings tab.

Re: Oracle Procedure

danosoft — Thu, 12 Oct 2017 15:45:53 GMT

ok i did it, the problem is the same:

SQL##f - SqlState: 37000, ErrorCode: 0, ErrorMsg: [Oracle][ODBC]Syntax error or access violation.

SQL Exec "ETRM_COMMON"."PROVADANO"

possible for call a Procedure the sintax is different?

This is my procedure:

CREATE OR REPLACE PROCEDURE PROVADANO (prc out sys_refcursor) AS

BEGIN

open prc for

SELECT * FROM "ETRM_XXX"."V_XXX" aa

END PROVADANO;

becouse if i call a function with

LOAD *;

SQL {? = CALL MYSCHEMA.PROVADANOFN() } ;

all is ok

Re: Oracle Procedure

vishsaggi — Thu, 12 Oct 2017 17:08:59 GMT

So did it work with your Call function?

Re: Oracle Procedure

maxgro — Thu, 12 Oct 2017 18:26:02 GMT

this worked for me, with Oracle ODBC

// Qlik

LOAD *;

SQL

select

{? = CALL MYFUNC };

// Oracle

CREATE OR REPLACE FUNCTION MYFUNC

RETURN SYS_REFCURSOR

lv_ret_refcursor SYS_REFCURSOR;

BEGIN

OPEN lv_ret_refcursor FOR

SELECT 'SUCCESS' as result FROM DUAL;

RETURN lv_ret_refcursor;

END ;

Re: Oracle Procedure

danosoft — Fri, 13 Oct 2017 06:38:35 GMT

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

Re: Oracle Procedure

danosoft — Fri, 13 Oct 2017 06:39:42 GMT

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

Re: Oracle Procedure

jonathandienst — Fri, 13 Oct 2017 06:53:43 GMT

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.

Re: Oracle Procedure

danosoft — Fri, 13 Oct 2017 07:23:55 GMT

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

Re: Oracle Procedure

maxgro — Fri, 13 Oct 2017 08:04:58 GMT

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

Re: Oracle Procedure

jonathandienst — Mon, 16 Oct 2017 09:07:03 GMT

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