Table API [TAPI] vs Transactional API [XAPI]
A series of SQL calls should be treated as a single Transaction and given its own API.
An example of a XAPI would be a series of APIs for managing Documents who's version/revision numbers should be "gap-free sequential numbers".
The complexity of ensuring "gap-free sequential numbers" requires a series of SQL calls that can't be done in a TRIGGER.
This series of SQL calls must be done as a single transaction.
As such, a XAPI would need to be build to handle "gap-free sequential numbering".
Some of the API commands could be:
- newDocument( .... ) -- starts document as v1.0
- newVersion( ... ) -- document goes from v1.x to v2.0
- newRevision( ... ) -- document goes from v3.1 to v3.2
{I might have version/revision mixed}
I see Table APIs [TAPI] as nothing more than a XAPI that does one thing: CRUD operations on a single table.
This type of package is exatly what things such as "Automatic DML" do.
Because TAPIs serve a single purpose, it is not supposed to be combined with other TAPIs to build a complex workflow.
(eg a series of CRUD operations defined in a single JAVA function).
Instead, that series of TAPI calls should be reclassified as a Transaction. Thus, requiring the developer to build a Transactional API [XAPI]
The Requests
The problem with APEX is that it can't leverage its "easy-to-build" capabilities (eg "create form on table with report") with complex data processes that have been hidden behind PL/SQL Packages (ie TAPIs & XAPIs)
These "APEX Feature Requests" are designed to solve that gap in technology.
The requests can be found here:
Additional Information for the "Use TAPI" request
There are a few of questions on this forum where the requestor is asking how to make APEX do certain "additional" things during APEX supplied DML operations (eg Automatic DML Process, MRU, etc)
Some of those questions could be solved with an additional Oracle technology (eg Identity column, Flashback Data Archive)
Other answers (and questions) revolve around using a TRIGGER.
Most developers would probably comment against the use of TRIGGERs and suggest "Use a TAPI".
However, for new APEX developers, making use of a TAPI is not easy.
Although a TAPI can be created in SQL Workshop, the resulting TAPI can not be used with Automatic DML.
This results in the (practically) uselessness of "Create form on Table [with report]".
Instead, the developer has to create his/her own DIY DML Process thus (seemingly) negating any usefullness that the "Create Table API [TAPI]" functionality provides.
I believe APEX should be modified so that "Automatic DML" (or "Automatic DML via TAPI") can use the resulting code from "Create Table API [TAPI]"
Because the format of the resulting code from "Create Table API [TAPI]" is constant, the only thing that would be needed for "Automatic DML [via TAPI]" would be the precise name of the package (ie SCHEMA + PACKAGE NAME)
Note : The format of the resulting code and the names (and purpose) of each functions/procedure should be documented so that other template based code generation utilities (eg ) can be used to create TAPIs.
Additional Information for the "Use XAPI" request
XAPIs are extremly important.
However, many APIs could exist in a single package.
APEX already has "Create form on procedure" wizard. However, this becomes tedious when dealing with a sizable amount of procedures.
Instead, I am suggesting a "Create forms for Transactional API [XAPI]"
One result of that wizard would be a series of navigation entries similar to:
- {parent location}
- tab name that defaults to package name
Each API entry would point to a separate page.
Each page should look and feel like it was created with the "Create form on procedure" wizard
Naturally, all names (and "parent" location of the first one) can be defined as part of the "Create forms for Transactional API [XAPI]" wizard.
Of course, this means that the key to building this wizard is to create the UI so that you are defining things in bulk.
Including, but not limited to, picking which Procedures to actually use and the Security settings.
Please feel free to comment.
Thanks,
MK