Sample TAPI APEX Application

If you attended my presentation at AUSOUG Perth earlier this month, or if you’ve had a peek at the slides, you may be interested in a more concrete demonstration of the ideas presented. So if you’d like to install and play with a sample application that includes a TAPI generator, feel free to download this (EDIT: updated, see below).

Disclaimer: this is provided for information (and entertainment) purposes only.

Oracle Application Express 4.2.2 or later

Read the README file for installation instructions.


If you’re only interested in the schema-level TAPI and not the APEX application, the zip file includes the DDL script that you can run directly in a schema without requiring Apex.

EDIT (18/11/2014): updated sample code to do the right thing in WHEN OTHERS triggers.

EDIT (20/11/2014): updated sample code with a further example for a FK to a table, which doesn’t use a surrogate key. Also added an exception handler to the Apex application.

EDIT (2/12/2014): added Grid Edit for event types, as an example of how a tabular form might work with a TAPI; added deployment package; moved code to Bitbucket.

EDIT (16/02/2014): upgraded to APEX 5, plus numerous improvements – refer to:

More updates and improvements will be added in the future – watch this space.

Help for your keyboard users
Why Kyte Tolerates Poor Questions


  1. Hello Jeff,
    I am not sure if I used TAPI right before. The main problem that I have experienced is that whenever I needed to add a new column to the table, I have to re-create the Table Package again, then add the item of that column; then modify the Process in APEX. So, I think just typing our own code on Apex Process is much easier to maintain ?

    • Hi Fateh, thanks for your comment.

      The idea behind generating the TAPI is to do *most* of the typing for you while the data model is being designed and tested. After you start customising each TAPI, however, it doesn’t make sense to re-generate the TAPI (otherwise you’ll have to redo all the customisations).

      The idea is that adding a new column to a table is *relatively* uncommon, so having to add it to the TAPI is not normally a big deal. Whether you use a TAPI or not however doesn’t make much difference – you will still probably add the field to the Apex report and form, and adding a bit of extra code to the package is not much more work.

      If the code is in your Apex Process, however, it might be referred to in multiple Page processes if the table is used in more than one page in your Apex application – so when the table changes, you might have to change it in every Process that refers to it. If this code is defined once in the TAPI, you only have one place you need to update.

      Your mileage may vary – and every environment has different requirements – meaning you have to think about what will work best in your situation.

  2. Hi Jeff,

    I’ve tended towards using a TAPI approach in my work but never had the opportunity to “build from scratch” and design something from top to bottom with this approach. Now I am embarking on my own creation (as a side business, hopefully to become my full time work) and I am building the foundations of my product.
    I’ve read through the various links on TAPI vs XAPI. I respect both Tom Kyte and Steven Feuerstein immensely. I’m curious as to what your thoughts are on this debate, since you have obviously committed to TAPI. Having only dabbled with it myself I only have an uninformed opinion, but it seems to me that it is not an either/or thing. Tom argues that the “slow by slow” approach, meaning row by row processing, is what a TAPI approach leads to even when much faster bulk processing could be performed. However, I see no reason that TAPI should always have to be used. In the case of screens we are almost always working on a row by row basis and therefore it is highly appropriate. When it’s not appropriate, we should develop a XAPI for particular use cases. I’m honestly not sure what the disagreement really is. I’m sure Steven would argue the same thing, and I think has if I’ve read his comment correctly. Yet Tom says “never TAPI under any circumstances”. Based on your experience using TAPI, what are your thoughts?

    • Hi Rodney,

      Thanks for your comments.

      1. I am not 100% committed to TAPI – I have used it on a number of projects so far, and it has proved very advantageous. It does have some downsides as well, though, to be aware of. I’ve also avoided it for some smaller projects where I could see that the requirements and data model were very simple, and development speed was important (i.e. I had literally hours to build some reports and simple edit forms).

      2. The TAPI approach leads to “slow by slow” processing when developers feel unable (or are unwilling) to add extra routines for bulk DML. This is something that my XAPI/TAPI approach deals with head-on – by making it relatively easy to add bulk operations as API calls. This is the reason some people do not consider my approach to be “true TAPI” – and this is not something that bothers me at all 🙂

      So far, my experience of using XAPI+TAPI has yielded code that makes it easy to read and maintain complex logic, and makes it easy to evolve the data model without breaking everything.

      As with any such technique, YMMV.

  3. Steve Trujillo
    16 May 2022 - 6:04 pm

    Hi Jeff,
    Considering the advancement in the features and functionality of the Interactive Grid and the transition to the use of JavaScript widgets, do you still feel (as of APEX 21.2) that the use of a TAPI is the best choice when using an IG? As well as worth the effort involved in producing and maintaining a TAPI (for use with an IG)?

    • Jeffrey Kemp
      16 May 2022 - 6:10 pm

      My opinions on the benefits and disadvantages of *my* TAPI approach haven’t changed, even with recent advancements in the interactive grid. I’m not sure that changes to the client side code such as JS widgets makes any difference to the discussion anyway.

      More to the point, it was never my opinion (and still isn’t) that the use of a TAPI is the “best” choice – mainly because I don’t think there is a “best” choice.

      That said, I’m still using my TAPI approach and have found it works well with the IG. However, for each new project I still make a conscious choice about whether to use this approach or not, depending on the nature of the project – mainly the expected complexity of the business rules and who will be developing and maintaining it.

Leave a Reply

Your email address will not be published / Required fields are marked *