Call a DB PROCEDURE or FUNCTION from ADF

Last updated: 10/12/2010

I was a reading Baig's blog post http://baigsorcl.blogspot.com/2010/11/how-to-execute-sql-dml-statements-in.html and I thought that the following might be interesting.


The best way for me to call DB PROCEDURE or FUNCTION from ADF is
DbCall created by Sasha Stojanovic (more about Sasha on his blog). I' ve been using it for the last year and it is solid.


Code for DbCall
I ve created a jar file to easy include it in your project .
(I just created the jar and nothing else. DbCall is totally attributed to Sasha :) )


I am justing reposting some usage samples:


And few examples of "DbCall" in action: 

Very simple procedure call with two IN parameters: 

DbCall dc = new DbCall("MYPACKAGE.MYPROC", this.getDBTransaction());

//add some carefully created string
dc.addIn("ABC");

//add some integers too, as they are great partners to strings
dc.addIn(123);

//Call it here... Done!!!
dc.execute(); 

Example of Procedure Call with various IN/OUT parameters: 

DbCall dc = new DbCall("MYPACKAGE.MYPROC", this.getDBTransaction());

//add the current user
dc.addIn(this.getUserPrincipalName());

//give a bit of mmh to you...
dc.addIn(this.getMMH());

//everybody needs some time
dc.addIn(new Timestamp(new java.util.Date().getTime()));

//here you cannot go wrong!
dc.addIn(null);

//register this as OUT param as I need it later. dc.addOut("IS_SUMMER",Types.CHAR);

//I need this info too.
dc.addOut("HOW_HOT", Types.FLOAT);

//call it here!!!
dc.execute();

//and here is the summer!
Object Summer = dc.getObj("IS_SUMMER");

//here is how hot it is!
Object Hot = dc.getObj("HOW_HOT"); 

Example of Function Call with various IN/OUT parameters: 

DbCall dc = new DbCall("?:=MYPACKAGE.MYFUNC", this.getDBTransaction());

//the function will return us something, here it goes
dc.addRet("ret", Types.VARCHAR);

//simple simple string as IN param
dc.addIn("ABC");

//some OUT param, as I need to know this.
dc.addOut("RET", Types.FLOAT);

//here is very cool IN/OUT param
dc.addInOut(123,"WHAT",Types.NUMERIC);

dc.execute();
Object ret = dc.getObj("RET");
Object what = dc.getObj("WHAT");