Thursday, March 11, 2010

ADF - PL/SQL Procedure or Function Call - Simple and Effective Tool

Even ADF Fusion has been brilliantly designed to rescue developers from coding direct calls to PL/SQL procedures and functions, sometime it still has to be done "by hand". After some time of using different techniques, from basic commands to using full frameworks, I finally wrote a helper class named DbCall, which 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.

A few advantages are here:
  • DbCall can be used equally simple for function and procedure calls.
  • No need to take care about types of IN parameters.
  • There is convenient and simple way of handling OUT params.
  • No need to build complex BEGIN/END PL/SQL string with question-marks.
  • It greatly simplifies the code you write
  • There is internal exception handling, but also can be moved externally.

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



Give it a try!
It is already in use on a big ADF-Fusion project!

DbCall ADF to PL/SQL wrapper is released under the GNU GPL licence and was published on the 12 March 2010.

Download DbCall source code here!

and please leave any comment if you find this code useful ... or whatever...

15 comments:

  1. Great start!

    Keep up the best work :)

    ReplyDelete
  2. This is excellent! I am using it every day!

    ReplyDelete
  3. Really good job! Keep up good posts like that ;)

    ReplyDelete
  4. This is indeed very simple and effective. In some cases the DatabaseProcedure class (provided by the Oracle JHeadstart team) can be a good alternative: http://adfplus.blogspot.com/2010/11/calling-oracle-database-procedure-from.html

    ReplyDelete
  5. the link to download dbcall class does not work, could y pls recover it.

    ReplyDelete
  6. How can I use this in my View Object?

    ReplyDelete
  7. Link to download DbCall class still does not work. Could you please recover it? Thanks!

    ReplyDelete
  8. The link to download DbCall class still does not work, could you please recover it? Thanks!

    ReplyDelete
  9. Hi Sacha, I'm using DBCall in a project and it works great. However, I found a situation that I'd like to share with you.

    Here is an example of a procedure signature:

    Procedure LISTA_ESTADO (
    p_NomeEstado OUT NomeEstadoTB,
    p_IdEstado OUT IdentifUnico,
    vr_CodRetorno OUT CodRetorno,
    batch_size IN INT,
    out_batch_size IN OUT INT,
    status OUT INT)

    The first three OUT parameters are based on an user created TYPE.
    In this example, the TYPES are defined as:

    TYPE NomeEstadoTB IS TABLE OF CHAR(25)
    INDEX BY BINARY_INTEGER;

    TYPE IdentifUnico IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;

    TYPE CodRetorno IS TABLE OF CHAR(10)
    INDEX BY BINARY_INTEGER;

    How to register these parameters using dc.addOut() ?

    Regards,
    Luis

    ReplyDelete
  10. Hi ...We are going to work on similar migration project ..froms to ADF..please can share any guidelines , best practices or any useful information..

    it would be helpfull..

    thanks

    ReplyDelete
  11. link dead? 404 not found error... please help.
    thanks. regards

    ReplyDelete
    Replies
    1. Should be ok now... please leave a comment if you find this code to be useful (or not) for you :-)

      Delete
  12. hi Sasha Stojanovic, found this article while searching on google for "Oracle ADF JDeveloper how to call stored procedure / function" it is very nice, allow me to use without further coding but there is something i want to ask about, if my function return oracle collection type (i have some pipelined functions to return required data in a created Type), the DBCall allow me to return collection type? as in mentioned examples there is nothing to illustrate it.

    ReplyDelete
    Replies
    1. I understand perfectly what you need and honestly I would also like to have that feature for my development needs, but I think it is not possible by using the existing class implementation, which is based on SQL predefined data types only (see for example http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html?is-external=true). Albeit I think it may be possible to make it working for collections as you described, still it looks it could turn out to become a quite lot of work, but unfortunatelly I am already overloaded on the current project...

      Delete






Copyright © 2010 Sasha Stojanovic.