Creating Stored Procedures using InetSoft's BI Software

Stored procedures are compiled and stored in a database. There are a few major differences between a stored procedure and a SQL query:

• A stored procedure is invoked as a function call instead of a SQL query.
• Stored procedures can have parameters for both passing values into the procedure and returning values from the call.
• Results can be returned as a result set, or as an OUT parameter cursor.

Stored procedures are listed on the same tree as tables and views. Only one stored procedure may be selected per query. The stored procedure parameters are listed on the middle pane. Specify values for the parameters by selecting each parameter on the tree and entering a value or variable name.

If the result column list is not populated, select the ‘Column Info’ button to retrieve it. Select the ‘Preview’ button to preview the query. If any parameter is left as null or specified as ‘Prompt User’, when the query is executed a parameter dialog will pop up to prompt for the remaining parameters.

Due to the different ways database vendors implement stored procedures, not all stored procedures are supported by JDBC. If a stored procedure cannot be used in the Data Modeler, it could be executed by custom code, and setup as a Java object data source.

The following is a list of known restrictions on stored procedures:

• Only one dataset is allowed in a stored procedure.
• In Oracle, only one cursor is allowed and it has to be the last parameter in the stored procedure.
• In Oracle, only standard SQL types should be used in the parameters (user defined types are not allowed).
• In MySQL, older drivers do not return procedure meta-data.