Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

d_caprelli
Contributor II

Oracle Procedure

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Oracle Procedure

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

1.png

16 Replies
vishsaggi
Esteemed Contributor III

Re: Oracle Procedure

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>

d_caprelli
Contributor II

Re: Oracle Procedure

yes, but it say:

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

SQL Exec "MYSCHEMA"."PROVADANO"

or

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

SQL CALL "MYSCHEMA"."PROVADANO"

vishsaggi
Esteemed Contributor III

Re: Oracle Procedure

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.

d_caprelli
Contributor II

Re: Oracle Procedure

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?

vishsaggi
Esteemed Contributor III

Re: Oracle Procedure

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?

Capture.PNG

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

Capture.PNG

d_caprelli
Contributor II

Re: Oracle Procedure

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

vishsaggi
Esteemed Contributor III

Re: Oracle Procedure

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

d_caprelli
Contributor II

Re: Oracle Procedure

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

vishsaggi
Esteemed Contributor III

Re: Oracle Procedure

So did it work with your Call function?

Community Browser