Wednesday, May 26, 2010

My ADF Presentations

Share this Post

During my visit to Karachi i decided to give presentations on "Oracle ADF introduction" and "Migrating Oracle Forms to ADF" to my colleagues at ORIX Leasing Pakistan based on my little research that i have done few months ago.

Following are the agenda of the presentations download slides from "The ShowCase" section on this blog
or click here
*) Introduction to Fusion Development using JDeveloper 11g
*) The Challenges: Oracle Forms Migration to Oracle ADF

Session 1 : Introduction to Fusion development using JDeveloper 11g

Session 2: The Challenges: Oracle Forms Migration to ADF

No technical post this month....

Take care,

Sunday, May 16, 2010

Adding Boolean Checkbox to Table Component in Oracle ADF

Share this Post

I have seen many people asked this question at OTN forums so i decided to blog it.

In Oracle ADF  af:selectBooleanCheckbox and af:selectBooleanRadio component has Boolean datatype which is not actually SQL supported data type (In Oracle we have Boolean in PL/SQL) and most of us stores boolean column values as Y/N or 1/0 in our tables.

Solution is pretty simple you have to add transient attribute in your ViewObject and set the values to your actual attribute based the values in transient attribute

Follow these steps:

1) Add a Transient Boolean attribute in your VO
2) Generate a Java class ViewRowImpl with Accessors of your VO
3) Set your attribute's values in Transient getter and setter method

Heres the example:
Create a table called checkboxer

create table checkboxer
(date_val timestamp,
 check_status varchar2(1 char));

- Create a ADF application and add checkboxer's EOs VO and AM

- Add a Transient attribute called StatusBoolean in VO

- Generate ViewRowImpl class replace the existing code with as shown in the slide

- Thats all Run the Business component browser and check the values
- Drop a collection as ADF table and right click the StatusBoolean column and choose Convert to.. and select Checkbox boolean.

Download the workspace

Happy Jdeveloping,

Saturday, May 8, 2010

Calling PL/SQL Procedure and Function in Oracle ADF application

Share this Post
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 ( 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;
  insert into logger
end insert_into_logger;

-- Function

create or replace function get_emp_fullname (empid in number) return varchar2 
 v_full_name varchar2(120);
  select first_name||' '||last_name
   into v_full_name
   from employees
   where employee_id = empid;
  return v_full_name;
 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 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;",
      // 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
      // 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
        } 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


Wednesday, May 5, 2010

Return a Random Rows from Table by SQL

Share this Post
Hi all,

While working on sample quiz application on ADF i got requirement to return the random questions from the table for every quiz user. For this I used DBMS_RANDOM package

I feel it would be cool to share it.

Example: Return Random 30 rows from the table

select mycolumn from
( SELECT mycolumn 
    FROM MyTable
dbms_random.value )
where rownum <= 30;