6.5 Setting up a Query using a Stored Procedure

It is possible to query a database using stored procedures rather than building a query using the steps described previously.

To define a query from a stored procedure:

  1. Select an element in the Definition Navigator pane.

  2. In the right pane, click the Query icon to open the Editor tab.

  3. Select the Custom radio button for the Query Type.

  4. Click the Source tab.

  5. In the Custom Query pane, specify the execute statement for the procedure invocation.

    Oracle stored procedures use “out” parameters to return values and must be handled in a special way. The execute statement must be:

    call procedure_name(?)

    where procedure_name is the name of the stored procedure. The question mark represents the out cursor parameter to use for Data Integrator.  If the procedure has multiple parameters, fill them in normally; just use a question mark for the out parameter.

  6. Click the Property Details tab.

  7. Create properties for procedure results columns.

    To map result columns to properties, specify the name of the result column for each property in the Column Name field.