topic Re: Execute a Stored Procedure in Qlikview in QlikView

Execute a Stored Procedure in Qlikview

sureshbaabu — Mon, 12 Jan 2015 11:36:01 GMT

Hello,

I'm trying to execute a stored procedure thro Qlikview, but it errors out with the following error:

Error message:

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

Qlikview script :

ODBC CONNECT TO [DB]:

SQL EXEC Schemaname.Procname;

I have been trying all possible suggestions posted on our community, but I'm unable to fix the issue.

Used "Execute" in place of "Exec" on the script above
Enabled & Disabled the option "Open Database in Read and Write Mode" on the Settings tab of the script editor
Enabled - Settings->user preferences->security->Script(allow database write and execute statements)
load *; sql Execute Procname; exit Script;
load *; sql Execute Procname(); exit Script;

Note:

1) The stored procedure runs perfectly in oracle.

2) The Procedure will just insert an entry to a table. I have granted permission to execute this PROC on different schema.

Procedure in DB:

create or replace procedure Procname as

begin

insert into dummy values (1,’abc’);

commit;

end;

If you could help me with any suggestions, that will be a great help!!

Thanks

Re: Execute a Stored Procedure in Qlikview

agomes1971 — Mon, 12 Jan 2015 11:40:30 GMT

Hi,

have you saw this thread:

How to trigger PL/SQl Stored Procedures from Qlikview Application???

Regards

André Gomes

Re: Execute a Stored Procedure in Qlikview

sureshbaabu — Mon, 12 Jan 2015 11:44:16 GMT

Thanks for the response.

Yes, I did!! I actually checked for some solutions on that thread, before posting a new discussion.

Re: Execute a Stored Procedure in Qlikview

sureshbaabu — Wed, 14 Jan 2015 13:18:08 GMT

Hello,

I have tried using ‘Call’ function and it works fine when the procedure doesn't have any action on the DB table.

But it error’s out with the following error, when trying to write into DB.

SQL##f - SqlState: S1000, ErrorCode: 1456, ErrorMsg: [Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction

ORA-06512: at "Schemaname.Procname", line 18

Qlikview script:

sql call procname (‘QV Test')

Note: using Exec and execute did not work, it error-ed out for ‘Syntax error or access violation’

Any thoughts ?

Thanks

Re: Execute a Stored Procedure in Qlikview

Anonymous — Fri, 27 Feb 2015 05:06:45 GMT

Hi Suresh,
I observe that your procedure is inserting records, so you should open ODBC connection in write mode and enable "Open Database in read and write mode" from settings.
Ex.: ODBC CONNECT TO [DB](Mode is write);

Re: Execute a Stored Procedure in Qlikview

offjunior — Thu, 23 Apr 2015 16:46:12 GMT

Hello Friend!

About calling procedures from within QlikView, if you have not achieved, you can try the way that I will describe below.

Step 1:

Set your script editor and it will open the databases in read and write mode.

Step 2:

Correctly write your connection string, it is ODBC or OLEDB, add, before or after the password the following content

"Mode is write"

Step 3:

Write correctly the call of Procedure that you want to call, in my example will look like this ...

"SQL call user_bi.prc_custo_padrão_real('4.2014','007');"

I think this should work, I have helped.

Hug!

Re: Execute a Stored Procedure in Qlikview

sureshbaabu — Fri, 24 Apr 2015 06:24:57 GMT

Thank you!! I will try the same.

Re: Execute a Stored Procedure in Qlikview

sunny_talwar — Wed, 22 Jul 2015 12:31:11 GMT

Thank you for this. Very helpful

Re: Execute a Stored Procedure in Qlikview

qlikviewwizard — Sat, 01 Aug 2015 15:59:16 GMT

Thank you for this. Good info and useful.

Re: Execute a Stored Procedure in Qlikview

Thu, 02 Jun 2016 14:06:49 GMT

Hello Suresh,

Thank you for the discussion!!.

I am facing issues the same issue in integrating oracle stored procedure data with QlikView.

I have tried all the above changes.

But I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracle table

Could you please take a look at and advise for corrections.

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary