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