Editing a Stored Procedure in an Introspected Data Source
During introspection, the direction of the scalar parameters and cursors in stored procedures might be unidentified. A stored procedure with an unidentified scalar parameter or cursor direction returns an error when run as-is. In such cases you need to specify parameter and cursor direction manually.
This section uses an example to show how to specify scalar parameter or cursor direction, and define a cursor signature. The stored procedure discussed here has two scalar parameters (one input and one output) and it returns a single cursor that has seven columns.
To edit a stored procedure in an introspected data source, see:
Specifying the Direction of Scalar Parameters and Cursors
If introspection results include a parameterized query with scalar parameters whose direction is undefined, follow these steps to define the direction of those scalar parameters.
To specify the direction of a scalar parameter or cursor
1. Double-click the stored procedure to open it, or right-click it and select Open.
The editor opens on the right.
If the direction of an introspected parameter or cursor is unknown—whether it is IN, INOUT, or OUT—the icon next to it looks broken.
If you create a copy of a stored procedure in Studio, Studio automatically introspects the procedure (that is, gathers metadata for it) and assigns the default I/O direction of IN to any parameter or cursor whose direction is unknown.
2. Select the Design Mode check box in the editor’s toolbar so you can start editing the parameters.
3. Select the parameter whose direction is unknown, and use the direction arrows buttons on the toolbar to specify the direction.
4. Save the stored procedure.
Defining a Cursor for Projection
If introspection results include a parameterized query with one or more cursors whose direction is undefined, follow these steps (using the sample cursor and its parameters as a guide) to manually define a cursor for projection.
To define a cursor for projection
1. Double-click the stored procedure to open it, or right-click it and select Open.
The editor opens on the right.
2. Select the Design Mode check box in the editor’s toolbar so you can start editing the parameters.
3. Click the Add button, and select Complex > CURSOR.
A new cursorParam of type CURSOR is added.
4. Select the cursorParam and click Cycle I/O Direction one or more times to change the direction to OUT.
The other two valid direction icons are IN and INOUT.
5. With cursorParam still selected, click Add and select Time > TIMESTAMP.
A new parameter named timestampColumn is automatically placed under cursorParam.
6. Rename the newly added parameter date and click Enter.
7. Add more child parameter names and types to cursorParam one at a time, in the following order, and specify each parameter’s type as noted.
ord_num (type NUMERIC)
qty (type SMALLINT)
title_id (type CHAR)
discount (type DOUBLE)
price (type DECIMAL)
total (type DOUBLE)
Your selection of the cursor columns is complete.
If you are unsure what columns and data types to use for the cursor, use Studio’s
Design By Example tool to select the parameters. See
Designing a Cursor by Example. When you use
Design By Example, all previously defined parameters for the cursor are overwritten.
8. Select the parameter named RETURN_VALUE and click the Delete button, or select Delete from the right-click menu.
9. Save the stored procedure.
For details on execution, see
Executing a Stored Procedure.
Designing a Cursor by Example
When you use Design By Example for a cursor, all previously defined parameters for the cursor are overwritten.
To design a cursor by example
2. Save the stored procedure after specifying the direction for any scalar parameter whose direction is unknown.
3. Click Design By Example on the procedure editor toolbar.
The Input Values for <resource> window opens.
4. Supply a value for the scalar input parameter and click OK.
The Design By Example window opens to display all the columns in the cursor, which is named result.
5. Click OK to accept the parameters.
All the cursor columns are included for projection in the stored procedure.
You can rename any of the parameters or columns, and also change their data types, as long as the data types are compatible with the ones in the underlying physical stored procedure.
6. Save the stored procedure.
For details on executing stored procedures, see
Executing a Stored Procedure.