Using Stored Procedures

Stored procedures are precompiled, complex queries that are executed on a database server and maintained by a database administrator. Stored procedures execute very quickly and are usually created to accomplish tasks that SQL cannot do alone. Interactive Reporting treats stored procedures as locked standard queries and does not allow you to modify the procedures.

You can use Interactive Reporting to process stored procedures through Open Client or ODBC, collect the results, and generate reports as you would with a standard query. Stored procedures can be loaded from your desktop and appear as a query object in the Content pane

Note:

ODBC only. Interactive Reporting supports stored procedures that return results. This support is contingent on the driver and database. The driver and database must support the required ODBC calls, including SQL Procedures to retrieve a list of available procedures and SQLProcedureColumns which identify the parameters required to execute the procedure. For Oracle, results are recognized in ODBC by specifying reference cursor parameters when the procedure is created. The Interactive Reporting database connection file must specify the database as “ODBC” rather than “Oracle” to work properly.

Note:

The ODBC driver must recognize the ODBC syntax for calling procedures: {call <procedure name> (parameter list)}. If the procedure has no parameters, the parentheses surrounding the parameter list are optional. Interactive Reporting does not insert empty parentheses in the call to execute the procedure. In addition, the driver must accept literal values for any specified parameter. Drivers that require parameter markers, for which values are provided when the procedure is executed, are not currently supported.

  To open a stored procedure:

  1. Select Query, then Stored Procedures.

    The Stored Procedures dialog box appears.

  2. Select the database owner name that contains the stored procedure.

    Any stored procedure to which you have been granted access is displayed in the Stored Procedures list.

  3. Select a stored procedure from the list and click Load.

    The stored procedure appears as an icon in the Content pane. No items appear on the Request line until the stored procedure is processed.

  To process a stored procedure:

  1. Click Process.

    If the stored procedure calls for user input, a dialog box appears and prompts you with up to 10 entry fields. If more than 10 arguments are required, successive dialog boxes appear.

  2. If an argument dialog box is displayed, enter appropriate values as arguments to the stored procedure.

    The arguments supplied are similar to variable filters. If necessary, see your database administrator for clarification on the arguments needed to process a particular stored procedure.

  3. If the stored procedure queries the database, the database server returns data to the Results section and the adds items to the Request line.