Databases Stored Procedures can be designed in a specific Metadata and invoked in Mappings as a common datastore, allowing to send input parameters data and retrieving output results.
Prerequisites:
- templates.generic
- templates.stored.procedure.2019-04-19 or higher
- Stambia DI Runtime S17.3.0 or higher
Stored Procedure Metadata
Metadata creation
The first step is to create the Stored Procedure Metadata.
For this, simply create a new Metadata as you would usually do for any other technology:
Metadata configuration
Metadata being created, you can now start defining your stored procedures.
This consists of defining first the database schema which will be used to retrieve a connection and execute the procedures defined in this Metadata.
And then define procedures, with their input parameters, output parameters, and output set.
Definition of corresponding database schema
First thing to do is to drag and drop a database schema on "Schema" attribute of root node:
Procedures will be executed with a connection retrieved from this database and schema.
Definition of a stored procedure
Definition of a stored procedure begins with the creation of a new dedicated node in Metadata.
Right click on root node and choose "New > Stored Procedure".
Following attributes are available on this node:
| Attribute | Description |
| Name | Stored procedure name, which must correspond to the exact name of an existing stored procedure. |
| Description | Optional description to add comments about the procedure. |
Definition of input and output parameters
Next step consists of defining the input and output parameters of the procedure.
To add input parameters, right click on procedure node and choose "New > Input Parameter".
To add output parameters, right click on procedure node and choose "New > Output Parameter"
Following attributes are available on those nodes:
| Attribute | Description |
| Name | Input / Output parameter name, which must correspond to the exact name of the parameter as defined in the procedure. |
| Type | Parameter's datatype. |
Definition of output set
When a procedure is returning a set of data, output must be defined through a dedicated node.
Right click on procedure node and choose "New > Output Set".
Then right click on created output set node and choose "New > Output Row".
The "Name" attribute which can be specified on output set node is only a label, which can optionally be defined for usability purposes. You can leave it empty.
You can now define the columns which will be returned by the procedure output set:
Following attributes are available on those nodes:
| Attribute | Description |
| Name | Column name, which must correspond to the exact name of the column which will be returned by the procedure. |
| Type | Column's datatype. |
Example:
Stored Procedures in Mapping
When your procedures are defined in Metadata, you can finally use them in Mappings, as usual datastores.
Drag and drop the procedure you want to call, a source which will be used to invoke it, and optionally a target for retrieving results.
Note that root node must be mapped from a source for calling the procedure.
This is mandatory and will define how many times the procedure will be invoked, this root node being the repetition key.
Example with input and output parameters
Example with a procedure returning an output set
Demonstration Project
A demonstration project can be found here: download
Do not hesitate to have a look at this project to find samples and examples.
Miscellaneous and limitations
Miscellaneous
When working with stored procedure on Oracle database, if you want to call a procedure which is inside an Oracle Package, simply prefix the procedure name with the package name in the Stored Procedure Metadata.
For instance: <package_name>.<procedure_name>
Limitations
You can find below actual limitations about using stored procedure.
We're working on new versions which will unlock them on the future.
Do not hesitate to contact us if you have any issue or if any of those limitation is blocking you.
- Procedures with no input parameters are not supported, there must be a mapped input parameter in Mapping while invoking.
- Some datatypes might not work, please do not hesitate to inform us if you have issues with a specific datatype.
- You cannot map the root node of the stored procedure in a target datastore. Only input and output parameters can be mapped.