Call DB Procedure/Function
Description
Call DB Procedure/Function executes a stored procedure or function in a database and retrieves the results as new fields in the data stream. Use this step when your workflow needs to call server-side database logic — for example, running a calculation procedure, executing a data validation function, or invoking a batch process that returns status information. Input parameters can come from the workflow stream, and output parameters are added as fields for downstream processing.
Configurations
| Field Name | Description |
|---|---|
| Step name | Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow. |
| Local Connection | Specify the name of the database connection on which the procedure resides. |
| Type | Select type of call you want to make, that is, either procedure or function. |
| Procedure name | Specify the name of the procedure or function to call. |
| Find it | Click to search available procedures and functions (Oracle and SQL Server only; With MySQL and DBC, it is not possible to retrieve the result set of a stored procedure.) |
| Enable auto commit | In some instances, you want to perform updates. Commit in the database using the specified procedure. You can either have the changes performed using auto-commit or not. If auto-commit is disabled, a single commit is performed after the last row is received by this step. |
| Abort connection on stop? | Select the checkbox to immediately drop the database connection when stopping a workflow. Use this setting for heavy SQL queries to bypass the waiting for the active query to complete. If cleared (default), the system sends a standard cancel command and waits for the active query to finish gracefully. |
| Result name | Specify a field to store the result of the function call. Note: The field is unavailable if you selected Procedure in the Type field. |
| Result type | Specify a field to store the Type of the result of the function call. Note: The field is unavailable if you selected Procedure in the Type field. |
| Parameters | Stored procedures and functions can only return values through their function arguments and those arguments must be defined in the Parameters section of the DB Procedure Call configuration. Provide a list of parameters that the procedure or function needs in this section - Parameter name: Name of the field. - Direction: Can be either IN (input only), OUT (output only), INOUT (value is changed on the database). - Type: Used for output parameters so that Process Studio knows what data type returns. |
| Get Fields | Fills in all the fields in the input streams to make your life easier; delete the lines you don't need and re-order the remaining lines |