KB14959: How to use stored procedures with Freeform SQL reports in Oracle or DB2 data warehouses in MicroStrategy SQL Generation Engine

In some cases, it may be desired to invoke a stored procedure in the data warehouse, and retrieve the results for display in the context of a Strategy Freeform SQL report. Successful use of this technique depends on proper cursor handling in the stored procedure itself.
In general, the stored procedures need to be created so that they open cursors on desired result sets. Then the procedure should return without closing the cursor. This specifies that the procedure should return results to the caller. Thus there is no need to use report pre-SQL or a separate SELECT statement.
The specifics of enabling this behavior vary for different database platforms. This technical note outlines the procedure that should be followed for Oracle and DB2 warehouses. Similar procedures may work for other database platforms, but have not been tested by Strategy outside of Oracle and DB2.
 
Oracle

1. Create a new TYPE in Oracle that represents a cursor variable, as shown below:
   CREATE OR REPLACE PACKAGE types AS
     TYPE cursorType IS REF CURSOR;
   END;
   
2. The definition of the stored procedure should have an output parameter of the newly defined type (cResults in the example below):
   CREATE OR REPLACE PROCEDURE emp_search
     (EmpID INT, cResults IN OUT types.cursorType) AS
     BEGIN
       OPEN cResults FOR
         SELECT emp_first_name, emp_last_name
         FROM lu_employee
         WHERE emp_id = EmpID;
     END;
   
3. The Strategy ODBC Driver for Oracle Wire Protocol must be configured to accept results from stored procedures.
   
• In Windows environments, open the Data Sources (ODBC) control panel and configure the warehouse DSN. On the Advanced tab, check the option called 'Procedure Returns Results.'
• In Strategy Intelligence Server Universal  (UNIX/Linux) environments, the following line must be added manually to the Data Source Name (DSN) definition in the ODBC.INI file, located in the Strategy home directory specified at installation time:
  ProcedureRetResults=1
  
4. When defining the report, the Freeform SQL statement must use the CALL command enclosed in curly braces when using the Strategy driver. Oracle native drivers may require slightly different syntax. Prompts can be used to pass in parameters, e.g.
   {call emp_search()}
   where is a numeric value prompt. If no prompt is needed, it may be omitted.
   

DB2

1. Declare cursors with the 'WITH RETURN' option when creating a stored procedure. Define multiple cursors when it is necessary to return multiple result sets and remember to open each cursor whose results are needed.
   CREATE PROCEDURE emp_search(IN EmpID INTEGER)
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN
     DECLARE c1 CURSOR WITH RETURN FOR
       SELECT emp_first_name, emp_last_name
       FROM lu_employee
       WHERE emp_id = EmpID;
   OPEN c1;
   END @
   The cursor c1 is left open intentionally to fetch the result set later. The maximum number of result sets that can be returned is determined by DYNAMIC RESULT SETS option.
    
   
2. Follow the instructions under step four for Oracle to create the Freeform SQL report to pass prompt answers to the stored procedure.

For further information on ODBC DSN configuration, consult the Strategy Installation and Configuration Guide (InstallationConfig.pdf) concerning the respective databases and ODBC drivers.
 
Prompting
 
The example in this technical note uses a value prompt, which may pass a single value into the stored procedure. As noted above, the prompt object should be inserted into the Freeform SQL statement in the place where the procedure argument is required.
In cases where multiple values need to be passed from the same prompt, an element list prompt should be used. Strategy value prompts do not allow multiple values. To use an element list prompt with Freeform SQL, the following conditions must be met:

• The prompt must be based on an attribute in the project's primary schema (a Freeform SQL or Query Builder attribute may not be used).
• The attribute's lookup table must exist in the primary warehouse database instance.
• The attribute element IDs in this lookup table must match the values that are expected in the data source specified for the Freeform SQL report.

Third Party Product Disclaimer
WARNING:
The third-party product(s) discussed in this article is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
Third party code/SQL:
Strategy Technical Support does not provide customized SQL queries. The syntax and the error messages shown in this document can vary depending on the database. Please contact your database administrator to obtain the correct syntax for any desired customized SQL using an ODBC driver.