Developer: Java
   DOWNLOAD
 Oracle JDeveloper 11g & ADF
 Sample Code
   TAGS
java, adf, All

Integrating the Oracle Designer Legacy Table API with
Oracle JDeveloper 11g ADF Business Components

by Chris Muir

Learn how to give your Designer-based systems new life by integrating the Oracle Designer Table API with Oracle JDeveloper.

Published October 2007

Oracle Designer is now firmly in maintenance mode, but it is still common to encounter legacy database schemas that have Designer's fingerprints all over them, with generated Table APIs and good old cg_ref_codes. Such constructs may be unfamiliar to the newer Oracle developers in the crowd, but this approach was the cutting edge at one time.

Fortunately, with Oracle JDeveloper 11g's various enhancements to Application Development Framework Business Components (ADF BC), such legacy code needn't be ignored or replaced. By embracing ADF BC's inherent support for calling database PL/SQL, you can give new life to your legacy Oracle Designer database systems.

This article specifically investigates integrating the Oracle Designer Table API with Oracle JDeveloper 11g (in Technical Preview 2 at the time of this writing).

Oracle Designer Metamorphosis

The contemporary Oracle development scene is radically different from that just 10 years ago. Today we have cutting-edge tools such as Oracle SOA Suite, Oracle XML DB, Oracle Application Express, and of course Oracle JDeveloper's ADF. Traditionally, however, Oracle Forms and Reports were the tools of choice, and building 100-percent generated Forms and database applications via Oracle's own CASE tool, Oracle Designer, was the state of the art.

The goal behind 100-percent generation was to define and design your modules, usually Forms, completely within Oracle Designer. The key advantage of this approach was that it also included your table definitions, upon which your modules were dependent. Changing a table definition in Oracle Designer automatically flowed onto the defined modules, or in the case of Oracle Forms modules, the defined blocks. For instance, when increasing a table column size or dropping a column, the relating Form would reflect the fact by enlarging or dropping the column appropriately. Once the developer was satisfied with the results, he or she would "generate" a real Form from Oracle Designer, ready to use.

While Oracle Designer is enjoying a slow sunset at many Oracle sites and 100-percent generation is no longer a common goal, Oracle Designer-generated code can be found in many existing legacy systems beyond just the modules it created, particularly in the database. Database artifacts such as Table APIs, journal tables, and good old cg_ref_codes can be found in many schemas.

Do these legacy database artifacts present an integration challenge for such a modern tool as Oracle JDeveloper 11g's ADF? This article will prove to you, specifically by looking at the integration of Oracle Designer's Table API into ADF, that the answer is: "Not at all."

Knowledge Requirements

This article assumes an understanding of Oracle JDeveloper ADF BC, including Entity Objects and View Objects, as well as exposure to Java coding and JDBC programming. A healthy dose of SQL and PL/SQL will assist the reader too.

The Data Model

For this article, consider the following simple data model:

Figure 1
Figure 1 Data model

The data model comprises two tables, organizations and events, with a one-to-many relationship. For example, the organization Sage Computing Services may hold over time many Oracle training events in Australia.

Downloads

A demonstration Oracle JDeveloper 11g application for this article can be downloaded here. It contains the complete source code, whereas the examples in this article are truncated for brevity.

The downloaded Oracle JDeveloper application contains a project database that includes scripts for creating the demonstration schema and necessary database objects. Run the install.sql script in SQL*Plus within your favorite database development account to create the sample schema. Then be sure to change the Oracle JDeveloper applications default connection details to connect to your database and schema of choice.

Oracle Designer Database Artifact: The Table API

One such Oracle Designer database artifact is the Table API.

Not only can Oracle Designer generate Oracle Forms, but it can also create a set of packages to wrap specified schema tables, known as the Table Application Programming Interface (Table API). The packages allow the calling program to indirectly select, insert, update, and delete the relating table data through the Table API PL/SQL packages.

The intention was that the generated Oracle Forms would not directly read and write to the table via DML but instead use the block level feature of basing an Oracle Form on a database package. In turn Oracle Designer would allow specific business rules to be generated into the Table API, which would now be enforced on the relevant Oracle Form dependent. At some sites, considerable programming logic and investment in the development of this logic could be created with the Table API structure.

If we consider the events table in our demo data model, a generated Table API package for the table would look as follows. (Note that this is a cut-down version of what would be generated by Oracle Designer to focus on what is needed for this article. From the Download section, above, you can obtain the complete Table API examples for the sample data model.)

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE OR REPLACE PACKAGE cg$EVENTS IS
                  
  TYPE cg$row_type IS RECORD
  (EVENT_NO     cg$row.EVENT_NO%TYPE
  ,ORG_ID       cg$row.ORG_ID%TYPE
  ,DESCRIPTION  cg$row.DESCRIPTION%TYPE
  ,CONTACT_NAME cg$row.CONTACT_NAME%TYPE
  ,START_DATE   cg$row.START_DATE%TYPE
  ,END_DATE     cg$row.END_DATE%TYPE
  ,COMMENTS     cg$row.COMMENTS%TYPE
  ,the_rowid    ROWID);

  TYPE cg$ind_type IS RECORD
  (EVENT_NO     BOOLEAN DEFAULT FALSE
  ,ORG_ID       BOOLEAN DEFAULT FALSE
  ,DESCRIPTION  BOOLEAN DEFAULT FALSE
  ,CONTACT_NAME BOOLEAN DEFAULT FALSE
  ,START_DATE   BOOLEAN DEFAULT FALSE
  ,END_DATE     BOOLEAN DEFAULT FALSE
  ,COMMENTS     BOOLEAN DEFAULT FALSE);

  PROCEDURE ins
  (cg$rec IN OUT cg$row_type,
   cg$ind IN OUT cg$ind_type,
   do_ins IN BOOLEAN DEFAULT TRUE);

  PROCEDURE upd
  (cg$rec IN OUT cg$row_type,
   cg$ind IN OUT cg$ind_type,
   do_upd IN BOOLEAN DEFAULT TRUE,
   cg$pk  IN cg$row_type DEFAULT NULL);

  PROCEDURE del
  (cg$pk  IN cg$pk_type,
   do_del IN BOOLEAN DEFAULT TRUE);

  PROCEDURE lck
  (cg$old_rec  IN cg$row_type,
   cg$old_ind  IN cg$ind_type,
   nowait_flag IN BOOLEAN DEFAULT TRUE);

  PROCEDURE slct
  (cg$sel_rec IN OUT cg$row_type);

END cg$EVENTS;

Note that the package provides procedures for inserting, updating, deleting, locking, and selecting data from the underlying table.

The cg$EVENTS.ins procedure takes the following parameters:

  • cg$rec: a PL/SQL record type defined in the same package that contains all the columns for the table. The values passed in the record to the procedure will be inserted into the events table. Potentially the Table API may modify the submitted values, so cg$rec is returned as an OUT parameter to allow the calling module to obtain these changes and apply them to its own data set.
  • cg$ind: a second PL/SQL record type defined within the cg$EVENTS package, comprised of a number of boolean columns used to indicate which cg$rec columns have been populated. It is returned by the ins procedure within the Table API to indicate if it has modified or defaulted any values, allowing the calling module to know which columns were updated.
  • do_ins: a simple boolean flag instructing the procedure to actually do the insert.

The cg$EVENTS.upd procedure takes a similar set of parameters to the ins procedure, with the addition of the cg$pk record type representing the primary key values of the record to be updated, and a flag to undertake the update operation.

The cg$EVENTS.del procedure takes a cg$pk record to indicate which record to delete and adds a flag to indicate if the operation should be undertaken.

The cg$EVENTS.lck procedure is used to lock a record before updating or deleting it in the database. The procedure takes a record type cg$old_rec containing all the old values before an update or delete statement occurs, as well as cg$old_ind indicating which columns were updated.

The nowait_flag boolean indicates that in taking the lock-wait out, the lock statement is issued with a no-wait option.

Using the Table API with ADF Business Components

Within Oracle JDeveloper's ADF BC, you typically create an Entity Object (EO) to represent every table in your database schema that you want to use in your application. The EO is designed, when the user makes changes to the relating data in your application, to collate the data and issue the DML insert, update, and delete statements as well as the row lock statement against the database.

The Oracle Application Development Framework Developer's Guide for Forms/4GL Developers outlines in Section 26.4 the basics of basing an EO on a database PL/SQL package API. The following outlines what needs to be done to custom-fit specifically to the Oracle Designer Table API.

The default EO data manipulation language (DML) functionality can easily be overridden by changing the chokepoint DML method doDML() within the EO's custom EntityImpl class. The default doDML() method appears as follows:

01
02
03
protected void doDML(int operation, TransactionEvent e) {
  super.doDML(operation, e);
}

The method's operation parameter indicates whether it should undertake an insert, update, or delete operation. Thus you can change the doDML() method to the following to handle each DML statement separately:

01
02
03
04
05
06
07
08
09
10
protected void doDML(int operation, TransactionEvent e) {
  if (operation == DML_INSERT) {
    // Insert logic
  } else if (operation == DML_UPDATE) {
    // Update logic
  } else if (operation == DML_DELETE) {
    // Delete logic
  } else // unlikely but you never know
    super.doDML(operation, e);
}

Now within each if code block, you can write your own logic to call the database package procedures. To do this, you'll use JDBC to collate the EO data and issue your own custom call to the database, passing in values from your EO and accepting change of values back from the appropriate cg$EVENTS procedures.

Overcoming a JDBC Limitation with Oracle Object Types

Typically with JDBC, you'd have no problems calling a database procedure passing in simple datatypes. However, the cg$EVENTS.ins procedure takes two PL/SQL record types, which unfortunately aren't directly supported by JDBC.

A simple solution is to create database object types instead, which JDBC does support. To do this, take the following steps:

  1. For both the PL/SQL record types you're interested in, you need to create a database object type that reflects the same amount of columns plus a large string to hold the rowid. For example, the following object type cg$events_cg$row_type is a mirror of the cg$EVENTS.cg$row_type PL/SQL record type.

    01
    02
    03
    04
    05
    06
    07
    08
    09
    
    CREATE OR REPLACE TYPE cg$events_cg$row_type AS OBJECT
    (event_no     NUMBER(6)
    ,org_id       NUMBER(4)
    ,description  VARCHAR2(75)
    ,contact_name VARCHAR2(30)
    ,start_date   DATE
    ,end_date     DATE
    ,comments     VARCHAR2(1000)
    ,the_rowid    VARCHAR2(256));
    

  2. In addition, the following object type cg$events.cg$ind_type reflects the cg$EVENTS.cg$ind_type PL/SQL record type. However, note how you replace the boolean datatypes with a number(1) to reflect TRUE (= 1) or FALSE (= 0), as the boolean datatype isn't supported by Oracle Object Types:

    01
    02
    03
    04
    05
    06
    07
    08
    
    CREATE OR REPLACE TYPE cg$events_cg$ind_type AS OBJECT
    (event_no     NUMBER(1)
    ,org_id       NUMBER(1)
    ,description  NUMBER(1)
    ,contact_name NUMBER(1)
    ,start_date   NUMBER(1)
    ,end_date     NUMBER(1)
    ,comments     NUMBER(1));
    

  3. Next you write a wrapper procedure for cg$EVENTS.ins to accept instances of the objects from above, that converts these to the associated PL/SQL record type and then passes them to cg$EVENTS.ins. You'll create a new package for the wrapper procedure events_pkg:

    01
    02
    03
    04
    05
    06
    07
    08
    09
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    
    CREATE OR REPLACE PACKAGE BODY events_pkg AS
    
    PROCEDURE ins
    (row      IN OUT cg$events_cg$row_type
    ,ind      IN OUT cg$events_cg$ind_type
    ,doInsert IN BOOLEAN)
    IS
      rowPlsqlItem cg$events.cg$row_type;
      indPlsqlItem cg$events.cg$ind_type;
    
      FUNCTION to_int(aBool BOOLEAN)
      RETURN number IS
      BEGIN
        IF aBool THEN RETURN 1; ELSE RETURN 0; END IF;
      END;
    
    BEGIN
      rowPlsqlItem.event_no     := row.event_no;    
      rowPlsqlItem.org_id       := row.org_id;      
      rowPlsqlItem.description  := row.description; 
      rowPlsqlItem.contact_name := row.contact_name;
      rowPlsqlItem.start_date   := row.start_date;  
      rowPlsqlItem.end_date     := row.end_date;    
      rowPlsqlItem.comments     := row.comments;    
      rowPlsqlItem.the_rowid    := row.the_rowid;  
    
      indPlsqlItem.event_no     := ind.event_no     = 1;  
      indPlsqlItem.org_id       := ind.org_id       = 1;
      indPlsqlItem.description  := ind.description  = 1;
      indPlsqlItem.contact_name := ind.contact_name = 1;
      indPlsqlItem.start_date   := ind.start_date   = 1;
      indPlsqlItem.end_date     := ind.end_date     = 1;
      indPlsqlItem.comments     := ind.comments     = 1;
                                                     
      cg$events.ins(rowPlsqlItem, indPlsqlItem, doInsert);
                                                     
      row.event_no     := rowPlsqlItem.event_no;     
      row.org_id       := rowPlsqlItem.org_id;       
      row.description  := rowPlsqlItem.description;  
      row.contact_name := rowPlsqlItem.contact_name; 
      row.start_date   := rowPlsqlItem.start_date;   
      row.end_date     := rowPlsqlItem.end_date;     
      row.comments     := rowPlsqlItem.comments;     
      row.the_rowid    := rowPlsqlItem.the_rowid;    
                       
      ind.event_no     := to_int(indPlsqlItem.event_no);
      ind.org_id       := to_int(indPlsqlItem.org_id);
      ind.description  := to_int(indPlsqlItem.description);
      ind.contact_name := to_int(indPlsqlItem.contact_name);
      ind.start_date   := to_int(indPlsqlItem.start_date);
      ind.end_date     := to_int(indPlsqlItem.end_date);
      ind.comments     := to_int(indPlsqlItem.comments);
    
    END ins;
    

For the purposes of this article, the above code has not been modularized for readability and clarity. Obviously, modularization will facilitate good code reuse, and the downloadable sample code uses further procedures and functions to reduce the code duplication.

In addition, for brevity, it does not list the equivalent events_pkg update, delete, and lock functions. Again, the downloadable sample code shows the full set of code.

Note that the events_pkg.lck procedure requires the following additional object type, as it only requires a list of primary key values:

01
02
03
CREATE OR REPLACE TYPE cg$events_cg$pk_type AS OBJECT
(event_no  NUMBER(6)
,the_rowid VARCHAR2(256));

Creating a Custom EntityImpl Superclass

As outlined in Section 26.4.3 of The Oracle Application Development Framework Developer's Guide for Forms/4GL Developers, it makes sense to create a custom EntityImpl superclass to write your JDBC logic to promote code reuse. As in the guide, you'll create a superclass PlsqlEntityImpl:

01
02
03
04
05
package common;


import oracle.jbo.server.EntityImpl;


public class PlsqlEntityImpl extends EntityImpl { }

Then for the EOs that require the JDBC logic, you can extend them to make use of this new superclass:

01
public class EventsImpl extends PlsqlEntityImpl

This allows you to write custom routines in the superclass PlsqlEntityImpl to handle the insert, update, delete, and lock JDBC calls to your relevant database packages.

Writing a JDBC Insert/Update Function

Once you have your superclass PlsqlEntityImpl class, write a function to undertake inserts or updates together, as the Table API specifications are nearly identical. A description of the function follows the code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
public void insertUpdate(int[] attributes
                        ,String dbRowTypeName
                        ,String dbIndTypeName
                        ,String dbStatement) {
                                                
  try {

    Object preRowArray[]  = new Object[attributes.length + 1];
    Object postRowArray[] = new Object[attributes.length + 1];

    Boolean preIndArray[] = new Boolean[attributes.length];
    Object postIndArray[] = new Object[attributes.length];

    for (int i = 0, size = attributes.length; i < size; i++)
      preRowArray[i] = getAttribute(i);

    for (int i = 0, size = attributes.length; i < size; i++)
      preIndArray[i] = isAttributeChanged(i);

    Connection conn = 
      getDBTransaction().createStatement(1).getConnection();

    StructDescriptor rowStructDesc = 
      StructDescriptor.createDescriptor(dbRowTypeName, conn);
    StructDescriptor indStructDesc = 
      StructDescriptor.createDescriptor(dbIndTypeName, conn);

    oracle.sql.STRUCT rowStruct = 
      new STRUCT(rowStructDesc, conn, preRowArray);
    oracle.sql.STRUCT indStruct = 
      new STRUCT(indStructDesc, conn, preIndArray);

    OracleCallableStatement statement = 
      (OracleCallableStatement)conn.prepareCall(dbStatement);

    statement.setSTRUCT(1, rowStruct);
    statement.setSTRUCT(2, indStruct);

    statement.registerOutParameter(1, OracleTypes.STRUCT, 
      dbRowTypeName);
    statement.registerOutParameter(2, OracleTypes.STRUCT, 
      dbIndTypeName);

    statement.executeUpdate();

    rowStruct = statement.getSTRUCT(1);
    indStruct = statement.getSTRUCT(2);

    postRowArray = rowStruct.getAttributes();
    postIndArray = indStruct.getAttributes();

    for (int i = 0, size = attributes.length; i < size; i++) {

      Boolean postInd = 
        ((BigDecimal)postIndArray[i]).toString().equals("1");
      if (preIndArray[i] != postInd) {
        String attributeType = 
           getStructureDef().getAttributeDef(
              attributes[i]).getJavaType().getName();
        // This code is incomplete and does not handle all the 
        // datatypes that could come back from the database
        if (postRowArray[i] == null)
          setAttribute(attributes[i], null);
        else if (attributeType.equals("oracle.jbo.domain.Date"))
          setAttribute(attributes[i]
                      ,new Date((Timestamp)postRowArray[i]));
        else if (attributeType.equals( 
                      "oracle.jbo.domain.Number"))
          setAttribute(attributes[i]
                      ,new Number((BigDecimal)postRowArray[i]));
        else if (attributeType.equals("java.lang.String"))
          setAttribute(attributes[i]
                      ,(String)postRowArray[i]);
        else
          throw new JboException(
                     "PlsqlEntityImpl.insertUpdate() datatype?");
      }
    }
  } catch (SQLException ex) {
    throw new JboException(ex);
  }
}

Again, for clarity this article hasn't modularized this function, but you can easily separate parts of the insertUpdate() function into reusable functions.

The insertUpdate() function takes the following parameters:

  • int[] attributes: an array containing the index positions of each of the attributes in the EO to write to the database. This will be generated via a final static int attributes constant in the original EntityImpl class, which I'll describe soon.

  • String dbRowTypeName: the name of the database row object type, such as CG$EVENTS_CG$ROW_TYPE

  • String dbIndTypeName: the name of the database indicator object type, such as CG$EVENTS_CG$IND_TYPE

  • String dbStatement: the actual PL/SQL call to make. For example,
                    BEGIN events_pkg.ins(?, ?, TRUE); END;
                   
    
    or
                    BEGIN events_pkg.upd(?, ?, TRUE); END;
                   
    
The insertUpdate() function undertakes these distinct operations:
  • Lines 08�18: for the current EO, assembles an array preRowArray of all the EO attribute values, an array of EO attribute change indicators in preIndArray, and two blank arrays to hold the results returned from the upcoming JDBC call

  • Lines 20-31: with the attribute and change indicator arrays in hand, two JDBC STRUCT values are created, associated with their database object type counterparts, and populated with the assembled arrays

  • Lines 33-34: prepares a JDBC call to the PL/SQL package and function specified in the dbStatement

  • Lines 36-42: writes the two STRUCT values to the PL/SQL function call and prepares to retrieve two OUT parameters

  • Line 44: calls the database to execute the PL/SQL function call, passing in the STRUCT values and retrieving the OUT parameters for later use

  • Lines 46-50: both the database insert and update routines return the resulting row values and indicators to flag which columns have changed. These lines deal with the collection of the results from the JDBC prepareCall() statement, populating the postRowArray and postIndArray for you to inspect.

  • Lines 52-74: iterating through the postRowArray, for each attribute where the corresponding postIndArray indicates a change, you grab the new attribute and write it to the EO attribute. Note how line 61 and onward in this example deal only with a limited number of datatypes. A complete solution would need to cover all the datatypes coming back from the database, including LOBs for example.

Writing a JDBC Delete Function

The method to handle calling the delete through JDBC is similar to the insertUpdate() function:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public void delete(int[] pkAttributes
                  ,String dbPkTypeName
                  ,String dbStatement) {

  try {

    Object preRowArray[] = new Object[pkAttributes.length + 1];

    for (int i = 0, size = pkAttributes.length; i < size; i++)
      preRowArray[i] = getAttribute(i);

    Connection conn = 
      getDBTransaction().createStatement(1).getConnection();

    StructDescriptor rowStructDesc = 
      StructDescriptor.createDescriptor(dbPkTypeName, conn);

    oracle.sql.STRUCT rowStruct = 
      new STRUCT(rowStructDesc, conn, preRowArray);

    OracleCallableStatement statement = 
      (OracleCallableStatement)conn.prepareCall(dbStatement);

    statement.setSTRUCT(1, rowStruct);
    statement.registerOutParameter(1, OracleTypes.STRUCT
                                  ,dbPkTypeName);
    statement.executeUpdate();

  } catch (SQLException ex) {
    throw new JboException(ex);
  }
}

The delete() function takes the following parameters:

  • int[] pkAttributes: an array containing the index positions of each of the primary key attributes in the EO to write to the database

  • String dbPkTypeName: the name of the database pk object type, such as CG$EVENTS_CG$PK_TYPE

  • String dbStatement: the actual delete PL/SQL call to make. For example,
                    BEGIN events_pkg.del(?, ?, TRUE); END;
    

As you can see, the delete() function is very similar to the insertUpdate() function, although it doesn't need to deal with any returned data.

Writing a JDBC Lock Function

The method to handle locking is similar to the previous functions:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
public void lock(int[] attributes
                ,String dbRowTypeName
                ,String dbIndTypeName
                ,String dbStatement) {

  OracleCallableStatement statement;

  try {
    Object preRowArray[]  = new Object[attributes.length + 1];
    Boolean preIndArray[] = new Boolean[attributes.length];

    // Note call to getPostedAttribute rather than getAttribute
    for (int i = 0, size = attributes.length; i < size; i++)
      preRowArray[i] = getPostedAttribute(i); 

    for (int i = 0, size = attributes.length; i < size; i++)
      preIndArray[i] = isAttributeChanged(i);

    Connection conn = 
      getDBTransaction().createStatement(1).getConnection();

    StructDescriptor rowStructDesc = 
      StructDescriptor.createDescriptor(dbRowTypeName, conn);
    StructDescriptor indStructDesc = 
      StructDescriptor.createDescriptor(dbIndTypeName, conn);

    oracle.sql.STRUCT rowStruct = 
      new STRUCT(rowStructDesc, conn, preRowArray);
    oracle.sql.STRUCT indStruct = 
      new STRUCT(indStructDesc, conn, preIndArray);

    statement = 
      (OracleCallableStatement)conn.prepareCall(dbStatement);

    statement.setSTRUCT(1, rowStruct);
    statement.setSTRUCT(2, indStruct);

    statement.registerOutParameter(1, OracleTypes.STRUCT
                                  ,dbRowTypeName);
    statement.registerOutParameter(2, OracleTypes.STRUCT
                                  ,dbIndTypeName);

    statement.executeUpdate();
  } catch (SQLException e) {
    if (e.getMessage().indexOf("TAPI--54") > 0)
      throw new RowInconsistentException(getKey());
    else
      throw new JboException(e);
  }
}

The lock() function parameters are nearly the same as that for the insertUpdate() method but for one key difference. Rather than retrieving the current EO attribute values via a call to getAttributes(), on line 14 the function calls getPostedAttributes() instead to retrieve the attribute values before the current DML operation. This is because the Table API lck method requires the previous values of the attributes in order to check if the database record has changed, by comparing the old values against what is currently in the database. If the values have changed, it indicates that another user has updated the record. If the values haven't changed, the routine will take a lock out on the selected row.

Also on lines 45-49, within the SQLException you need to check if the returned database exception message includes the string "TAPI--54". This message is specifically flagged by the Table API lck method, indicating that another user has taken a lock out on the record. In this case, you flag this error by raising the JBO exception RowInconsistentException, which is the normal ADF BC mechanism for flagging row lock errors.

Overriding the EO doDML() Method to Call Your JDBC Functions

On completing the method to make the insert, update, delete, and lock JDBC calls, you can now complete your doDML() method for all DML operations with the following code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
static final int attributes[] = 
  new int[] { EVENTNO, ORGID, DESCRIPTION, CONTACTNAME, 
              STARTDATE, ENDDATE, COMMENTS };
static final int pkAttributes[] = new int[] { EVENTNO };

static final String rowType = "CG$EVENTS_CG$ROW_TYPE";
static final String indType = "CG$EVENTS_CG$IND_TYPE";
static final String pkType  = "CG$EVENTS_CG$PK_TYPE";

protected void doDML(int operation, TransactionEvent e) {

  try {
    if (operation == DML_INSERT) {
      super.insertUpdate(attributes, rowType, indType,
                  "BEGIN events_pkg.ins(?, ?, TRUE); END;");
    } else if (operation == DML_UPDATE) {
      super.insertUpdate(attributes, rowType, indType,
                  "BEGIN events_pkg.upd(?, ?, TRUE); END;");
    } else if (operation == DML_DELETE) {
      super.delete(pkAttributes, pkType, 
                  "BEGIN events_pkg.del(?, ?, TRUE); END;");
    } else // unlikely but you never know
      super.doDML(operation, e);
  } catch (SQLException ex) {
    throw new JboException(ex);
  }
}

This code undertakes the following steps:

  • Lines 01-04: within the EntityImpl, you create an array of indexes for the individual EO attributes as well as an array of indexes of primary key attributes in the EO, to pass to your JDBC methods and ultimately to the Table API methods.

  • Lines 06-08: static final Strings containing your Oracle Object Type names.

  • Lines 13-20: for each operation type, a call to your JDBC methods within the superclass PlsqlEntityImpl, passing in the relevant arrays, Oracle Object Type names, and the PL/SQL package functions to call.

Overriding the EO lock() Method to Call Your JDBC Function

Finally you also need to override the EO lock() method to call your equivalent Table API cg$EVENTS.lck method indirectly. As you can see, this code is even easier than the doDML() method:

01
02
03
04
public void lock() {
  super.lock(attributes, rowType, indType,
               "BEGIN events_pkg.lck(?, ?); END;");
}

Conclusion

Once you have completed the above steps, your Oracle JDeveloper code will run with the Oracle Designer database Table API.

Legacy code can prove a challenge to integrate into most new systems. Yet Oracle Designer's Table APIs prove little challenge for the mature ADF within Oracle JDeveloper, due to its rich support for JDBC database calls and clear framework chokepoint methods, which can be overridden to implement custom solutions. This should give any site worried about adopting new technologies while integrating with the legacy database artifacts reassurance that both past and future development investments will be secure.


Chris Muir [ http://one-size-doesnt-fit-all.blogspot.com] is an Oracle ACE Director (Oracle Fusion Middleware) and a senior consultant and Oracle trainer for SAGE Computing Services in Australia. With more than 10 years working in traditional Oracle development, he has more recently earned battle scars working with, training with, and promoting Oracle JDeveloper and ADF.