Execute a Stored Procedure in Qlikview

[sureshbaabu]

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

[agomes1971]

Hi,

have you saw this thread:

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

Regards

André Gomes

[sureshbaabu]

Thanks for the response.

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

[sureshbaabu]

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

[Anonymous]

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

[offjunior]

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!

[sureshbaabu]

Thank you!! I will try the same.

[sunny_talwar]

Thank you for this. Very helpful

[qlikviewwizard]

Thank you for this. Good info and useful.

[#10]

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 oracl...

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

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary