How to call Oracle Stored Procedure from Mapper

Question: Is it possible to call an Oracle stored procedure within the mapping?

I would like to call it as part of the query function, is that possible?
We have a stored procedure that reads in one value and returns another value.

>> > In SQL I would call it like this:
>> >
>> > Select DB.StoredProcedure('parameter1', $my_variable,
>> > to_date('2005-07-01', 'YYYY-MM-DD')) from DUAL
>> >
>> > Where to_date is another Oracle function I would like to incorporate


Answer: We have created a class (ExecuteOracleProcedureForUpdate.class) that calls
the Oracle stored procedure. First, add this in customClasses folder in the
Adeptia install directory. Stop and restart the Adeptia kernels after the
class is placed in the folder. Now if you open the Mapper this Class method
will be shown under the Global Method category in lower right (see attached
pdf). Currently, this class handles stored procedure with IN parameters.

The Method execute() in the class takes four input parameter as:
1. Identifier, please keep it same as $_identifier.
2. OraDBInfoName, 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(parameter1, parameter1, parameter3…. and so on)}

1. Create a local variable with name toDate and Value concat('to_date(' ,$apos,'2005-07-01',$apos,',',$apos,'YYYY-MM-DD',$apos,')' ) This will create the String text using to_date function.
2. Create a local variable named my_variable
3. Create a local variable as quer with value:
concat('{call StoredProcedure (' ,$apos,'parameter1',$apos,',',$my_variable,',', $toDate,')}')
4. Finally, double click on the execute method and provide the input parameters as
java:ExecuteOracleProcedureForUpdate.execute($_identifier ,'OraDBInfo' ,$_subject , $quer )

Note: OraDBInfo is the name of the Database Info object. Class is attached below this post.


Have more questions? Submit a request


Article is closed for comments.