Saturday, May 8, 2010

Calling PL/SQL Procedure and Function in Oracle ADF application

Hi all,

Migrating Oracle forms applications to ADF the one of the biggest issue is what to do with you existing PL/SQL code. As we all aware that ADF is JEE technology based and it cannot directly interact with PL/SQL as we used to do in Oracle forms application.

The simplest solution is to migrate your existing code to the database and use that PL/SQL as exposed web-service in our ADF application and today i will show you the example of it.

Where should the business logic code resides?
Its a million dollar question and my views that It depends on application to application. If we analyze that this code can work inside database so we should move it to database and if the logic depends on some UI interaction (e.g looping over the rows etc etc) then it should be the combination of both.

Mr.Tom Kyte (asktom.oracle.com) shared his experience in one of his presentations about migrating business logic code to database and i agreed with him in some context.
Migrating business logic code to database is better choice as we can manage our code centralized  and also you never know what will happen in next 5 to 10 years technology changing quickly today Java, ADF is HOT tomorrow who knows :) so logic stored in database will save us from major re-writing the same code again.


In this example:
- We will use Oracle default HR Schema.
- We will create a stored package in PL/SQL which will insert some data into table.
- We will create a stored function which will return the complete name of Employee i.e First_name||' '||Last_name.
- We will add a code in our Application ModuleImpl Class and expose all methods to client interface.
- Integrate those methods on JSF page.

I assumed that you are already setup a project with at least Application Module (AM)

Create PL/SQL Package Procedure and function:
Note: for sake of understanding i am keeping things simple

--Table to wok with Procedure.
create table logger
(log_time timestamp)
tablespace users;

-- Procedure
create or replace procedure insert_into_logger as 
pragma autonomous_transaction;
begin
  insert into logger
  values(systimestamp);
  commit;
end insert_into_logger;

-- Function

create or replace function get_emp_fullname (empid in number) return varchar2 
as
 v_full_name varchar2(120);
begin
  select first_name||' '||last_name
   into v_full_name
   from employees
   where employee_id = empid;
   
  return v_full_name;
exception
 when no_data_found then 
   return null;
end get_emp_fullname;

Generate Java Class for Application Module
Double click the Application Module and click the Pencil Icon appearing with the Java classes.


- Check the first checkbox and Press OK

Double click the AppModuleImpl.java under your Application Module and add the following code note that this code is based on my example if you function and procedure taking more argument you need to slightly change the code you can check examples here


  private static int VARCHAR2 = Types.VARCHAR;

  protected Object callStoredFunction(int sqlReturnType, String stmt,
                                      Object[] bindVars) {
    CallableStatement st = null;
    try {
      // 1. Create a JDBC CallabledStatement
      st =
          getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;",
                                                     0);
      // 2. Register the first bind variable for the return value
      st.registerOutParameter(1, sqlReturnType);
      if (bindVars != null) {
        // 3. Loop over values for the bind variables passed in, if any
        for (int z = 0; z < bindVars.length; z++) {
          // 4. Set the value of user-supplied bind vars in the stmt
          st.setObject(z + 2, bindVars[z]);
        }
      }
      // 5. Set the value of user-supplied bind vars in the stmt
      st.executeUpdate();
      // 6. Return the value of the first bind variable
      return st.getObject(1);
    } catch (SQLException e) {
      throw new JboException(e);
    } finally {
      if (st != null) {
        try {
          // 7. Close the statement
          st.close();
        } catch (SQLException e) {
        }
      }
    }
  }

- Add Method to call database functions

  public String getEmpFullName(Number n) {
    return (String)callStoredFunction(VARCHAR2, "get_emp_fullname(?)",
                                      new Object[] { n });
  }

  public void logProcedure() {
    getDBTransaction().executeCommand("begin insert_into_logger; end;");
  }

- Right click and choose Make to compile the file.

Go to again Application Module and below Java class and click Client Interface to Expose methods on Client interface. Shuttle the methods to selected area and Click OK

- Open the JSF page and expand the data control
- Drag the logProcedure Method to the page and choose ADF button

- Drag the getEmpFullname function to the page and choose ADF parameter form (it is a function so we have to bind the input and return values)



-Drag and drop the String (output parameter) as Output Text

- The page on design time look like this

- Right click and choose Run . Enter 100 in the Input Parameter and Press Get Employee Name Button.
- Press Test Log procedure button couple of times.

 - Query the logger table as we can see the procedure inserting timestamp data



Download the example


More Info: Fusion Developer guide

Cheers,
Baig

56 comments:

  1. Thanks for the example, what if I wanted to call this method that is exposed from the application module in a backing bean. What is the easiest way to do this?

    ReplyDelete
  2. Thanks for comments another way to call methods on bean using Bindings

    BindingContainer bindings = getBindings();

    OperationBinding operationBinding = bindings.getOperationBinding("logProcedure");
    Object result = operationBinding.execute();

    if (!operationBinding.getErrors().isEmpty()) {
    return null;
    }
    return null;

    ReplyDelete
  3. Thanks for the example , what if I wanted to call this method that is exposed from the application module when validate the binding not when press the button . What is the easiest way to do this?

    ReplyDelete
  4. Hi yasser,

    To validate via PL/SQL a great post by Andrejus would help you http://andrejusb.blogspot.com/2010/04/implementing-custom-generic-plsql.html

    ReplyDelete
  5. Thanks for sharing your post and it was superb .I would like to hear more from you in future too.

    ReplyDelete
  6. Nice Post.
    How i can attach view accessor with parameter of method so that when an adf page is created from method parameters, that field can be inputListOfValues

    ReplyDelete
  7. Please clearly describe your use case. i didnt get your point.

    ReplyDelete
  8. can i apply this call procedure and function for effective Date?

    1) search effective date by calling procedure/function

    2)update effective date by calling function passing parameter.

    how can i acheived this? please help

    ReplyDelete
  9. hi rock,

    if i understand you correctly then do you want to call functions in some expression?

    Yes you can call using Groovy expression in ADF-BC using adf.object.YouMethodName in entityImpl

    I hope it helps,
    Zeeshan

    ReplyDelete
  10. Dear Zeeshan.
    i am new to adf,jdeve.
    i was testing your code, to use function,
    but i am getting following error.
    Error(96,34):Cannot find Variable types
    Error(99,33):Cannot find Class CallableStatement
    Error(117,19):Cannot find class JboException.

    can u please tell me how to clear this errors.

    ReplyDelete
  11. Hi Eqbal,

    I guess some you are missing Java as technology scope in the projects.

    1. download my sample and run
    2. Create a new application and take Fusion ADF as template for it.

    Hope it helps
    ZB

    ReplyDelete
  12. Hi Baig,
    Thanks for sharing, really informative. I'm not very good at Java, I came from a purely Oracle procedural language background. I just want to ask, do we put the callStoredFunction, getEmpFullName and logProcedure inside the public class AppModuleImpl? Is it after the following line: public class AppModuleImpl extends ApplicationModuleImpl?

    I apologize if the question it too basic.
    Thanks in advance!

    ReplyDelete
  13. Hi derrick,

    Correct In my example i am doing this in ApplicationModuleImpl class. But in general you can call where ever you need this.

    If you read the post you will find the text

    "Double click the AppModuleImpl.java under your Application Module and add the following code"

    Hope it helps,
    ZB

    ReplyDelete
  14. Dear Baig.
    if we want to pass two parameters, like first name and employee number, then how to pass the two parameters.

    Thanks.

    ReplyDelete
  15. Hi,

    Eqbal you can register one more parameter then use that values in your code.

    registerOutParameter(2, String); etc etc

    Check the fusion developer guide link provided in the end of the post.

    ZB

    ReplyDelete
  16. Dear Baig.
    your previous suggestion, workd. Thanks.
    i need one more help. i am trying to embed the swf fle (Flash) in my adf home page, how can we acomlish it.
    Thanks in advance.

    ReplyDelete
  17. Hi Eqbal,

    I posted a blog post regarding your question http://www.baigzeeshan.com/2011/11/how-to-play-flash-swf-file-in-oracle.html

    ZB

    ReplyDelete
  18. hi zeeshan,
    how can i contact you ...
    m new to adf.....n badly messed up.....
    m in bad need of ur help......kindly share a point of contact with u .....
    Thanx....

    ReplyDelete
  19. Hi Mohit,

    You can contact me by clicking Contact me button at top.

    ZB

    ReplyDelete
  20. How can we use function call at asimple button click...
    i don;t want to drag that getFullName() method.
    I have created a i/p text and a button and a o/p text.
    nd want that function on my button click

    ReplyDelete
  21. Hi Shish,

    You can call method binding using java code.

    1. Add method binding in Page definition.
    2. Call method binding in managed bean on the button.

    // get an Action or MethodAction
    OperationBinding method = bindings.getOperationBinding("methodAction");
    method.execute();
    List errors = method.getErrors();

    method = bindings.getOperationBinding("methodAction");
    Map paramsMap = method.getParamsMap();
    paramsMap.put("param","value") ;
    method.execute();

    Hope it helps
    ZB

    ReplyDelete
    Replies
    1. this is for button binding but what about input text(Where we enter EMPNO) and about output text(For Emp Full Name); how to bind that values.,.,

      Please upload an demo of Button Click application that call a function

      Delete
  22. Hi Ashish,

    Please clear your use case why don't you want to drag and drop? It has the same effect it creates binding in the page definition

    You can pass the values of empno and name via bean attribute to your method in paramsMap.put (mentioned above)

    Why to take longer route when you can do the same thing declaratively.

    ZB

    ReplyDelete
  23. Hi Zeeshan,

    When I ran your application, and entered the emp id, it showed an error after pressing the button

    ORA-06550: line 1, column 14: PLS-00201: identifier 'GET_EMP_FULLNAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

    How can it be resolved ?

    Nigel.

    ReplyDelete
  24. Hi nigel,

    Make sure you created the function in the database and same schema that you are connecting Jdev with.

    I have provided the script in this post.

    ZB

    ReplyDelete
  25. Hi Zeeshan,

    I'm a newbie in ADF. How can I call a PL/SQL procedure without user input ? I would like to call a PL/SQL stored procedure with the data displayed in a af:table. How can I do that ?

    ReplyDelete
    Replies
    1. Hi,

      Can you tell me exact event when you want to execute your code. You can check Page Phase listener event as well. Search this blog for that.

      Delete
    2. Hi,

      Upon selection of a row in a af:table, I will display a confirmation dialog (popup) and I would like to call my PL/SQL procedure when the user has confirmed it (onDialogClose). Please advise

      Delete
    3. You can call the method using Operation binding on Dialog close event.

      I am copying my earlier comments.


      You can call method binding using java code.

      1. Add method binding in Page definition.
      2. Call method binding in managed bean on the button.

      // get an Action or MethodAction
      OperationBinding method = bindings.getOperationBinding("methodAction");
      method.execute();
      List errors = method.getErrors();

      method = bindings.getOperationBinding("methodAction");
      Map paramsMap = method.getParamsMap();
      paramsMap.put("param","value") ;
      method.execute();

      Hope it helps
      ZB

      Delete
  26. thank you very much for this clear example its very helpful

    ReplyDelete
  27. I overlooked it, sorry about that. It's working fine now.

    Thanks ZB

    ReplyDelete
  28. Hi Zeeshan,
    Its a very helpful post.My requirement is a bit diff.I am trying to call a pl/sql (Create)procedure API in a remote server through my ADF application using a dblink.I have added the code for calling the pl/sql procedure in my AppModuleImpl.Added a managed bean in the submit button to save the values in the remote database.But due to some reason the value is not getting save.No errors in the log either.I hope I am making sense.Can you help me out in this?
    Thanks,
    Prasanna

    ReplyDelete
  29. This comment has been removed by a blog administrator.

    ReplyDelete
  30. hi,

    thanks for the post, the example u shared is working fine.
    but when i tried to use the same for my function which has four String parameters and doesnt have any return type it is giving
    java.sql.SQLException: Invalid column index

    this is how i modified
    protected void callStoredFunction(int sqlReturnType, String stmt,
    Object[] bindVars) {
    CallableStatement st = null;
    try {
    // 1. Create a JDBC CallabledStatement
    st =
    getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;",
    0);
    // 2. Register the first bind variable for the return value
    // st.registerOutParameter(1, sqlReturnType);
    if (bindVars != null) {
    // 3. Loop over values for the bind variables passed in, if any
    for (int z = 0; z < bindVars.length; z++) {
    // 4. Set the value of user-supplied bind vars in the stmt
    st.setObject(z + 2, bindVars[z]);
    }
    }
    // 5. Set the value of user-supplied bind vars in the stmt
    st.executeUpdate();
    // 6. Return the value of the first bind variable
    // return st.getObject(1);
    } catch (SQLException e) {
    throw new JboException(e);
    } finally {
    if (st != null) {
    try {
    // 7. Close the statement
    st.close();
    } catch (SQLException e) {
    System.out.println("####################Exception Occured: " + e.getMessage());
    }
    }
    }
    }

    public void PROC_MAIN(String tableName, String columnName, String patternName, String algorithName) {
    callStoredFunction(VARCHAR2, "PROC_MAIN(?)",
    new Object[] { tableName, columnName, patternName, algorithName });
    }

    ReplyDelete
    Replies
    1. Hi,

      Invalid column index exception means that your code is not in order with parameter positions. Please check that values your are passing to java method and database procedure.

      Hope it help,
      ZB

      Delete
  31. Hi Baig,
    Thanks for your post.

    Could you please give an example to handle result sets returning from a PL SQL function whose return type Ref Cursor.
    What will be assigned to sqlreturn type variable as shown below statement "st.registerOutParameter(1, sqlReturnType);"
    Could you please edit your code according to PLSQL Ref Cursor
    structures

    ReplyDelete
  32. Could u please share the same code for Pl Sql Ref Cursor

    ReplyDelete
    Replies
    1. Hi,

      You can use the same check this page http://jobinesh.blogspot.com/2010/02/sample-adf-bc-application-based-on.html

      ZB

      Delete
  33. Very useful information. Thanks and keep posting :)

    ReplyDelete
  34. Hi Baig,

    I am using your code to call a function.
    PFB the fuction description:

    Create or replace package body "APP_PROFILE_PKG" as

    function get_value(p_name varchar2) return varchar2 is
    l_return_value ifcas.app_profiles.profile_value%type;
    begin
    select profile_value
    into l_return_value
    from ifcas.app_profiles
    ,global_name
    where profile_name = upper(p_name) and app_site = global_name;
    return l_return_value;
    exception
    when no_data_found
    then return null;
    end;


    I have modified your code as:

    private static int VARCHAR2 = Types.VARCHAR;

    protected Object callStoredFunction(int sqlReturnType, String stmt,
    Object[] bindVars) {
    CallableStatement st = null;
    try {
    // 1. Create a JDBC CallabledStatement
    st =
    getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;", 0);
    // 2. Register the first bind variable for the return value
    st.registerOutParameter(1, sqlReturnType);
    if (bindVars != null) {
    // 3. Loop over values for the bind variables passed in, if any
    for (int z = 0; z < bindVars.length; z++) {
    // 4. Set the value of user-supplied bind vars in the stmt
    st.setObject(z + 2, bindVars[z]);
    }
    }
    // 5. Set the value of user-supplied bind vars in the stmt
    st.executeUpdate();
    // 6. Return the value of the first bind variable
    return st.getObject(1);
    } catch (SQLException e) {
    throw new JboException(e);
    } finally {
    if (st != null) {
    try {
    // 7. Close the statement
    st.close();
    } catch (SQLException e) {
    }
    }
    }
    }

    public String getProfileValue(String n) {
    return (String)callStoredFunction(VARCHAR2, "get_value(?)",
    new Object[] { n });
    }

    public void logProcedure() {
    getDBTransaction().executeCommand("begin insert_into_logger; end;");
    }


    But when I am clicking the button to get the result, i am getting the below error:

    PLS-00306: wrong number or types of arguments in call to 'GET_VALUE'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Kindly suggest what changes I can make to make it run successfully.
    A quick reply will be appreciated.

    Thanks in advance

    ReplyDelete
    Replies
    1. Hi,

      Please verify if you are able to successfully execute the function in SQL developer.

      Check the data type of ifcas.app_profiles.profile_value%type it should be VARCHAR2 as your function is returning this type.

      Regards,
      Zeeshan

      Delete
    2. Hi Baig,
      Thanks for your reply.

      As you suggested, i checked executing the function and its getting executed successfuly and also the data type is VARCHAR2 only.

      Now the error i m getting is:

      java.sql.SQLException: ORA-06550: line 1, column 14:
      PLS-00201: identifier 'GET_VALUE' must be declared
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored.

      Where should this 'GET_VALUE' function be declared.
      Please suggest.

      Thanks in advance.

      Delete
    3. Hi,

      Can you try a simple example as provided in this post. If this is not working then figure it out where you are making mistake.

      The steps are also provided in Jdev documentation http://docs.oracle.com/cd/E14571_01/web.1111/b31974/bcadvgen.htm#sm0297

      Delete
  35. This comment has been removed by a blog administrator.

    ReplyDelete
  36. hey zeeshan ...its nice article... keep posting :)

    ReplyDelete
  37. HI Zeeshan, please can you provide an example or a guide on how to create an ADF form that inserts and updates the employees table or probably a tip on how to go about.

    Regards!

    ReplyDelete
    Replies
    1. Hi,

      Just search for this blog Your first ADF form. you will get it.

      Zeeshan

      Delete
  38. Hi Zeeshan, I mean an example/guide on calling plsql insert and update functions that interacts with an oracle ADF form.

    Regards!

    ReplyDelete
    Replies
    1. Hi,

      You can do that using this post.

      1. Create a package with 2 stored procedure, One does insert and other for update in table
      2. call these packages in different java methods as shown above
      3. Expose these methods to data control and call them using Operation Binding or simple button drag and drop.

      Hope it help,
      Zeeshan

      Delete
  39. Hi,
    It is good but how I can to execute procedure without form
    e.g. my procedure name getGroupPolicy(Sting st) and I need to invoke this procedure in Rendered region of component with static input
    e.g. Rendered="#{security.userInRole('GroupA')}" and my procedure getGroupPolicy take input return string "#{security.userInRole('GroupA')}"
    I have created procedure and invoke it from database but i can only drag and drop it with form .. and I need to invoke it as Rendered= getGroupPolicy(ph3)
    to be when return value as Rendered="#{security.userInRole('GroupA')}"
    please help me

    ReplyDelete
    Replies
    1. Hi,

      You can call any method using Operation binding in Java as far it is available in page bindings check this http://oracleadfhowto.blogspot.com/2012/03/calling-adf-action-binding.html

      You can define hard coded parameters in page bindings as well.

      Zeeshan

      Delete
  40. Hello, I'm trying to get many out parameters like this:

    public ProRecallPlatesBean getCallProRecallPlates(int numPlates) {
    CallableStatement st = null;
    try {
    // 1. Define the PL/SQL block for the statement to invoke
    String stmt = "begin CTS.Pk_PreIn.proRecallPlates(?,?,?,?,?,?); end;";
    // 2. Create the CallableStatement for the PL/SQL block
    st = getDBTransaction().createCallableStatement(stmt,0);
    // 3. Register the positions and types of the OUT parameters
    st.registerOutParameter(2,Types.VARCHAR);
    st.registerOutParameter(3,Types.VARCHAR);
    st.registerOutParameter(4,Types.VARCHAR);
    st.registerOutParameter(5,Types.VARCHAR);
    st.registerOutParameter(6,Types.VARCHAR);
    // 4. Set the bind values of the IN parameters
    st.setObject(1,numPlates);
    st.executeUpdate();
    // 6. Create a bean to hold the multiple return values
    ProRecallPlatesBean result = new ProRecallPlatesBean();
    // 7. Set values of properties using OUT params
    result.setSpfVal(st.getString(2));
    result.setTransportTypeVal(st.getString(3));
    result.setTransportCompanyVal(st.getString(4));
    result.setCompanyDescrVal(st.getString(5));
    result.setDGAPrint(st.getString(6));
    // 9. Return the result
    return result;

    Now That I have "result" filled, how can I display those out values into Output Text components? sorry i'm starting to learn ADF, thanks in advance i'll appreciate it A LOT!!

    ReplyDelete
    Replies
    1. Follow this post you will find it how

      Zee

      Delete
  41. thanks alot , but i cant find the example

    ReplyDelete