Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…


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 new

 

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:

 

metadata add schema md link

 

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".

 

metadata 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".

 

metadata new input parameter

 

To add output parameters, right click on procedure node and choose "New > Output Parameter"

 

metadata 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".

 

metadata output set

 

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:

 

metadata output set output column

 

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:

metadata output set output columns 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.

mapping key must be mapped

 

Example with input and output parameters

mapping common input output

 

Example with a procedure returning an output set

 mapping common 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.

 

 

Articles

Suggest a new Article!