Calling a stored procedure or function in Oracle from QlikView

[Bjorn_Wedbratt]

In Oracle, you can call a stored function with a syntax  like:

var result number
exec :result := pkg_ims_status.ext_checkdbstatus()

The function will return a value stored in the variable "result".

You can call the same function in QlikView script using an ordinary SQL SELECT-statement.

Environment:

QlikView 

Resolution:

SQL SELECT-statement:

SQL SELECT
pkg_ims_status.ext_checkdbstatus() as status;

Running a stored procedure without returning any recordset

Use the function CALL to specify the name of the stored procedure.

Example (launches the stored procedure XYZ with two parameters 2 and 5):

sql call XYZ ( 2 , 5 ); 

Note1: Make sure to check  "Open Databases in Read and Write mode" in QlikView. This is done in the script editor on the tab "Settings".

Note2: If using OLEDB as a provider, make sure to add the parameter "mode is write" in the OLEDB-connection:
CONNECT TO [Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;](mode is write);