I’ve been working on a flexible table API generator for Oracle Databases since several months. A TAPI generator doesn’t sound like a real innovation. But this one contains some features you probably have not seen before in TAPI generator and hopefully will like it as much as I do.

In this post I will not explain the feature set thoroughly. Instead I will more or less focus on one of my favourite features.

Four models

The generators knows the following four data models.


If your table is based on one of these four models you may

  1. simply generate a table API for it or
  2. switch to another model and optionally generate a table API as well.

Option 2) is extraordinary, since it will preserve the existing data. E.g. it will preserve the content of the flashback data archive when you switch your model from uni-temporal transaction-time to a bi-temporal model even if the flashback archive tables need to be moved to another table. Furthermore it will keep the interface for the latest table the same. No application change required. Everything with just a few mouse clicks. If this sounds interesting for you, then have a look at https://github.com/oddgen/bitemp/blob/main/README.md where the concept is briefly explained or join me my session “oddgen – Bi-temporal Table API in Action” at the More than just – Performance Days 2016. Remote participation is still possible.

Option 1) is what we had since years. It was part of Oracle Designer, it’s part of SQL Developer in a simplified way and there are a some more or less simple table API generators around. So no big deal. However, when you choose option 1), there is one part which is really cool. The hook API package concept.

The Hook API

The problem with a lot of table API solution is, that there is typically no developer friendly way to include the business logic. I’ve seen the following:

  • Manual changes of the generated code, which is for various reason not a good solution.
  • External hooks, e.g. in XML files, in INI files, relational tables, etc. and merged at generation time into the final code. Oracle Designer worked that way.
  • Code which is dynamically executed by the generator at runtime, e.g. code snippets are stored in an pre-defined way in relational tables.

But what I’ve never seen, was business logic implemented in manually crafted PL/SQL packages, separated from the PL/SQL generated code. That’s strange, because this is a common practice in Java based projects.

In Java you typically define an interface for that and configure at runtime the right implementation. In PL/SQL we may do that similarly. A PL/SQL specification is an interface definition. That just one implementation may exist for an interface is not a limiting factor in this case.

Bitemp Remodeler generates the following hook API package specification for the famous EMP table in schema SCOTT:

The generated table API calls before an INSERT the pre_ins procedure and after the INSERT the post_ins procedures. For DELETE and UPDATE this works the same way. On the highlighted line 5 and 6 two interested things are pointed out. The body is not generated and the body does not need to be implemented since the API ignores errors caused by a missing PL/SQL hook package body.

Technically this is solved as follows in the API package body:

Now you may ask what the performance impact of these e_hook_body_missing exceptions are. I’ve done a small test and called a procedure without and with implemented body 1 million times. The overhead of the missing body exception is about 7 microseconds per call. Here’s the test output from SQL Developer, the  relevant lines 51 and 89 are highlighted.

It make sense to provide a body with a NULL implementation to avoid the small overhead of handling the missing body exception.

Nonetheless, the way how the business logic is separated from the generated code, is one of the many things I like about Bitemp Remodeler.

Download Bitemp Remodeler from the Download section on my blog or install it directly via the SQL Developer update site http://update.oddgen.org/

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.