| Developer: Java
Integrating the Oracle Designer Legacy Table API with
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."
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:
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.
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.)
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:
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:
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:
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:
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:
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:
Then for the EOs that require the JDBC logic, you can extend them to make use of this new superclass:
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:
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:
Writing a JDBC Delete Function
The method to handle calling the delete through JDBC is similar to the insertUpdate() function:
The delete() function takes the following parameters:
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:
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:
This code undertakes the following steps:
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:
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.