topic Re: Stored Procedure in Connectivity & Data Prep

Stored Procedure

manoranjan_d — Thu, 26 Sep 2024 18:20:03 GMT

Hi All,

We have created simple stored procedure in oracle DB and same has been called in the below qliksense script

LIB CONNECT TO 'connection_name';

sql exec
TEST_POC_PROC;

but I am getting an error , pls let me know how we can fix this issue.

Started loading data
Connected
{0}
The following error occurred:
ERROR [HY000] [Qlik][OracleOCI] (3000) Oracle Caller Interface: ORA-00900: invalid SQL statement

Re: Stored Procedure

marksouzacosta — Thu, 26 Sep 2024 21:30:31 GMT

Hi @manoranjan_d,

Try this link: Solved: How to Execute Oracle Stored Procedure with multip... - Qlik Community - 1889943

Regards,

Mark Costa

Re: Stored Procedure

manoranjan_d — Fri, 27 Sep 2024 11:50:02 GMT

Hi,

its not working out, actually the stored are developed for angular API which pass input and o/p parameter and also they are not normal stored proc its have cursor concept in that. the same store proc has be executed in qlik

Re: Stored Procedure

p_verkooijen — Fri, 27 Sep 2024 12:08:28 GMT

@manoranjan_d

Use CALL

SQL EXEC = MS SQL
SQL CALL = Oracle

https://community.qlik.com/t5/Official-Support-Articles/Calling-a-stored-procedure-or-function-in-Oracle-from-QlikView/ta-p/1710260

Re: Stored Procedure

Manoranjan_D_cognizant — Mon, 30 Sep 2024 11:18:22 GMT

how to connect the external web application with parameter thru api?

Re: Stored Procedure

nhoover — Mon, 30 Sep 2024 12:32:59 GMT

Try calling your SP like this:
SQL EXEC TEST_POC_PROC !EXECUTE_NON_SELECT_QUERY;

Re: Stored Procedure

Manoranjan_D_cognizant — Mon, 30 Sep 2024 12:41:59 GMT

Connector reply error: Unknown substring, string, or value at (2,1): '!EXECUTE_NON_SELECT_QUERY'

getting error like this now we have decieded to go with api connection bcoz all these proc are used cursor for web application , so getting data from web application thru rest api connector and passing the parameter any help on this