Wednesday, November 3, 2010

How to execute SQL DML statements in Oracle ADF


In some use cases it is required to execute just a simple DML (Insert, Update, Delete) statements in application.  so how to execute those in ADF? I will describe a one way to do this.

The steps are:

1. Create a method in Application Module (using DBTransaction and CallableStatement)
2. Expose that method to client interface
3. Drag and drop the method to your page
4. And optionally you can set the partial triggers and write a code to refresh your iterator bindings.

Here is the sample method in

  public int increaseSalary(int deptId, int increamentPercent) {
      DBTransaction trans = getDBTransaction();

      CallableStatement statement = null;
      int rows = 0;
      String plsql =
            " BEGIN " 
          + " UPDATE EMPLOYEES "
          + " SET SALARY = SALARY + (SALARY * ? / 100)"
          + " WHERE DEPARTMENT_ID = ?;"  
          + " END; ";
      statement = trans.createCallableStatement(plsql, 2);
      try {
          statement.setInt(1, increamentPercent);
          statement.setInt(2, deptId);
         rows = statement.executeUpdate();

      } catch (SQLException s) {
          throw new JboException(s);
      } finally {
          try {
              if (statement != null)
              return rows;
          } catch (SQLException s) { /* ignore */
  return rows;

Download the sample code

Watch this video for more info

Happy JDeveloping,
Zeeshan Baig


  1. Assalam-o-alikum Zeshan;
    Nice to see your blog here.
    I am a newbie in the field of Jdeveloper, I am a professional oracle developer using forms and reports and now a days E-Business suit.
    Development is my passion.
    I used to read blogs of
    shay shmeltzer and Grant Ronald.
    a very good stuff found on ur blogs.
    I have request to you please write some about...

    1. Generation of primary key with the help of some database package or function not using DB Sequence.
    e.g we use statement
    select nvl(max(dept_id), 0) + 1
    from departments;

  2. Wasalam,

    Imran thanks for appreciation. Your question to generation PKs is not the recommend way to generate sequence. in web based apps better choice is to use a sequence object. i recommend you get out of FORMS zone before getting into Jdev both are different products and different model structure so working with JDEV you have to think differently.

    any ways that was my thoughts. If you want to generate sequence from database the logic is kinda same you need to override method doDML in entityImpl.

    I will try to put some example of it.

    Take care,
    Zeeshan Baig

  3. Hi,

    How to download these videos


  4. Hi. Could you please try to help me with this ADF problem I'm having?

    I'm using "getTransaction().executeCommand(sql)" to execute a DELETE statement, but some users report that they get an error like this on commit:

    oracle.jbo.RowAlreadyDeletedException: JBO-25019: Entity row of key oracle.jbo.Key[573 6 1995 ] not found in...

    I know it's not going to be found, since I explicitely deleted it, but for some reason the business components think that row should still be there.

    I have called
    after the SQL DELETE statement. It works fine in my tests, but a few users still get the JBO-25019 error. Do you have any hint?

  5. What is the value of jbo.locking.mode in Application Module property. Make sure it is set to optimistic. Default is pessimistic.

    The details can he found here

  6. Thanks, i will check it out.
    I think i had it as optmistic, but maybe there is a misconfiguration somewhere. :-)

  7. Thanks, Your Blog is very useful. But i want to know what would be the solution if i want to query related to select statement.How can i play with recordsets.

  8. Hi Zeeshan,
    I learned from your blog a lot, thanks for that.
    However, with this particular one, I'm getting NULL from getDBTransaction() in the AM. I'm sure my db connection is there.
    Just wondering whether you come across this and any solution?
    Kind regards,

    1. Hi,

      Do you see anything in the logs ?


  9. Hi Zeeshan,
    1) I am getting "getDBTransaction method not found error" though I imported all below
    import oracle.jbo.server.DBTransaction;
    import oracle.jbo.server.DBTransactionImpl;
    import oracle.jbo.server.DBTransactionImpl2;
    import oracle.jbo.server.DatabaseTransactionFactory;
    import oracle.jbo.server.TransactionEvent;

    2) Another question... how can we write insert statement from backing bean ?


    1. Hi,

      What template are you using for ADF application. Make sure you add required libraries in the project.


  10. Hi Zeeshan,
    Thanks for your article and your help.
    I finally learned that getDBTransaction() has to be called from AMImpl or EOImpl.

    Till date I was trying to call it from bean and had a lot of frustrations !!!


  11. Mr Zeeshan I need ur help
    I want to execute select statement and and return many fields from the query
    just like Cursor

    1. Hi,

      I would assume you are trying to do this in Model layer you can do using RowsetIterator the example can be found on the following link

      // Custom method in an application module implementation class
      public void doSomeCustomProcessing() {
      ViewObject vo = getCustomerView1();
      // create secondary row set iterator with system-assigned name
      RowSetIterator iter = vo.createRowSetIterator(null);
      while (iter.hasNext()) {
      Row r =;
      // Do something with the current row.
      Integer custId = (Integer)r.getAttribute("Id");
      String name = (String)r.getAttribute("Name");
      System.out.println(custId + " " + name);
      // close secondary row set iterator

      if you want to utilize similar method on JSF page then expose this method to data control and use as operation binding


  12. Hi I am samruddhi. I have created a login page in ADF and now I am supposed to maintain session for user such that if a user is already logged in and tries to login again from different tab or browser then he is prompted that u are already logged in / please logout before login. My sir told me that it requires minute changes in configuration file but i can't find any way out. Kindly help me with descriptive steps or a video with code.
    I am using JDeveloper 11g release 2 .here is my email id :

    1. Hi,

      This might help