Oracle Stored Procedure - Output Parameters

Situation:-
Calling Oracle stored procedure within the data mapper to handle 2 return parameters.

Solution:-
If the database is Oracle then you need to use custom class Attached(MultipleParamsSP.class) that calls the stored procedure. Firstly, add this class in customClasses folder in the Adeptia install directory. After the class file is placed in the folder restart the Adeptia Services. Now if you open the Mapper this Class method will be shown under the Global Method category in lower right. Currently, this class handles stored procedure with 1 IN parameters(Data Type is Varchar) and 2 OUT parameters(Data Type is Integer).

The Method execute() in the class takes five input parameter as:
1. Identifier, please keep it same as $_identifier.
2. OracleDBInfoName, this will contain the Database Info Name for Oracle Data base.
3. Subject, this will be same as $_subject.
4. Query String, that query will be in this format:
'{call StoredProcedureName(?,?,?)}'
5. Input Parameter stored in the variable.

We can get the desired result from the stored procedure by using the below steps:-


1. Create a variable such as"varquery" and provide the input like screenshot below.

'{call StoredProcedure_Name(?,?,?)}'

 

query.jpg


2. Create a variable for the input parameter Eg:-"varinput" 

varinput.jpg


3. Create a variable for defining the rule which should be used to fetch the data from the procedure and in this query use the name of Activity "DB_INFO" from Services > Connector > Database Info. Eg:- "varRule"

java:MultipleParamsSP.execute($_identifier,'DB_INFO',$_subject ,$varquery,$varinput)


4. Use the sub-string-before and sub-string-after function from the mapping to separate the fetched data into the variable.

Storedata.jpg

 

Storedata2.jpg

Have more questions? Submit a request

0 Comments

Article is closed for comments.