Can run on: Windows, Linux
Call DB Procedure/Function
Description
Use the step to run a stored procedure or a stored function that already exists in the database, and to bring the result back into the workflow.
A workflow often needs a value that only the database can work out, such as the discount for a customer, the tax on an order, or the next invoice number. A stored procedure or function in the database already holds the calculation. The step calls the routine, sends the values from the row into the routine, and adds the answer back to the row. The calculation stays in one place, so a change made in the database applies to every workflow that calls the routine.
How the step behaves:
- The step calls the routine once for every row that arrives.
- Values from the row go into the routine as parameters.
- An OUT parameter adds a new field to the row. An INOUT parameter writes the value back into the field it came from, and adds no new field.
The step makes one database call per row. For very large volumes, compare the step against a set-based option, such as a single query that handles all rows at once.
Fields added to the row
Rows pass through the step unchanged, apart from the following new fields:
- The field named in Result name, when Type is Function.
- One field for every parameter with direction OUT. An INOUT parameter updates the field it came from instead of adding one.
Prerequisites
- A database connection exists in the current workflow or process. A new connection can also be created from the step dialog with New… or Wizard….
- The database account used by the connection has EXECUTE permission on the procedure or function.
- The routine name matches the name in the database. Some databases also need the schema name.
Example 1: Procedure with IN and OUT parameters
A workflow processes new orders. The customer tier and the discount rate already sit in the database, inside a procedure that the finance team maintains. The step reads both values for each order.The Oracle procedure looks as follows:
SQL:
CREATE PROCEDURE SP_GET_CUSTOMER_TIER (
p_customer_id IN NUMBER,
p_tier OUT VARCHAR2,
p_discount OUT NUMBER
);
Configuration:
| Setting | Value |
|---|---|
| Type | Procedure |
| Procedure name | SP_GET_CUSTOMER_TIER |
| Parameters | customer_id / IN / Integer tier / OUT / String discount / OUT / Number |
Result:
A row that arrives with customer_id = 10482 and order_total = 1500 leaves the step as customer_id = 10482, order_total = 1500, tier = Gold, discount = 12.5.
Example 2: Function with a return value
The same workflow needs the tax amount for each order. A function in the database holds the tax rules for every region, so the workflow calls the function instead of storing the rates in the workflow.
The function looks as follows:
SQL:
CREATE FUNCTION FN_CALCULATE_TAX (
p_amount NUMBER,
p_region VARCHAR2
) RETURN NUMBER;
Configuration:
| Setting | Value |
|---|---|
| Type | Function |
| Function name | FN_CALCULATE_TAX |
| Result name | tax_amount |
| Result type | Number |
| Parameters | order_total / IN / Number region / IN / String |
Result:
A row that arrives with order_total = 1500 and region = CA leaves the step with one extra field, tax_amount = 116.25.
Configurations
| Field Name | Description |
|---|---|
| Step name | Specify a unique name for the step, which will help you identify and reference it easily when debugging or linking steps in the workflow. The field is mandatory. |
| Connection | Select the database connection that runs the procedure or function. The drop-down list shows the connections already defined in the current workflow or process. When only one connection exists, Process Studio selects that connection automatically. Selecting OK while the field is empty shows the message "Please select a valid connection!". The dialog then closes and the step is saved with no connection, so open the step again and pick one. The field is mandatory. |
| (Button) Edit… | Click to change the connection selected in Local Connection. |
| (Button) New… | Click to create a database connection without leaving the step dialog. |
| (Button) Wizard... | Click to create a database connection with the help of guided prompts. |
| Type | Select the database call: • Procedure: runs a set of operations in the database. • Function: runs a routine that returns one value. Note: The step never returns the rows of a routine, on any connection. It reads back only the OUT and INOUT parameters and, for a function, the return value. A routine that returns a cursor or many rows needs a different approach, such as a Table Input step that wraps the call. |
| Procedure name | Specify the name of the procedure to call. The name must match the name in the database. A wrong name stops the step from connecting to the procedure. The field accepts a static value or a variable and is mandatory. Note: Procedure name and Function name are the same field. The label changes with Type. Leaving it empty shows "Procedure name cannot be blank" and the dialog stays open. |
| OR | |
| Function name | Specify the name of the function to call. The name must match the name in the database. A wrong name stops the step from connecting to the function. The field accepts a static value or a variable and is mandatory. Note: Function name and Procedure name are the same field. The label changes with Type. Leaving it empty shows "Function name cannot be blank" and the dialog stays open. |
| (Button) Find it… | Click to see the procedures and functions available on the selected connection and pick one from the list. Process Studio connects to the database and shows the available routines. Notes: • Find it works with any connection whose driver can list procedures. When the driver returns nothing, the dialog shows "I couldn't find any procedures to select from." • For Microsoft SQL Server connections, the list shows procedures and functions separately, based on the selected Type. |
| Enable auto commit | Select the checkbox to save each change to the database as the change happens. Use the setting for routines such as a status update, where each change counts on its own. Clear the checkbox to save all changes together, after the last row is processed. The routine then has to handle its own commit and rollback. Use the setting for routines such as closing a month-end batch, where the whole call must be saved, or none of the call. Default: Selected |
| Abort connection on stop? | Select the checkbox to close the database connection at once when a user stops the workflow. Use the setting for long-running procedures and heavy queries, where waiting for the current call to finish takes too long. Clear the checkbox to send a normal cancel request and let the active query finish gracefully. Default: Cleared. |
| Result name | Specify the name of the new field that holds the value returned by the function. The step adds the new field to the outgoing row. A name that already exists in the row creates two fields with the same name, and later steps cannot tell the two fields apart. Note: The field is available if Type is selected as Function. The field is mandatory when Type is Function. Leaving it empty shows "Result name cannot be blank" and the dialog stays open. Choosing Procedure clears the field. |
| Result type | Select the data type of the value returned by the function: • String • Integer • Number • Date • Boolean • BigNumber • Binary • Timestamp • Internet Address A type that does not match the return type in the database causes a conversion error when the workflow runs. Default Value: Number. Note: The field is available if Type is selected as Function. |
| Parameters | A procedure sends values back only through parameters with direction OUT or INOUT. An OUT parameter only receives value from the database. An INOUT parameter sends a value to the database and receives a value back. A function sends one value back directly, and Result name holds that value. List every parameter that the routine expects in the Parameters section. Add one line per parameter and keep the lines in the same order as the parameters in the database routine. Process Studio matches parameters by position, not by name. A wrong order causes an error or a wrong result when the workflow runs. |
| Name | Select the field in the incoming row that supplies the value for the parameter. The drop-down list shows the fields that arrive from the previous step. For a parameter with direction OUT, the name entered here becomes the name of the new field added to the outgoing row. A name that already exists in the row creates two fields with the same name, and later steps cannot tell the two fields apart. For a parameter with direction IN or INOUT, the name must match a field arriving from the previous step. Otherwise the step fails with "Couldn't find field '<name>' in row!". |
| Direction | Select the direction of the parameter: • IN: The value only goes to the database. • OUT: The database sends a value back, and a new field with the given name is added to the outgoing row. • INOUT: The value goes to the database, and the value that comes back replaces the value in the same field. No new field is added. |
| Type | Select the data type of the parameter. Process Studio uses the data type to know what kind of value the database returns. A type that does not match the type in the database causes a conversion error when the workflow runs. The list is the same as Result type: String, Integer, Number, Date, Boolean, BigNumber, Binary, Timestamp, Internet Address. |
| Get Fields | Click to fill the Parameters grid with all the fields that arrive from the previous step. Each line starts with direction IN. Delete the lines that are not needed, change the directions, and reorder the remaining lines to match the database routine. |