Call PL/SQL Procedure or Function

Sometimes, migrating business logic code to database is a better strategic choice as we can handle our code in a centralized way. Also we never know what will happen in the next decade: Technology is moving really fast: JEE frameworks are really hot today but  who knows about tomorrow ! So storing business logic in database will save us a lot of major re-writing of the same code again and again.
In this article, we will explain how to call PL/SQL Procedure or Function inside ADF application modules. We will use a helper class written by the Greek developer Sasha Stojanovic.  this class is named DbCall. It significantly reduces the strain of creating complicated statements. Still, it is very light on code, and easy to expand for any customization you might need.
Some of its advantages are:

• DbCall can be used equally for function and procedure calls.
• No need to specify IN parameters' types.
• Offers a convenient  way of OUT  parameters' handling by using java.sql.Types
• No need to build complex BEGIN/END PL/SQL string with question-marks.
• Exceptions are handled internally, but also can be thrown to be caught externally.

Here are some hands-on examples, they should be used inside your application module java implementation.

Procedure call with IN parameters

DbCall dc = new DbCall("MYPACKAGE.MYPROC", this.getDBTransaction());
//add a string IN parameter
dc.addIn("hello");
//add another integer IN parameter
dc.addIn(123);
//execute the procedure
dc.execute(); 

Procedure Call with various IN/OUT parameters

DbCall dc = new DbCall("MYPACKAGE.MYPROC", this.getDBTransaction());
//add a string IN parameter
dc.addIn("hello");
//add a Timestamp IN parameter
dc.addIn(new Timestamp(new java.util.Date().getTime()));
//register "IS_LATE" as an OUT param as we will need it later.
dc.addOut("IS_LATE",Types.CHAR);
//register  "SOME_FLOAT" as an other OUT param, Types stands for java.sql.Types
dc.addOut("SOME_FLOAT", Types.FLOAT);
//execute the procedure
dc.execute();
//and then, we can retrieve "IS_LATE" param value
Object isLate= dc.getObj(" IS_LATE ");
//and "SOME_FLOAT" param value
Object Hot = dc.getObj(" SOME_FLOAT "); 

Function Call with various IN/OUT parameters

DbCall dc = new DbCall("?:=MYPACKAGE.MYFUNC", this.getDBTransaction());
//define a return parameter for the function
dc.addRet("RETURN", Types.VARCHAR);
//add a string IN parameter
dc.addIn("hello");
//add a Out parameter
dc.addOut("SOME_FLOAT", Types.FLOAT);
// add  an  IN/OUT param
dc.addInOut(123,"MY_IN_OUT_PARAM",Types.NUMERIC);
//execute the function
dc.execute();
//retrieve OUT parameters values
Object floatParam= dc.getObj("SOME_FLOAT");
Object intParam= dc.getObj("MY_IN_OUT_PARAM");
//getting the function's return value
Object returnValue= dc.getObj("RETURN"); 

DbCall class is released under the GNU GPL licence. Its source code can be downloaded here.