Execute Oracle Procedure From The Loadscript

I worked on a project a few months ago where several Oracle database tables had been designed specifically to support a QlikView document. The tables contained summarized data extracted from a large database. Often, database software, running on a large server can process large amounts of data more efficiently than QlikView can do it within the loadscript. In this particular case, the solution would only work correctly if the tables were updated shortly before loading the QlikView document. I decided to try something different and execute an Oracle stored procedure from a loadscript command. The procedure would update the tables and then the remainder of the loadscript could load data from the tables with SQL select statements.

The syntax for the procedure execution took a little trial and error. I put the execution immediately after the ODBC connect statement. This procedure takes a parameter which is supplied from a document variable. The procedure, named update_rpt_data, is implemented within an Oracle package named corp_report. The loadscript statement looks like this:

// Call Oracle data summarization procedure
sql call corp_report.update_rpt_data('$(var_COUNTRY)');

In order to make this work correctly with Oracle, I had to add a COMMIT statement as the first executable line in the stored procedure. Without the commit, Oracle threw an error.

If you use this technique, remember that the procedure execution may take extra seconds or minutes and this will add to the time required for the loadscript. Other types of database software may require different syntax; this example applies only to Oracle. Note that the statement only executes the procedure – no data is being returned directly from the procedure.

★ ★ ★