Run a DB Procedure with PL/SQL in the DB

For Java originated ADF developers

Sometimes we need to run a PROC in DB to check if it works there. Below is a way.

Let's Call a PROC with 1st 2 params are IN and the 3rd is IN/OUT

code

--enable DBMS_OUTPUT

SET SERVEROUTPUT ON

DECLARE

  --Declare vars

  MY_NUM_VAR NUMBER;

  MY_STRING_VAR VARCHAR2(77);

  MY_DATE_VAR DATE;

BEGIN

  --set vars

  MY_NUM_VAR :=7; NUMBER;

  MY_STRING_VAR:='My Lucky Number;

  MY_DATE_VAR DATE:=NULL;

  --call the PROC. 1st 2 params are IN and the 3rd is IN/OUT

  MY_SCHEAMA.MY_PACKAGE.MY_PROCEDURE ( MY_NUM_VAR, MY_STRING_VAR, MY_DATE_VAR);

  

  --Print the var

  DBMS_OUTPUT.PUT_LINE(MY_DATE_VAR);

  --In case you want to commit

  --COMMIT;

END;

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"); 

Execute DB function and get its value through an VO attribute

Last Updated on: 14/12/2010 

Create a Transient attr "Mapped to  Column or SQL"

In the Expression put the call passing the appropriate params

+: No need to write java code to call it

-: Execute it for every row

-: Can pass as params only the attr of the same table.

Alternatively, check related ADF How To  Call a DB PROCEDURE or FUNCTION from ADF

+: You decide when to execute

+: Can pass any param 

-: Got to write java but this is not so hard with DbCall. ;)