I was recently asked if I could put together a demo application to show how the use of tapiGen makes it easier to build some simple pages in ApEx. I thought about it for a while but ultimately decided it was not a very easy task, in fact, it's currently more difficult! No one can really argue against how simple the dev team at Oracle has made building simple forms. The trade off, however, is control.
When I first created tapiGen, I was hoping that Oracle had an API through which I could automate the creation of pages and content, much like they've done when you use the wizards to create pages and regions. The idea was to provide an alternate means thru which a form could be created that would use tapiGen for processing over the Automated Row Fetch and Automatic Row Processing. However, no such API exists and it's unclear if one ever will. (the most I can do in tapiGen is to generate the code which can then be copied and pasted into ApEx processes - I'll do this in version .3).
There are, however, some compelling reasons for using tapiGen in ApEx:
- Automatic Row processes are only useful for simple forms that feed off of one table. If you want to work with multiple tales on the same page you'll need to create code to perform the fetches and DML. tapiGen will help keep this "custom" code to a minimum while providing other functionality.
- Using the Automatic Row processes requires up to 4 additional page items for the audit columns: PXX_CREATED_BY, PXX_CREATED_ON, PXX_MODIFIED_BY, PXX_MODIFIED_ON. These page items then need to be properly configured so that 1) they are not shown/made editable to the end user and 2) their values are properly set on submittal to update the columns as needed. In JDeveloper this can be automated, so I figured why not in ApEx. With tapiGen audit columns are handled for you so the items are not necessary.
- There is only one "success message" displayed for Automatic Row Processing (DML), which can handle three types of operations, such as "Operation Successful". I wanted to display more contextually accurate messages such as "Contact Created", "Contact Modified", and "Contact Deleted". This requires three separate page processes, one for each DML operation, but using tapiGen keeps the amount of code required to a minimum.
- Errors are not "handled" using Automatic Row processes, only displayed to the end user. If a row fetch fails I want to know about it. tapiGen provides a consistent method thru which errors are written to a table called ERRORS. The row inserted includes a lot of information such as the application alias, application id, page id, app user, and time during which the error occurred along with other information. This can be very useful when trying to figure out what went wrong. If you want to take it to the next level, a trigger can be placed on the errors table with the necessary code to email the group/person responsible for maintaining the application that includes all of the aforementioned information. Proactive maintenance, oh yeah! ;)
The truth is that table APIs are not ApEx specific. Many corporations that have never heard of ApEx use TAPIs to help ensure that PL/SQL code is written in a consistent and efficient manner that makes maintenance much easier.
Steve Feuerstein first got me into TAPIs in his book PL/SQL Best Practices. In fact he offers a TAPI generator as a part of the Quest CodeGen Utility that I'm still working to catch up to in some ways. But I wanted a TAPI generator that made working with ApEx easier, included some additional functionality for working in web based environments, and didn't require a JRE to work (so it could be used in XE). Also, Steve is not currently/actively maintaining the generator, so new DB features such as the function result cache in 11g will not be taken advantage of, at least not until he decides to do it.
tapiGen is open source and I don't plan to stop updating it (of course I never planed on making it either). The fact is that I use it all the time so I have to keep making it better. For example, the next version will make DML with collections much easier - something I'm really looking forward to! I would love to have others helping to maintain the code although I don't know if that'll ever happen. I'd also love any feed back as to how it could be made better. Hopefully by the time version 1 (production/stable) is released the project will help meet a lot of peoples needs.
Having said all this, I feel like I'm coping out... I'll tell you what, when version .3 is released, provided there's some interest, I'll put out an example application that demonstrates the use of tapiGen in ApEx.