Oracle Hint in the Loadscript

People who load data from an Oracle database into QlikView may sometimes want to use an Oracle hint in the SQL query code. An Oracle hint tells Oracle about how you would like it to plan and execute the query in order to get faster, more efficient execution.

The Oracle hint syntax is actually a comment embedded in the SQL query. For example, the hint to tell Oracle to use up to four parallel processors might look like this in the first line of the query:
SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

If you simply code a query like that in the loadscript you won’t get an error or a warning, the query will return data, but you won’t get the performance improvement you were hoping for either. That’s because QlikView doesn’t ordinarily pass comments through to the SQL processor. Oracle would never see your hint.

What is needed, is for you to tell QlikView not to strip out the comments before passing the query into the SQL processor. Code a line like this before your loadscript table definition:

Set StripComments = 0;

ORDER_TABLE:

SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

The StripComments variable is a system variable that controls QlikView behavior. Setting the variable to zero tells QlikView not to strip out the comments. It is a good practice to turn StripComments back on after the query with a line like this:
Set StripComments = 1;

One thing to look out for is any other comments in your code. If you have a regular loadscript comment //(a comment beginning with two slashes) mixed in with your SQL code then it will cause a SQL error when it gets passed in to the SQL processor.

★★★

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.

★ ★ ★

Using Oracle Configured for Different Countries

We load much of the data in our QlikView documents from Oracle databases. A common query technique uses the Oracle Next_Day function to aggregate data into weekly quantities. For example,

Select Next_Day(INC_DATE,2)-7, Count(distinct INCIDENT_NUM) From INC_LOG group by NextDay(INC_DATE,2)-7;

will gather a count of security incidents by week. The Next_Day function in the example should return a Monday date. But, sometimes, when the Oracle database is configured for a European business then the way Oracle numbers days of the week will be different from a database in the U.S. A European database may give Tuesday dates for that example query.

In order to make the SQL commands in a loadscript behave like they do in the U.S. I add a single SQL line to the loadscript following the ODBC Connect string that modifies the Oracle NLS setting temporarily while data is loaded into the document. The line I add after the ODBC connect string is:

SQL ALTER SESSION SET NLS_TERRITORY='America';

Does anyone have a different way of dealing with this issue?

* * *