TAPI Generator MkII

The last few weeks I’ve made quite a few improvements to my TAPI generator which I thought I’d share. I’ve also added an Apex API generator which generates code suitable for interfacing between simple Apex applications and my TAPIs. This reduces the volume of PL/SQL required within Apex to a bare minimum.

  • Templates are now defined in a package spec, so they are easier to edit in a tool with syntax highlighting (more or less)
  • Most dynamic code generation is defined within the template using a simple syntax
  • Makes inferences from schema metadata to generate code, including some guesses based on object and column naming conventions.
  • Ability to insert table-specific code into the template so that it is retained after re-generating the TAPI.
  • As much as possible, allow generated code to follow my preferred code formatting rules as possible.
  • The Table API (“TAPI”) package defines two record types; one (rowtype) is based on the table, the other (rvtype) uses mostly VARCHAR2(4000) columns in order to hold a pre-validated record.

Assumptions

My generator makes the following assumptions:

  • All tables and columns are named non-case-sensitive, i.e. no double-quote delimiters required.
  • (APEX API) All columns are max 26 chars long (in order to accommodate the Apex “P99_…” naming convention)
  • (APEX API) Table has no more than 1 CLOB, 1 BLOB and 1 XMLTYPE column (in order to support conversion to/from Apex collections)

If any of the above do not hold true, the TAPI will probably need to be manually adjusted to work. All TAPIs generated should be reviewed prior to use anyway.

Example

For example, given the following schema:

CREATE TABLE emps
  (emp_id       NUMBER NOT NULL
  ,name         VARCHAR2(100 CHAR) NOT NULL
  ,emp_type     VARCHAR2(20 CHAR) DEFAULT 'SALARIED' NOT NULL
  ,start_date   DATE NOT NULL
  ,end_date     DATE
  ,dummy_ts     TIMESTAMP(6)
  ,dummy_tsz    TIMESTAMP(6) WITH TIME ZONE
  ,life_history CLOB
  ,CONSTRAINT emps_pk PRIMARY KEY ( emp_id )
  ,CONSTRAINT emps_name_uk UNIQUE ( name )
  ,CONSTRAINT emp_type_ck
     CHECK ( emp_type IN ('SALARIED','CONTRACTOR')
  );
CREATE SEQUENCE emp_id_seq;

I can run this:

BEGIN GENERATE.tapi('emps'); END;
/

This generates the following package (I’ve removed large portions, the full version is linked below):

create or replace PACKAGE EMPS$TAPI AS
/**********************************************************
 Table API for emps
 10-FEB-2016 - Generated by SAMPLE
**********************************************************/ 

SUBTYPE rowtype IS emps%ROWTYPE; 

TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER; 

TYPE rvtype IS RECORD
  (emp_id       emps.emp_id%TYPE
  ,name         VARCHAR2(4000)
  ,emp_type     VARCHAR2(4000)
  ,start_date   VARCHAR2(4000)
  ,end_date     VARCHAR2(4000)
  ,dummy_ts     VARCHAR2(4000)
  ,dummy_tsz    VARCHAR2(4000)
  ,life_history emps.life_history%TYPE
  ,version_id   emps.version_id%TYPE
  ); 

TYPE rvarraytype IS TABLE OF rvtype INDEX BY BINARY_INTEGER; 

-- validate the row (returns an error message if invalid)
FUNCTION val (rv IN rvtype) RETURN VARCHAR2; 

-- insert a row
FUNCTION ins (rv IN rvtype) RETURN rowtype; 

-- insert multiple rows, array may be sparse
-- returns no. records inserted
FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER; 

$if false $then/*need to grant DBMS_CRYPTO*/
-- generate a hash for the record
FUNCTION hash (r IN rowtype) RETURN VARCHAR2;
$end

...

END EMPS$TAPI;
create or replace PACKAGE BODY EMPS$TAPI AS
/**********************************************************
 Table API for emps
 10-FEB-2016 - Generated by SAMPLE
**********************************************************/ 

FUNCTION val (rv IN rvtype) RETURN VARCHAR2 IS
  -- Validates the record but without reference to any other rows or tables
  -- (i.e. avoid any queries in here).
  -- Unique and referential integrity should be validated via suitable db
  -- constraints (violations will be raised when the ins/upd/del is attempted).
  -- Complex cross-record validations should usually be performed by a XAPI
  -- prior to the call to the TAPI.
BEGIN
  log_start('val'); 

  UTIL.val_not_null (val => rv.name, column_name => 'NAME');
  UTIL.val_not_null (val => rv.emp_type, column_name => 'EMP_TYPE');
  UTIL.val_not_null (val => rv.start_date, column_name => 'START_DATE'); 

  UTIL.val_max_len (val => rv.name, len => 100, column_name => 'NAME');
  UTIL.val_max_len (val => rv.emp_type, len => 20, column_name => 'EMP_TYPE');
  UTIL.val_date (val => rv.start_date, column_name => 'START_DATE');
  UTIL.val_date (val => rv.end_date, column_name => 'END_DATE');
  UTIL.val_timestamp (val => rv.dummy_ts, column_name => 'DUMMY_TS');
  UTIL.val_timestamp_tz (val => rv.dummy_tsz, column_name => 'DUMMY_TSZ'); 

  --TODO: add more validations if necessary 

  log_end;
  RETURN UTIL.first_error;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val; 

FUNCTION ins (rv IN rvtype) RETURN rowtype IS
  r         rowtype;
  error_msg VARCHAR2(32767);
BEGIN
  log_start('ins'); 

  error_msg := val (rv => rv); 

  IF error_msg IS NOT NULL THEN
    raise_error(error_msg);
  END IF; 

  INSERT INTO emps
        (emp_id
        ,name
        ,emp_type
        ,start_date
        ,end_date
        ,dummy_ts
        ,dummy_tsz
        ,life_history)
  VALUES(emp_id_seq.NEXTVAL
        ,rv.name
        ,rv.emp_type
        ,UTIL.date_val(rv.start_date)
        ,UTIL.date_val(rv.end_date)
        ,UTIL.timestamp_val(rv.dummy_ts)
        ,UTIL.timestamp_tz_val(rv.dummy_tsz)
        ,rv.life_history)
  RETURNING
         emp_id
        ,name
        ,emp_type
        ,start_date
        ,end_date
        ,dummy_ts
        ,dummy_tsz
        ,life_history
        ,created_by
        ,created_dt
        ,last_updated_by
        ,last_updated_dt
        ,version_id
  INTO   r.emp_id
        ,r.name
        ,r.emp_type
        ,r.start_date
        ,r.end_date
        ,r.dummy_ts
        ,r.dummy_tsz
        ,r.life_history
        ,r.created_by
        ,r.created_dt
        ,r.last_updated_by
        ,r.last_updated_dt
        ,r.version_id; 

  msg('INSERT emps: ' || SQL%ROWCOUNT); 

  log_end;
  RETURN r;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index;
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END ins; 

FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER IS
  rowcount NUMBER;
BEGIN
  log_start('bulk_ins'); 

  bulk_val(arr); 

  FORALL i IN INDICES OF arr
    INSERT INTO emps
           (emp_id
           ,name
           ,emp_type
           ,start_date
           ,end_date
           ,dummy_ts
           ,dummy_tsz
           ,life_history)
    VALUES (emp_id_seq.NEXTVAL
           ,arr(i).name
           ,arr(i).emp_type
           ,UTIL.date_val(arr(i).start_date)
           ,UTIL.date_val(arr(i).end_date)
           ,UTIL.timestamp_val(arr(i).dummy_ts)
           ,UTIL.timestamp_tz_val(arr(i).dummy_tsz)
           ,arr(i).life_history); 

  rowcount := SQL%ROWCOUNT; 

  msg('INSERT emps: ' || rowcount); 

  log_end('rowcount=' || rowcount);
  RETURN rowcount;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index;
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END bulk_ins; 

$if false $then/*need to grant DBMS_CRYPTO*/
FUNCTION hash (r IN rowtype) RETURN VARCHAR2 IS
  sep    CONSTANT VARCHAR2(1) := '|';
  digest CLOB;
  ret    RAW(2000);
BEGIN
  log_start('hash'); 

  digest := digest || sep || r.emp_id;
  digest := digest || sep || r.name;
  digest := digest || sep || r.emp_type;
  digest := digest || sep || TO_CHAR(r.start_date, UTIL.DATE_FORMAT);
  digest := digest || sep || TO_CHAR(r.end_date, UTIL.DATE_FORMAT);
  digest := digest || sep || TO_CHAR(r.dummy_ts, UTIL.TIMESTAMP_FORMAT);
  digest := digest || sep || TO_CHAR(r.dummy_tsz, UTIL.TIMESTAMP_TZ_FORMAT); 

  ret := DBMS_CRYPTO.hash(digest, DBMS_CRYPTO.hash_sh1); 

  log_end(ret);
  RETURN ret;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END hash;
$end

...

END EMPS$TAPI;

Example Template

The following is a template which provides the source used to generate the above TAPI. The syntax may look very strange, but if you read on you can read my explanation of the syntax below. My goal was not to invent an all-singing all-dancing general-purpose syntax for code generation – but to have “just enough” expressive power to generate the kind of code I require.

create or replace PACKAGE TEMPLATES AS
$if false $then
<%TEMPLATE TAPI_PACKAGE_SPEC>
CREATE OR REPLACE PACKAGE #TAPI# AS
/**********************************************************
 Table API for #table#
 #SYSDATE# - Generated by #USER#
**********************************************************/
<%IF EVENTS>
/*Repeat Types*/
DAILY    CONSTANT VARCHAR2(100) := 'DAILY';
WEEKLY   CONSTANT VARCHAR2(100) := 'WEEKLY';
MONTHLY  CONSTANT VARCHAR2(100) := 'MONTHLY';
ANNUALLY CONSTANT VARCHAR2(100) := 'ANNUALLY';
<%END IF>
SUBTYPE rowtype IS #table#%ROWTYPE;

TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER;

TYPE rvtype IS RECORD
  (<%COLUMNS EXCLUDING AUDIT INCLUDING ROWID,EVENTS.REPEAT_IND>
   #col#--- VARCHAR2(4000)~
   #col#--- #table#.#col#%TYPE{ID}~
   #col#--- #table#.#col#%TYPE{LOB}~
   #col#--- VARCHAR2(20){ROWID}~
   #col#--- VARCHAR2(1){EVENTS.REPEAT_IND}~
  ,<%END>
  );

TYPE rvarraytype IS TABLE OF rvtype INDEX BY BINARY_INTEGER;

-- validate the row (returns an error message if invalid)
FUNCTION val (rv IN rvtype) RETURN VARCHAR2;

-- insert a row
FUNCTION ins (rv IN rvtype) RETURN rowtype;

-- insert multiple rows, array may be sparse; returns no. records inserted
FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER;

...

<%IF DBMS_CRYPTO><%ELSE>$if false $then/*need to grant DBMS_CRYPTO*/<%END IF>
-- generate a hash for the record
FUNCTION hash (r IN rowtype) RETURN VARCHAR2;
<%IF DBMS_CRYPTO><%ELSE>$end<%END IF>

END #TAPI#;
<%END TEMPLATE>

<%TEMPLATE TAPI_PACKAGE_BODY>
CREATE OR REPLACE PACKAGE BODY #TAPI# AS
/**********************************************************
 Table API for #table#
 #SYSDATE# - Generated by #USER#
**********************************************************/

FUNCTION val (rv IN rvtype) RETURN VARCHAR2 IS
  -- Validates the record but without reference to any other rows or tables
  -- (i.e. avoid any queries in here).
  -- Unique and referential integrity should be validated via suitable db
  -- constraints (violations will be raised when the ins/upd/del is attempted).
  -- Complex cross-record validations should usually be performed by a XAPI
  -- prior to the call to the TAPI.
BEGIN
  log_start('val');

  <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY,NULLABLE>
  UTIL.val_not_null (val => rv.#col#, column_name => '#COL#');~
  <%END>
  <%IF EVENTS>
  IF rv.repeat_ind = 'Y' THEN
    UTIL.val_not_null (val => rv.repeat, column_name => 'REPEAT');
    UTIL.val_not_null (val => rv.repeat_interval, column_name => 'REPEAT_INTERVAL');
  END IF;
  <%END IF>
  <%COLUMNS EXCLUDING GENERATED,SURROGATE_KEY,LOBS INCLUDING EVENTS.REPEAT_IND>
  UTIL.val_ind (val => rv.#col#, column_name => '#COL#');{IND}~
  UTIL.val_yn (val => rv.#col#, column_name => '#COL#');{YN}~
  UTIL.val_max_len (val => rv.#col#, len => #MAXLEN#, column_name => '#COL#');{VARCHAR2}~
  UTIL.val_numeric (val => rv.#col#, column_name => '#COL#');{NUMBER}~
  UTIL.val_date (val => rv.#col#, column_name => '#COL#');{DATE}~
  UTIL.val_datetime (val => rv.#col#, column_name => '#COL#');{DATETIME}~
  UTIL.val_timestamp (val => rv.#col#, column_name => '#COL#');{TIMESTAMP}~
  UTIL.val_timestamp_tz (val => rv.#col#, column_name => '#COL#');{TIMESTAMP_TZ}~
  UTIL.val_integer (val => rv.#col#, range_low => 1, column_name => '#COL#');{EVENTS.REPEAT_INTERVAL}~
  UTIL.val_domain
    (val          => rv.#col#
    ,valid_values => t_str_array(DAILY, WEEKLY, MONTHLY, ANNUALLY)
    ,column_name  => '#COL#');{EVENTS.REPEAT}~
  ~
  <%END>
  <%IF EVENTS>
  UTIL.val_datetime_range
    (start_dt => rv.start_dt
    ,end_dt   => rv.end_dt
    ,label    => 'Event Date/Time Range');
  <%END IF>
  <%IF EVENT_TYPES>
  UTIL.val_cond
    (cond        => rv.event_type = UPPER(rv.event_type)
    ,msg         => 'Event Type Code must be all uppercase'
    ,column_name => 'EVENT_TYPE');
  UTIL.val_cond
    (cond        => rv.event_type = TRANSLATE(rv.event_type,'X -:','X___')
    ,msg         => 'Event Type Code cannot include spaces, dashes (-) or colons (:)'
    ,column_name => 'EVENT_TYPE');
  UTIL.val_date_range
    (start_date => rv.start_date
    ,end_date   => rv.end_date
    ,label      => 'Event Types Date Range');
  <%END IF>
  --TODO: add more validations if necessary

  log_end;
  RETURN UTIL.first_error;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val;

FUNCTION ins (rv IN rvtype) RETURN rowtype IS
  r         rowtype;
  error_msg VARCHAR2(32767);
BEGIN
  log_start('ins');

  error_msg := val (rv => rv);

  IF error_msg IS NOT NULL THEN
    raise_error(error_msg);
  END IF;

  INSERT INTO #table#
        (<%COLUMNS EXCLUDING GENERATED>
        #col#~
        ,<%END>)
  VALUES(<%COLUMNS EXCLUDING GENERATED>
         #seq#.NEXTVAL{SURROGATE_KEY}~
         rv.#col#~
         UTIL.num_val(rv.#col#){NUMBER}~
         UTIL.date_val(rv.#col#){DATE}~
         UTIL.datetime_val(rv.#col#){DATETIME}~
         UTIL.timestamp_val(rv.#col#){TIMESTAMP}~
         UTIL.timestamp_tz_val(rv.#col#){TIMESTAMP_TZ}~
        ,<%END>)
  RETURNING
         <%COLUMNS INCLUDING VIRTUAL>
         #col#~
        ,<%END>
  INTO   <%COLUMNS INCLUDING VIRTUAL>
         r.#col#~
        ,<%END>;

  msg('INSERT #table#: ' || SQL%ROWCOUNT);

  log_end;
  RETURN r;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index;
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END ins;

FUNCTION bulk_ins (arr IN rvarraytype) RETURN NUMBER IS
  rowcount NUMBER;
BEGIN
  log_start('bulk_ins');

  bulk_val(arr);

  FORALL i IN INDICES OF arr
    INSERT INTO #table#
           (<%COLUMNS EXCLUDING GENERATED>
            #col#~
           ,<%END>)
    VALUES (<%COLUMNS EXCLUDING GENERATED>
            #seq#.NEXTVAL{SURROGATE_KEY}~
            arr(i).#col#~
            UTIL.num_val(arr(i).#col#){NUMBER}~
            UTIL.date_val(arr(i).#col#){DATE}~
            UTIL.datetime_val(arr(i).#col#){DATETIME}~
            UTIL.timestamp_val(arr(i).#col#){TIMESTAMP}~
            UTIL.timestamp_tz_val(arr(i).#col#){TIMESTAMP_TZ}~
           ,<%END>);

  rowcount := SQL%ROWCOUNT;

  msg('INSERT #table#: ' || rowcount);

  log_end('rowcount=' || rowcount);
  RETURN rowcount;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UTIL.raise_dup_val_on_index;
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END bulk_ins;

<%IF DBMS_CRYPTO><%ELSE>$if false $then/*need to grant DBMS_CRYPTO*/<%END IF>
FUNCTION hash (r IN rowtype) RETURN VARCHAR2 IS
  sep    CONSTANT VARCHAR2(1) := '|';
  digest CLOB;
  ret    RAW(2000);
BEGIN
  log_start('hash');

  <%COLUMNS EXCLUDING GENERATED,LOBS>
  digest := digest || sep || r.#col#;~
  digest := digest || sep || TO_CHAR(r.#col#, UTIL.DATE_FORMAT);{DATE}~
  digest := digest || sep || TO_CHAR(r.#col#, UTIL.DATETIME_FORMAT);{DATETIME}~
  digest := digest || sep || TO_CHAR(r.#col#, UTIL.TIMESTAMP_FORMAT);{TIMESTAMP}~
  digest := digest || sep || TO_CHAR(r.#col#, UTIL.TIMESTAMP_TZ_FORMAT);{TIMESTAMP_TZ}~
  <%END>

  ret := DBMS_CRYPTO.hash(digest, DBMS_CRYPTO.hash_sh1);

  log_end(ret);
  RETURN ret;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END hash;
<%IF DBMS_CRYPTO><%ELSE>$end<%END IF>

END #TAPI#;
<%END TEMPLATE>

$end
END TEMPLATES;

Template Syntax

You may be wondering what all the <%bla> and #bla# tags mean. These are the controlling elements for my code generator.

All template code is embedded within $if false $then ... $end so that the template package spec can be compiled without error in the schema, while still allowing most syntax highlighters to make the template easy to read and edit. This source is then read by the generator from the TEMPLATES database package.

Each template within the TEMPLATES package is delineated by the following structural codes, each of which must appear at the start of a line:

&amp;amp;amp;amp;amp;lt;%TEMPLATE template_name&amp;amp;amp;amp;amp;gt;
...
&amp;amp;amp;amp;amp;lt;%END TEMPLATE&amp;amp;amp;amp;amp;gt;

Anything in the TEMPLATES package not within these structural elements is ignored by the generator.

Some simple placeholders are supported anywhere in a template:

  • #SYSDATE# – Today’s date in DD-MON-YYYY format
  • #TABLE# – Table name in uppercase
  • #table# – Table name in lowercase
  • #USER# – User name who executed the procedure
  • #Entity# – User-friendly name based on table name, singular (e.g. EVENTS -> Event)
  • #Entities# – User-friendly name based on table name
  • #TAPI# – Table API package name
  • #APEXAPI# – Apex API package name
  • \n – Insert a linefeed (not often required, since actual linefeeds in the template are usually retained)

These are all case-sensitive; in some cases an UPPERCASE, lowercase and Initcap version is supported for a placeholder.

Code portions that are only required in certain cases may be surrounded with the IF/ELSE/END IF structure:

&amp;amp;amp;amp;amp;lt;%IF condition&amp;amp;amp;amp;amp;gt;
   ...
&amp;amp;amp;amp;amp;lt;%ELSE&amp;amp;amp;amp;amp;gt;
   ...
&amp;amp;amp;amp;amp;lt;%END IF&amp;amp;amp;amp;amp;gt;

Currently the list of conditions are limited to LOBS (true if the table has any LOB-type columns), ROWID (true if the table does NOT have a surrogate key (i.e. a primary key matched by name to a sequence), or the name of a table (useful to have some code that is only generated for a specific table), or the name of a DBMS_xxx package (useful to have code that is only generated if the owner has been granted EXECUTE on the named DBMS_xxx package).

To negate a condition, simply leave the first part of the IF/ELSE part empty, e.g.:

&amp;amp;amp;amp;amp;lt;%IF LOBS&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;lt;%ELSE&amp;amp;amp;amp;amp;gt; /*this table has no LOBS*/ &amp;amp;amp;amp;amp;lt;%END IF&amp;amp;amp;amp;amp;gt;

Code portions that need to be repeated for each column (or a subset of columns) in the table use the COLUMNS structure:

(&amp;amp;amp;amp;amp;lt;%COLUMNS&amp;amp;amp;amp;amp;gt;
 #col#--- =&amp;amp;amp;amp;amp;gt; :#COL#~
,&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;)

The COLUMNS structure looks very weird and might take a while to get used to, but basically it contains a list of sub-templates, delimited by tildes (~). The first sub-template (e.g. #col#--- => :#COL#) is used for each column, and the second sub-template (e.g. ,) is inserted between each column (if there is more than one column). In the above example, our emps table would result in the following generated:

(emp_id       =&amp;amp;amp;amp;amp;gt; :EMP_ID
,name         =&amp;amp;amp;amp;amp;gt; :NAME
,emp_type     =&amp;amp;amp;amp;amp;gt; :EMP_TYPE
,start_date   =&amp;amp;amp;amp;amp;gt; :START_DATE
,end_date     =&amp;amp;amp;amp;amp;gt; :END_DATE
,dummy_ts     =&amp;amp;amp;amp;amp;gt; :DUMMY_TS
,dummy_tsz    =&amp;amp;amp;amp;amp;gt; :DUMMY_TSZ
,life_history =&amp;amp;amp;amp;amp;gt; :LIFE_HISTORY)

Notice that #col# is replaced with the column name in lowercase, and #COL# is replaced with the column name in uppercase. In addition, the --- is a special code that causes the generator to insert additional spaces so that the code is aligned vertically. Notice also that the second sub-template (the separator bit with the comma) also includes a carriage return (after ~ and before ,). If we had instead used the following template:

&amp;amp;amp;amp;amp;lt;%COLUMNS&amp;amp;amp;amp;amp;gt;
#col#--- =&amp;amp;amp;amp;amp;gt; :#COL#~,&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

This would have been the result:

emp_id       =&amp;amp;amp;amp;amp;gt; :EMP_ID,name         =&amp;amp;amp;amp;amp;gt; :NAME,emp_type     =&amp;amp;amp;amp;amp;gt; :EMP_TYPE,start_date   =&amp;amp;amp;amp;amp;gt; :START_DATE,end_date     =&amp;amp;amp;amp;amp;gt; :END_DATE,dummy_ts     =&amp;amp;amp;amp;amp;gt; :DUMMY_TS,dummy_tsz    =&amp;amp;amp;amp;amp;gt; :DUMMY_TSZ,life_history =&amp;amp;amp;amp;amp;gt; :LIFE_HISTORY

The generator gives you a great deal of control over which columns are included. The COLUMNS structure supports three optional clauses: INCLUDING, EXCLUDING and ONLY.

&amp;amp;amp;amp;amp;lt;%COLUMNS&amp;amp;amp;amp;amp;gt;
  (all columns in the table, EXCEPT for virtual columns)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS INCLUDING VIRTUAL&amp;amp;amp;amp;amp;gt;
  (all columns in the table, including virtual columns)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS EXCLUDING PK&amp;amp;amp;amp;amp;gt;
  (all columns except for Primary Key columns)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS EXCLUDING LOBS&amp;amp;amp;amp;amp;gt;
  (all columns except for LOB-type columns)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS EXCLUDING EMPS.NAME&amp;amp;amp;amp;amp;gt;
  (all columns - except for the specified column)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS EXCLUDING AUDIT&amp;amp;amp;amp;amp;gt;
  (all columns except for the audit columns such as CREATED_BY, etc.)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS ONLY PK&amp;amp;amp;amp;amp;gt;
  (only Primary Key columns)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS ONLY PK,NAME&amp;amp;amp;amp;amp;gt;
  (only Primary Key columns and columns named NAME)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS INCLUDING ROWID&amp;amp;amp;amp;amp;gt;
  (all columns in the table, plus the pseudocolumn ROWID)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS INCLUDING MADEUPNAME&amp;amp;amp;amp;amp;gt;
  (all columns in the table, plus a fake column)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS INCLUDING EMPS.MADEUPNAME&amp;amp;amp;amp;amp;gt;
  (all columns in the table, plus a fake column for the specified table)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

&amp;amp;amp;amp;amp;lt;%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID&amp;amp;amp;amp;amp;gt;
  (multiple criteria may be combined)
&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

Within a sub-template the following placeholders are recognised:

  • #COL# – column name in uppercase
  • #col# – column name in lowercase
  • #Label# – generated user-friendly label based on column name
  • #MAXLEN# – max length for a CHAR-type column
  • #DATA_DEFAULT# – column default value
  • #SEQ# – surrogate key sequence name
  • #00i# – 001, 002, 003 etc. in order of column id
  • --- – padding (inserts just enough extra spaces depending on length of column name so that code is aligned vertically)

For example, the following generates a comma-delimited list of user-friendly labels for each column in the table:

&amp;amp;amp;amp;amp;lt;%COLUMNS&amp;amp;amp;amp;amp;gt;#Label#~, &amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;
Emp, Name, Emp Type, Start, End, Dummy, Dummy, Life History

Side Note: it’s noteworthy that I have no need for a “#datatype#” placeholder; in most cases my templates will anchor to the column’s datatype anyway, so a template just needs to use #col#%TYPE.

Multiple additional sub-templates may be provided within a <%COLUMNS> structure, to be used for certain columns. These must end with a {X} indicator, where X can be a data type or column name. Other indicators are supported for special cases as well.

&amp;amp;amp;amp;amp;lt;%COLUMNS&amp;amp;amp;amp;amp;gt;
Default subtemplate                       ~
ID column                                 {ID}~
NUMBER column                             {NUMBER}~
Date/time column                          {DATETIME}~
Date column                               {DATE}~
Timestamp column                          {TIMESTAMP}~
Timestamp with time zone                  {TIMESTAMP_TZ}~
Indicator (Y or null) column              {IND}~
Yes/No (Y or N) column                    {YN}~
Any other VARCHAR2 column                 {VARCHAR2}~
Any LOB-type column (e.g. BLOB, CLOB)     {LOB}~
Any specific datatype                     {CLOB}~
Primary key matched to a sequence         {SURROGATE_KEY}~
Special case for a specific column        {TABLE.COLUMN}~
Extra code to be used if NO columns match {NONE}~
,&amp;amp;amp;amp;amp;lt;%END&amp;amp;amp;amp;amp;gt;

The “data type” for a column is usually just the data type from the schema data dictionary; however, there are some special cases where a special data type is derived from the column name:

  • ID: a NUMBER column with a name ending with _ID
  • DATETIME: a DATE column with name ending with _DT
  • IND: a VARCHAR2 column with a name ending with _IND
  • YN: a VARCHAR2 column with a name ending with _YN

Within a template it is possible to import the code from another template (e.g. to share code between multiple templates, or to facilitate a nested-IF structure) using this structure:

&amp;amp;amp;amp;amp;lt;%INCLUDE OTHERTEMPLATE&amp;amp;amp;amp;amp;gt;

This will cause the generator to find a template named OTHERTEMPLATE, evaluate it, then insert it at the given position.

This method has allowed my code generator to be quite flexible and powerful, makes it easy to add additional code to all my API packages and other generated code, and makes it easy to find and fix errors.

You can download all the source for the template and generator below. Note that a new Sample Apex application is included (f560.sql) which works in Apex 5 and uses the new Apex API. Disclaimer:This is a work in progress!

If you find it useful or you have suggestions for improvement please comment.

Source code/download: https://github.com/jeffreykemp/jk64-sample-apex-xapi

Refresh APEX Calendar
APEX API – call a package for all your DML

Comments

  1. Hi Jeff,

    I wanted to take a closer look at your TAPI Generator Mkii and the APEX Sample Application.

    I read your thoughts about TAPIS and XAPIS and found it inspiring.

    I would like to follow the idea of generating TAPIS whereas in some respect I don’t like TAPIs because of the data model
    of my application having more than 1 table for a business entity. For example there are
    some entities for which I want to have a locale setting, so I have a main table and language specific
    table holding all the language specific terms.
    For the moment I am not sure how to handle this problem, but anyway…

    Unfortunately I couldn’t get your sample including the TAPI-Generator Mkii up and running.

    First of all there was the declarion for type t_str_array missing

    I got that straight with a ‘create type t_str_array as table of varchar2(4000);’

    But when the generate-package wanted to compile the CSV_UTIL_PKG-package could not been found
    (call to CSV_UTIL_PKG.csv_to_array(pseudocolumns) e.g., Line 256 in the generate-package-body and some following lines).

    Is this still under construction or is there something else I am missing?

    Best regards
    Jochen (Germany)

  2. Hi Jochen,

    Thanks for your comments, I do think this is a great way to build a system and encourage you to persist.

    I apologise for the state of the code I’ve checked in – since I wrote this post I’ve been playing around with a few other ideas as well and have just checked them in out of habit – but then I’ve been distracted by other projects and so this repository is not in a very stable state right now.

    I’m planning to get back in and clean up the example code, and then package it for release so that this problem doesn’t re-occur.

    In the mean time, you can download CSV_UTIL_PKG from the Alexandria library – download it from here: http://github.com/mortenbra/alexandria-plsql-utils – you don’t need to install the entire library if you don’t want to, you just need the CSV_UTIL_PKG and its dependencies – including T_STR_ARRAY and some other types.

    I hope this helps.

    • Hi Jeffrey,

      thanks for your quick response – apology accepted 😉

      To tell the truth I was looking for a TAPI framework/generator and “the race” was between
      your Mkii and tapigen2 from Oscar Magallanes.

      What I liked in your generator was the integration with the APEX-Package-Generator – what I like in
      tapigen2 was the use of the LOGGER-Framework from Martin D’Souza (and the fact that it worked right away 😉 )

      Anyway…I havent’t made up my mind yet because I have a general “thing” (wouldn’t call it a problem) with Tapis
      and generally would prefer XAPIs (but I don’t want to program every XAPI manually).

      Let me illustrate this with a simple example: in my newly to be designed system a want to be multilingual from the beginning,
      which means, that I have 2 tables for an entity at minimum – sometimes even more.

      The main table could look like this

      CREATE TABLE SA.SAT_ENTITY
      (
      ID_MANDATOR NUMBER DEFAULT 1 NOT NULL,
      ID_ENTITY NUMBER NOT NULL,
      ID_ENTITY_TYPE NUMBER DEFAULT -1,
      NOTATION VARCHAR2(255 BYTE),
      NOTE VARCHAR2(4000 BYTE),
      DATE_CREATED DATE DEFAULT sysdate NOT NULL,
      DATE_UPDATED DATE,
      DATE_DEACTIVATED DATE,
      USER_CREATED VARCHAR2(255 BYTE),
      USER_UPDATED VARCHAR2(255 BYTE),
      USER_DEACTIVATED VARCHAR2(255 BYTE)
      )

      The table holding the language specific entries looks like this

      CREATE TABLE SA.SAT_ENTITY_LANG
      (
      ID_ENTITY NUMBER NOT NULL,
      ID_LANGUAGE NUMBER NOT NULL,
      NOTATION VARCHAR2(255 BYTE) NOT NULL,
      NOTE VARCHAR2(4000 BYTE),
      DESCRIPTION VARCHAR2(4000 BYTE),
      NOTATION_SHORT VARCHAR2(255 BYTE),
      DATE_CREATED DATE DEFAULT sysdate NOT NULL,
      DATE_UPDATED DATE,
      USER_CREATED VARCHAR2(255 BYTE),
      USER_UPDATED VARCHAR2(255 BYTE)
      )

      So the “entity” will be “spread” over two tables with the …LANG table holding the language specific entries.
      The language is set by the language-Id of cause.

      Do you have ideas or plans to cut through this shortcummings of the TAPI-architecture? I don’t have any ideas how
      this can be solved generically, but if there could be identified a certain pattern…maybe it could work.

      Best regards
      Jochen

  3. Hi Jochen,

    Note that my TAPI generator, like most generators including Oscar’s I suspect, is designed to be adapted and extended for your purposes. Mine does have LOGGER generation included as well, although I didn’t show this in my blog post.

    If you have a lot of “Entity” / “Language” table pairs, this method will almost certainly help you generate all the code you need. In the same way that I generated an Apex API for each table, on top of a TAPI for each table, in your position I would also generate a Language API for each base table. Personally, I would create TAPIs on each and every table, then hide the Language logic behind a XAPI for each Entity/Language table pair; but you don’t necessarily need to do it that way.

    The approach I use is to first write just one example for one table, get it as perfect and well-tested as I can, then copy-and-paste the code into the TEMPLATES package spec. Then it’s a matter of going through the code and replacing any references to the table with the appropriate substitution string; and anywhere there is some code that must be repeated for each column, add the <%COLUMNS specifications. Because you start with a template that works for one entity (e.g. SAT_ENTITY), you can do this iteratively – make a few substitutions, generate the package, fix any compilation or logic errors, rinse and repeat. When you have finished making all the substitutions, you then start testing the template against other tables with different column structures. Eventually you’ll end up with a generator that will handle any tables in your schema along with their Language pair.

    I hope this helps.

    • Hi Jeff,
      in the TEMPATE package header I get an error with the preprocessor directive (Line 29)
      $if false $then
      where has to be the endif?

      Second question: which version of the logger-Util you are expecting (got some more complile errors)?

      …or not to bother you because of every error or patential error: when do you think you’ll have the next version finished and uploaded?
      Thanks in advance
      Jochen

  4. Hi Jochen,

    Don’t wait for me to get the next version finished, it will be some time as I have lots of other projects on the go at the moment, including ones that pay me money 🙂

    The TEMPLATES package should have an $end at the very bottom of the package because the majority of its contents are not supposed to be compiled.

    I’ve been using logger version 3.1.1 http://github.com/OraOpenSource/Logger/releases/tag/3.1.1 – what compilation errors are you getting?

    Feel free to bother me 🙂 I might not always be able to respond immediately, but I will respond eventually.

    Thanks,

    Jeff

  5. Claudio Arriagada
    25 August 2020 - 3:17 am

    Hi Jeff. I want to take a closer look at your TAPI Generator Mkii.

    Is there any repository to download the source code of the generators.?. Thanks in advance.
    Regards

  6. Claudio Alejandro Arriagada Jara
    26 August 2020 - 3:48 am

    Hi Jeff. Thank you very much. Can you also share source code generators related with APEX?, like you mention in https://jeffkemponoracle.com/2016/02/apex-api-call-a-package-for-all-your-dml/

    Regards,
    Claudio.

    • Hi Claudio, it’s the same source (I’ll update the link). Note that I would not necessarily go this path nowadays, given recent improvements in APEX.

      Jeff

Leave a Reply

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