topic Executing Oracle Stored Procedure from Qlikview not working in QlikView

Executing Oracle Stored Procedure from Qlikview not working

Wed, 25 Nov 2020 16:16:04 GMT

i want call Oracle Store Procedure from Qlikview, but not wroking.

Qlikview Scrip : SQL EXEC APPS.GGGG_TES22;

Error Message :

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

SQL EXEC APPS.GGGG_TES22

ss:

oracle database Store Procedure :

CREATE OR REPLACE PROCEDURE APPS.GGGG_TES22 IS

BEGIN

null;

END;

Qlikview Scrip Setting :

Whats Wrong, Anyone Can help me?

Regard,

Hertono.

Re: Executing Oracle Stored Procedure from Qlikview not working

Peter_Cammaert — Fri, 07 Apr 2017 07:59:55 GMT

This looks like a DBMS problem, not really a QlikView problem. Is null; an accepted PL/SQL statement?

What do you get if you return at least some data through the ODBC connection?

Re: Executing Oracle Stored Procedure from Qlikview not working

Fri, 07 Apr 2017 08:48:02 GMT

i try update store procedure to :

CREATE OR REPLACE PROCEDURE APPS.GGGG_TES22 IS

BEGIN

INSERT INTO TES_INVOICES_STAGING VALUES(1,1000);

END;

error :

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

SQL EXEC APPS.GGGG_TES22;

i want insert data to TES_INVOICES_STAGING VALUES using Oracle Store Procedure, then select Data from table TES_INVOICES_STAGING VALUES using Qlikview

Example :

SQL EXEC APPS.GGGG_TES22;

INVOICE_STG:

SQL

SELECT *

FROM TES_INVOICES_STAGING VALUES;

EXIT Script;

Re: Executing Oracle Stored Procedure from Qlikview not working

Peter_Cammaert — Fri, 07 Apr 2017 09:52:41 GMT

Does the account that is used to make a connection have all the required permissions to run Stored Procedures and Insert data into that particular table?

Re: Executing Oracle Stored Procedure from Qlikview not working

Peter_Cammaert — Fri, 07 Apr 2017 09:55:17 GMT

Another suggestion: try running the INSERT statement as a simple SQL statement in your QlikView script. Don't forget to enable the "Open Databases in Read and Write mode" checkbox on the Settings tab in the Script Editor.

Re: Executing Oracle Stored Procedure from Qlikview not working

Fri, 07 Apr 2017 09:57:35 GMT

i already use apps Account, and make sure to DBA team, this account can execute store procedure,

i already test to execute this procedure using TOAD editor , using APPS account.

Re: Executing Oracle Stored Procedure from Qlikview not working

Peter_Cammaert — Mon, 10 Apr 2017 07:51:42 GMT

Ok, that's a good start. But in the end there is just one real test: does it work in QlikView. As a next step, please try the INSERT statement in QlikView script, just to make sure that QV will be able to do perform a direct insert, and that the account you are using to make a connection is allowed to perform DB updates. If that works, then you'll only need to find out why executing Stored Procedures doesn't work, because everything else will be ok.

Re: Executing Oracle Stored Procedure from Qlikview not working

Mon, 10 Apr 2017 08:39:05 GMT

i aready test insert Statement in Qliview Script, work success.

"SQL INSERT INTO TES_INVOICES_STAGING VALUES(3,1000);"