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

[vishsaggi]

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>

[danosoft]

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]

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.

[danosoft]

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]

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)

[danosoft]

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

[vishsaggi]

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

[danosoft]

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]

So did it work with your Call function?