topic Re: How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense in App Development

How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense

Siqk — Mon, 07 Feb 2022 20:20:20 GMT

I am trying to Execute Oracle Stored Procedure from QlikSense passing a parameter "Y"

I Set the parameter allow-nonselect-queries to True

Tried below syntax to execute the SP sending a parameter "Y" -

SQL exec ('[DBNAME].[STOREDPROCEDURENAME]');

SQL EXEC DBNAME.STOREDPROCEDURENAME '$(Y)'
SQL EXEC DBSERVERNAME.DBNAME.STOREDPROCEDURENAME'$(Y)';

none of them are working and throwing below Oracle error -

ERROR [HY000] [Qlik][OracleOCI] (3000) Oracle Caller Interface: ORA-00900: invalid SQL statement.

What's the correct syntax for executing Stored Procedures with multiple parameters in QlikSense?

Procedure execution is allowed at SQL*PLUS as well.

Appreciate any helpful pointers. Thanks in advance.

Re: How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense

Digvijay_Singh — Mon, 07 Feb 2022 23:46:38 GMT

Not sure but this thread might be useful -

https://community.qlik.com/t5/QlikView-App-Dev/QlikView-integration-with-Oracle-Stored-procedure-FAILS-in/td-p/1098446

Re: How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense

Siqk — Tue, 08 Feb 2022 14:31:28 GMT

@Digvijay_Singh Thank you so much . This definitely helped .

I still had to tweak a little . Here is the statement that worked for me -

SQL CALL DBNAME.STOREDPROCEDURENAME('parameter') !EXECUTE_NON_SELECT_QUERY;

Re: How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense

Digvijay_Singh — Tue, 08 Feb 2022 15:08:17 GMT

Glad to hear that you found the fix for your problem. 🙂

Re: How to Execute Oracle Stored Procedure with multiple Parameters in QlikSense

shampy19 — Mon, 20 Mar 2023 05:19:20 GMT

Hi @Digvijay_Singh

I tried the same command but still it throws me an error that SQL command is not ended properly.Please find me code below as i have 3 input and 3 output parameters.

Let v_inpput_param1='SSG1';
Let v_inpput_param2='28 FEB 2023';
Let v_inpput_param3='N';
Let v_output_param1='@p_ResultSet OUTPUT';
Let v_output_param2='@p_RetCode OUTPUT';
Let v_output_param3='p_RetString OUTPUT';

SQL CALL PK_JAZZ_FOBOREC.Get_CashRec('$(v_inpput_param1)','$(v_inpput_param2)','$(v_output_param1)','$(v_output_param2)','$(v_output_param3)','$(v_inpput_param3)')!EXECUTE_NON_SELECT_QUERY;