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:

<%TEMPLATE template_name>
...
<%END TEMPLATE>

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:

<%IF condition>
   ...
<%ELSE>
   ...
<%END IF>

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.:

<%IF LOBS><%ELSE> /*this table has no LOBS*/ <%END IF>

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

(<%COLUMNS>
 #col#--- => :#COL#~
,<%END>)

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       => :EMP_ID
,name         => :NAME
,emp_type     => :EMP_TYPE
,start_date   => :START_DATE
,end_date     => :END_DATE
,dummy_ts     => :DUMMY_TS
,dummy_tsz    => :DUMMY_TSZ
,life_history => :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:

<%COLUMNS>
#col#--- => :#COL#~,<%END>

This would have been the result:

emp_id       => :EMP_ID,name         => :NAME,emp_type     => :EMP_TYPE,start_date   => :START_DATE,end_date     => :END_DATE,dummy_ts     => :DUMMY_TS,dummy_tsz    => :DUMMY_TSZ,life_history => :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.

<%COLUMNS>
  (all columns in the table, EXCEPT for virtual columns)
<%END>

<%COLUMNS INCLUDING VIRTUAL>
  (all columns in the table, including virtual columns)
<%END>

<%COLUMNS EXCLUDING PK>
  (all columns except for Primary Key columns)
<%END>

<%COLUMNS EXCLUDING LOBS>
  (all columns except for LOB-type columns)
<%END>

<%COLUMNS EXCLUDING EMPS.NAME>
  (all columns - except for the specified column)
<%END>

<%COLUMNS EXCLUDING AUDIT>
  (all columns except for the audit columns such as CREATED_BY, etc.)
<%END>

<%COLUMNS ONLY PK>
  (only Primary Key columns)
<%END>

<%COLUMNS ONLY PK,NAME>
  (only Primary Key columns and columns named NAME)
<%END>

<%COLUMNS INCLUDING ROWID>
  (all columns in the table, plus the pseudocolumn ROWID)
<%END>

<%COLUMNS INCLUDING MADEUPNAME>
  (all columns in the table, plus a fake column)
<%END>

<%COLUMNS INCLUDING EMPS.MADEUPNAME>
  (all columns in the table, plus a fake column for the specified table)
<%END>

<%COLUMNS ONLY SURROGATE_KEY,VERSION_ID INCLUDING ROWID>
  (multiple criteria may be combined)
<%END>

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:

<%COLUMNS>#Label#~, <%END>
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.

<%COLUMNS>
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}~
,<%END>

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:

<%INCLUDE OTHERTEMPLATE>

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: http://bitbucket.org/jk64/jk64-sample-apex-tapi


Refresh APEX Calendar

calendarwithrefreshbutton.PNG
Sometimes it’s the simple little things that can add polish and make your Apex application shine. One simple little thing that you can do is add a Refresh button to improve the usability of your Apex 5 calendar. This makes it easy for the user to see recent changes on the database, e.g. if events had been added or changed since the page had last been loaded.

  1. Set the Static ID on the Calendar region (e.g. “eventscalendar“)
  2. Add an Icon button (Button Template = “Icon”) to the calendar region
  3. Set the button’s Static ID (e.g. “refreshbutton“)
  4. Set Icon CSS Classes to “fa-refresh
  5. Set Action to “Defined by Dynamic Action”
  6. (optional) Set Template Option -> Style to “Remove UI Decoration”
  7. Add a Dynamic Action to the button, Event = “Click”
  8. Set Fire on Page Load to “No”
  9. Add a True Action “Execute Javascript Code” with the code below:
$("#eventscalendar_calendar").fullCalendar("refetchEvents");

This calls the refetchEvents method of the FullCalendar object. Replace the “eventscalendar” part of the id with whatever static ID you set on the Calendar region in step #1.

Now, to add a bit of pizzazz you can get the refresh button icon to spin while the calendar is being refreshed. To do this, change the dynamic action code to this instead:

$("#refreshbutton span.t-Icon").addClass("fa-spin");
window.setTimeout(function() {
  $("#eventscalendar_calendar").fullCalendar("refetchEvents");
  window.setTimeout(function() {
    $("#refreshbutton span.t-Icon").removeClass("fa-spin");
  }, 1000);
}, 50);

This code starts the refresh icon spinning before invoking refetchEvents, then stops the icon spinning after it has completed. Note that these are done via timeouts (otherwise the icon isn’t repainted until after the entire javascript function has completed). I added a wait of 1 second prior to stopping the spinning because most of the time the refresh is too quick to notice the spinning effect.

You can, if it makes sense in your case, also make the calendar automatically refresh itself periodically, using some simple javascript: add the following function to the page Function and Global Variable Declaration:

function refreshCalendar() {
  $("#refreshbutton span.t-Icon").addClass("fa-spin");
  window.setTimeout(function() {
    $("#eventscalendar_calendar").fullCalendar("refetchEvents");
    window.setTimeout(function() {
      $("#refreshbutton span.t-Icon").removeClass("fa-spin");
    }, 1000);
  }, 50);
}

Then add this to start the timer in the page attribute Execute when Page Loads:

var periodicrefresh = setInterval(function() {
                                    refreshCalendar();
                                  }, 30000);

In this example, I’ve set the timer to go off every 30 seconds. Not only does it refresh the calendar, but the user gets feedback on what’s going on because the refresh button icon is spinning. Be careful not to set the timeout too low, or else your database could get very busy!

The function I’ve declared can now also be reused by the button’s dynamic action, so I can replace the DA javascript with simply:

refreshCalendar();

APEX Developer Toolbar Options

One of the things that used to bug me about the Apex developer toolbar was that it sometimes obscured the content I was trying to test at the bottom of the page; you could turn it off but then next thing you want to access it you have to jump through the hoops to turn it back on again.

I just noticed it now has some new display options which solves this problem perfectly:

devtoolbaroptions1

  • Auto Hide – I turn this on so that it slides almost completely out of the way when I don’t want it (move your mouse over it to make it pop out again, click into your page to hide it)
  • Show Icons Only – once you’re familiar with the options you can shrink the toolbar to show only the icons (hover over the icon to see the label)
  • Display Position – put it on the Right-hand side of the window instead of the bottom

A random string of digits

715175039885776956103287888080

I needed to generate a random string with an exact length consisting of numeric digits, that I could send in an SMS to a user as a temporary account “pin”. DBMS_RANDOM.string is unsuitable for this purpose as its supported modes all include alphabetic characters. So I used DBMS_RANDOM.value instead. I call TRUNC afterwards to lop off the decimal portion.

FUNCTION random_pin (digits IN NUMBER)
  RETURN NUMBER IS
BEGIN
  IF digits IS NULL OR digits < 1 OR digits > 39 THEN
    RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39');
  END IF;

  IF digits = 1 THEN
    RETURN TRUNC( DBMS_RANDOM.value(0,10) );
  ELSE
    RETURN TRUNC( DBMS_RANDOM.value(
                    POWER(10, digits-1)
                   ,POWER(10, digits) ));
  END IF;
END random_pin;

random_pin(digits => 6);

482372

EDIT 8/1/2016: added special case for 1 digit

ADDENDUM

Because the requirements of my “pin” function was to return a value that would remain unchanged when represented as an integer, it cannot return a string of digits starting with any zeros, which is why the lowerbound for the random function is POWER(10,digits-1). This, unfortunately, makes it somewhat less than perfectly random because zeroes are less frequent – if you call this function 1000 times for a given length of digits, then counted the frequency of each digit from 0..9, you will notice that 0 has a small but significantly lower frequency than the digits 1 to 9.

To fix this, the following function returns a random string of digits, with equal chance of returning a string starting with one or more zeroes:

FUNCTION random_digits (digits IN NUMBER)
  RETURN VARCHAR2 IS
BEGIN
  IF digits IS NULL OR digits < 1 OR digits > 39 THEN
    RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39');
  END IF;
 
  RETURN LPAD( TRUNC(
    DBMS_RANDOM.value(0, POWER(10, digits))
    ), digits, '0');
END random_digits;

The above functions may be tested and downloaded from Oracle Live SQL.


APEX 5 Application Context

"under the hood"Just a quick note that (as mentioned by Christian Neumueller earlier) APEX 5 now populates an Application Context APEX$SESSION with the session’s User, Session ID and Workspace ID:

SYS_CONTEXT('APEX$SESSION','APP_USER')
SYS_CONTEXT('APEX$SESSION','APP_SESSION')
SYS_CONTEXT('APEX$SESSION','WORKSPACE_ID')

Using the above should be faster in your queries than calling v() to get these values. Note that the alias 'SESSION' won’t work like it does with v().

The context is managed by the database package APEX_050000.WWV_FLOW_SESSION_CONTEXT which is an undocumented API used internally by APEX to synchronize the context with the associated APEX attibutes. Incidentally, the comments in the package indicate it was authored by Chris himself.

Personally I was hoping that a bit more of the session state would be replicated in the context, e.g. application ID, page ID, request, debug mode, application items and page items.

Sidebar: to see all contexts that are visible to your session, query ALL_CONTEXT. To see all context values set in your session, query SESSION_CONTEXT. Of course, don’t query these in your application code to get individual values – that’s what the SYS_CONTEXT function is for.


Detect Empty List

You have a Select List item on your page driven from a dynamic query, e.g. one that only shows valid values. One day, users notice that the list appears empty and raise a defect note.

emptylist.PNG

You check the query behind the list and verify that indeed, the list should be empty because there are no valid values to show. It’s an optional item so the user is free to save the record if they wish.

There are a number of ways we could make this more user-friendly: depending on the specifics of the situation, we might just hide the item, or we might want to show an alternative item or a warning message. We can do any of these things quite easily using either a computation on page load (if the list doesn’t change while the page is open) or a dynamic action.

In the case of my client, they wanted the item to remain on screen, but to show an orange warning message to let them know that there are no gateways currently available; this is only a warning because there are subsequent processes that can handle the missing gateway (e.g. a higher-privileged user can assign a “hidden” gateway to the record if they deem it suitable).

To do this we create a display item (e.g. “P1_NO_GATEWAY_WARNING” which shows the warning message) and a dynamic action with the following attributes:

  • Event = Page Load
  • Condition = JavaScript expression
  • Value = listIsEmpty("P1_GATEWAY_ID")
  • True Action = Set Value
  • Set Type = Static Assignment
  • Value = Warning: no gateways currently available
  • Selection Type = Item(s)
  • Item(s) = P1_NO_GATEWAY_WARNING

In the page’s Function and Global Variable Declaration, or (even better) in the application’s global javascript file, we add the following:

function listIsEmpty(itemName) {
  return $("#" + itemName + " option:enabled").filter(
    function(){return this.text;}
    ).length==0;
}

This was adapted from some solutions here. It looks for all <option>s under the item, filters the list for options which are not disabled and have a label, and returns true if the remaining set is empty. I added the this.text bit because the empty lists generated by Apex include a single empty option for the “NULL” value. This is because I have set the list item’s Null Display Value to blank (null).

emptylistwarning


Media player in APEX

Quite a long time ago I made a collection of MP3s available from our APEX website and made them playable within the browser using Google’s shockwave player, using code like this:

<embed type="application/x-shockwave-flash"
       flashvars="audioUrl=#FILE_URL#"
       src="/3523697345-audio-player.swf"
       width="400"
       height="27"
       quality="best">
</embed>

This relies on the user’s browser being able to run flash applications. It looked like this:
audio-player-old

With HTML5, however, this is no longer required, so I’ve updated it to:

<audio controls preload>
  <source src="#FILE_URL#" type="audio/mpeg">
</audio>

Not only is it simpler and no longer requires flash, it looks much nicer as well:
audio-player-new

Note: you may or may not want to include the preload tag, especially if you have more than one audio control on a page.


SQL Problem Solved: Merge History Tables

Yesterday I published a SQL problem (that I I had solved – or at least, thought I had solved!) and invited readers to submit their own solutions.

SPOILER ALERT: if you haven’t had a chance to have a go yourself, go back, don’t read the comments (yet), but see if you can solve it yourself.

I was very pleased to see two excellent submissions that were both correct as far as I could tell, and are (in my opinion) sufficiently simple, elegant and efficient. Note that this was not a competition and I will not be ranking or rating or anything like that. If you need to solve a similar problem, I would suggest you try all of these solutions out and pick the one that seems to work the best for you.

Solution #1

Stew Ashton supplied the first solution which was very simple and elegant:

with dates as (
  select distinct deptid, dte from (
    select deptid, from_date, to_date+1 to_date from t1
    union all
    select deptid, from_date, to_date+1 to_date from t2
  )
  unpivot(dte for col in(from_date, to_date))
)
select * from (
  select distinct a.deptid, dte from_date,
  lead(dte) over(partition by a.deptid order by dte) - 1 to_date,
  data1, data2 from dates a
  left join t1 b on a.deptid = b.deptid and a.dte between b.from_date and b.to_date
  left join t2 c on a.deptid = c.deptid and a.dte between c.from_date and c.to_date
)
where data1 is not null or data2 is not null
order by 1,2;

SQL Fiddle

Stew’s solution does a quick pass over the two tables and generates a “date dimension” table in memory; it then does left joins to each table to generate each row. It may have a disadvantage due to the requirement to make two passes over the tables, which might be mitigated by suitable indexing. As pointed out by Matthias Rogel, this solution can be easily generalised to more than two tables, which is a plus.

Solution #2

The second solution was provided by Kim Berg Hansen which was a bit longer but in my opinion, still simple and elegant:

with v1 as (
   select t.deptid
        , case r.l
             when 1 then t.from_date
             when 2 then date '0001-01-01'
             when 3 then t.max_to + 1
          end from_date
        , case r.l
             when 1 then t.to_date
             when 2 then t.min_from -1
             when 3 then date '9999-01-01'
          end to_date
        , case r.l
             when 1 then t.data1
          end data1
     from (
      select deptid
           , from_date
           , to_date
           , data1
           , row_number() over (partition by deptid order by from_date) rn
           , min(from_date) over (partition by deptid) min_from
           , max(to_date) over (partition by deptid) max_to
        from t1
     ) t, lateral( 
         select level l
           from dual
         connect by level <= case t.rn when 1 then 3 else 1 end
     ) r
), v2 as (
   select t.deptid
        , case r.l
             when 1 then t.from_date
             when 2 then date '0001-01-01'
             when 3 then t.max_to + 1
          end from_date
        , case r.l
             when 1 then t.to_date
             when 2 then t.min_from -1
             when 3 then date '9999-01-01'
          end to_date
        , case r.l
             when 1 then t.data2
          end data2
     from (
      select deptid
           , from_date
           , to_date
           , data2
           , row_number() over (partition by deptid order by from_date) rn
           , min(from_date) over (partition by deptid) min_from
           , max(to_date) over (partition by deptid) max_to
        from t2
     ) t, lateral( 
         select level l
           from dual
         connect by level <= case t.rn when 1 then 3 else 1 end
     ) r
)
select v1.deptid
     , greatest(v1.from_date, v2.from_date) from_date
     , least(v1.to_date, v2.to_date) to_date
     , v1.data1
     , v2.data2
  from v1
  join v2
      on v2.deptid = v1.deptid
     and v2.to_date >= v1.from_date
     and v2.from_date <= v1.to_date
 where v2.data2 is not null or v1.data1 is not null
 order by v1.deptid
        , greatest(v1.from_date, v2.from_date)

Since it takes advantage of 12c’s new LATERAL feature, I wasn’t able to test it on my system, but I was able to test it on Oracle’s new Live SQL and it passed with flying colours. I backported it to 11g and it worked just fine on my system as well. An advantage of Kim’s solution is that it should only make one pass over each of the two tables.

Oracle Live SQL: merge-history-kimberghansen (this is the 12c version – Oracle login required)

SQL Fiddle (this is my attempt to backport to 11g – you can judge for yourself if I was faithful to the original)

Solution #3

When my colleague came to me with this problem, I started by creating a test suite including as many test cases as we could think of. Developing and testing the solution against these test cases proved invaluable and allowed a number of ideas to be tested and thrown away. Big thanks go to Kim Berg Hansen for providing an additional test case, which revealed a small bug in my original solution.

with q1 as (
select nvl(t1.deptid, t2.deptid) as deptid
      ,greatest(nvl(t1.from_date, t2.from_date), nvl(t2.from_date, t1.from_date)) as gt_from_date
      ,least(nvl(t1.to_date, t2.to_date), nvl(t2.to_date, t1.to_date))            as lt_to_date
      ,t1.from_date AS t1_from_date
      ,t1.to_date   AS t1_to_date
      ,t2.from_date AS t2_from_date
      ,t2.to_date   AS t2_to_date
      ,min(greatest(t1.from_date, t2.from_date)) over (partition by t1.deptid) - 1 as fr_to_date
      ,max(least(t1.to_date, t2.to_date)) over (partition by t2.deptid)        + 1 as lr_from_date
      ,t1.data1
      ,t2.data2
      ,row_number() over (partition by t1.deptid, t2.deptid order by t1.from_date) as rn_fr1
      ,row_number() over (partition by t1.deptid, t2.deptid order by t2.from_date) as rn_fr2
      ,row_number() over (partition by t1.deptid, t2.deptid order by t1.to_date desc) as rn_lr1
      ,row_number() over (partition by t1.deptid, t2.deptid order by t2.to_date desc) as rn_lr2
from   t1
full outer join t2
on     t1.deptid = t2.deptid
and    (t1.from_date between t2.from_date and t2.to_date
        or t1.to_date between t2.from_date and t2.to_date
        or t2.from_date between t1.from_date and t1.to_date
        or t2.to_date between t1.from_date and t1.to_date)
order by 1, 2
)
select deptid
      ,gt_from_date AS from_date
      ,lt_to_date   AS to_date
      ,data1
      ,data2
from   q1
union all
select deptid
      ,t1_from_date as from_date
      ,fr_to_date AS to_date
      ,data1
      ,'' AS data2
from   q1
where  fr_to_date between t1_from_date and t1_to_date
and    rn_fr1 = 1
union all
select deptid
      ,t2_from_date as from_date
      ,fr_to_date AS to_date
      ,'' AS data1
      ,data2
from   q1
where  fr_to_date between t2_from_date and t2_to_date
and    rn_fr2 = 1
union all
select deptid
      ,lr_from_date as from_date
      ,t2_to_date AS to_date
      ,'' AS data1
      ,data2
from   q1
where  lr_from_date between t2_from_date and t2_to_date
and    rn_lr2 = 1
union all
select deptid
      ,lr_from_date as from_date
      ,t1_to_date AS to_date
      ,data1
      ,'' AS data2
from   q1
where  lr_from_date between t1_from_date and t1_to_date
and    rn_lr1 = 1
order by deptid, from_date

SQL Fiddle

This solution is probably the ugliest of the three, and though it only requires one pass over the two tables, it materializes all the data in a temp table so probably requires a fair amount of memory for large data sets.

I’m so glad I posted this problem – I received help in the form of two quite different solutions, which help to show different ways of approaching problems like this. Not only that, but it highlighted the value of test-driven development – instead of groping in the dark to come up with a quick-and-dirty solution (that might or might not work), starting with comprehensive test cases is invaluable for both developing and validating potential solutions.


SQL Problem: Merge record history tables

A colleague asked me if I could help solve a problem in SQL, and I was grateful because I love solving problems in SQL! So we sat down, built a test suite, and worked together to solve it.

I will first present the problem and invite you to have a go at solving it for yourself; tomorrow I will post the solution we came up with. I think our solution is reasonably simple and elegant, but I’m certain there are other solutions as well, some of which may be more elegant or efficient.

I have two “record history” tables, from different data sources, that record the history of changes to two sets of columns describing the same entity. Here are the example tables:

T1 (deptid, from_date, to_date, data1)
T2 (deptid, from_date, to_date, data2)

So T1 records the changes in the column “data1” over time for each deptid; and T2 records the changes in the column “data2” over time for each deptid.

Assumptions/Constraints on T1 and T2:

  • All columns are not Null.
  • All dates are just dates (no time values).
  • All date ranges are valid (i.e. from_date <- to_date).
  • Within each table, all date ranges for a particular deptid are gap-free and have no overlaps.

We want to produce a query that returns the following structure:

T3 (deptid, from_date, to_date, data1, data2)

Where the dates for a particular deptid overlap between T1 and T2, we want to see the corresponding data1 and data2 values. Where the dates do not overlap (e.g. where we have a value for data1 in T1 but not in T2), we want to see the corresponding data1 but NULL for data2; and vice versa.

Below is the setup and test cases you can use if you wish. Alternatively, you can use the SQL Fiddle here.

create table t1
  (deptid number not null
  ,from_date date not null
  ,to_date date not null
  ,data1 varchar2(30) not null
  ,primary key (deptid, from_date)
  );

create table t2
  (deptid number not null
  ,from_date date not null
  ,to_date date not null
  ,data2 varchar2(30) not null
  ,primary key (deptid, from_date)
  );

--Test Case 1 (4001)
--T1  |-------------||-------------|
--T2         |-------------||-------------||-------------|

insert into t1 values (4001, date'2015-01-01', date'2015-01-31', 'jan data 1');
insert into t1 values (4001, date'2015-02-01', date'2015-02-28', 'feb data 1');
insert into t2 values (4001, date'2015-01-15', date'2015-02-14', 'jan-feb data 2');
insert into t2 values (4001, date'2015-02-15', date'2015-03-14', 'feb-mar data 2');
insert into t2 values (4001, date'2015-03-15', date'2015-04-14', 'mar-apr data 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4001    01-JAN-2015  14-JAN-2015  jan data 1
--4001    15-JAN-2015  31-JAN-2015  jan data 1      jan-feb data 2
--4001    01-FEB-2015  14-FEB-2015  feb data 1      jan-feb data 2
--4001    15-FEB-2015  28-FEB-2015  feb data 1      feb-mar data 2
--4001    01-MAR-2015  14-MAR-2015                  feb-mar data 2
--4001    15-MAR-2015  14-APR-2015                  mar-apr data 2

--Test Case 2 (4002)
--T1         |-------------||-------------||-------------|
--T2  |-------------||-------------|

insert into t1 values (4002, date'2015-01-15', date'2015-02-14', 'jan-feb data 1');
insert into t1 values (4002, date'2015-02-15', date'2015-03-14', 'feb-mar data 1');
insert into t1 values (4002, date'2015-03-15', date'2015-04-14', 'mar-apr data 1');
insert into t2 values (4002, date'2015-01-01', date'2015-01-31', 'jan data 2');
insert into t2 values (4002, date'2015-02-01', date'2015-02-28', 'feb data 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4002    01-JAN-2015  14-JAN-2015                  jan data 2
--4002    15-JAN-2015  31-JAN-2015  jan-feb data 1  jan data 2
--4002    01-FEB-2015  14-FEB-2015  jan-feb data 1  feb data 2
--4002    15-FEB-2015  28-FEB-2015  feb-mar data 1  feb data 2
--4002    01-MAR-2015  14-MAR-2015  feb-mar data 1
--4002    15-MAR-2015  14-APR-2015  mar-apr data 1

--Test Case 3 (4003)
--T1  |-------------|
--T2  |-------------|

insert into t1 values (4003, date'2015-01-01', date'2015-01-31', 'jan data 1');
insert into t2 values (4003, date'2015-01-01', date'2015-01-31', 'jan data 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4003    01-JAN-2015  31-JAN-2015  jan data 1      jan data 2

--Test Case 4 (4004)
--T1  |-------------|
--T2      |-----|

insert into t1 values (4004, date'2015-01-01', date'2015-01-31', 'jan data 1');
insert into t2 values (4004, date'2015-01-10', date'2015-01-20', 'jan data int 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4004    01-JAN-2015  09-JAN-2015  jan data 1
--4004    10-JAN-2015  20-JAN-2015  jan data 1      jan data int 2
--4004    21-JAN-2015  31-JAN-2015  jan data 1

--Test Case 5 (4005)
--T1      |-----|
--T2  |-------------|

insert into t1 values (4005, date'2015-01-10', date'2015-01-20', 'jan data int 1');
insert into t2 values (4005, date'2015-01-01', date'2015-01-31', 'jan data 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4005    01-JAN-2015  09-JAN-2015                  jan data 2
--4005    10-JAN-2015  20-JAN-2015  jan data int 1  jan data 2
--4005    21-JAN-2015  31-JAN-2015                  jan data 2

--Test Case 6 (4006)
--T1  ||------------|
--T2  |-------------|

insert into t1 values (4006, date'2015-01-01', date'2015-01-01', 'jan data a 1');
insert into t1 values (4006, date'2015-01-02', date'2015-01-31', 'jan data b 1');
insert into t2 values (4006, date'2015-01-01', date'2015-01-31', 'jan data 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4006    01-JAN-2015  01-JAN-2015  jan data a 1    jan data 2
--4006    02-JAN-2015  31-JAN-2015  jan data b 1    jan data 2

--Test Case 7 (4007)
--T1  |-------------|
--T2  ||------------|

insert into t1 values (4007, date'2015-01-01', date'2015-01-31', 'jan data 1');
insert into t2 values (4007, date'2015-01-01', date'2015-01-01', 'jan data a 2');
insert into t2 values (4007, date'2015-01-02', date'2015-01-31', 'jan data b 2');

--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4007    01-JAN-2015  01-JAN-2015  jan data 1      jan data a 2
--4007    02-JAN-2015  31-JAN-2015  jan data 1      jan data b 2

--Test Case 8 (4008)
--T1  |-------------|
--T2    |---||---||---|
 
insert into t1 values (4008, date'2015-01-01', date'2015-01-31', 'jan data 1');
insert into t2 values (4008, date'2015-01-09', date'2015-01-17', 'jan data a 2');
insert into t2 values (4008, date'2015-01-18', date'2015-01-26', 'jan data b 2');
insert into t2 values (4008, date'2015-01-27', date'2015-02-04', 'jan-feb data 2');
 
--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4008    01-JAN-2015  08-JAN-2015  jan data 1
--4008    09-JAN-2015  17-JAN-2015  jan data 1      jan data a 2
--4008    18-JAN-2015  26-JAN-2015  jan data 1      jan data b 2
--4008    27-JAN-2015  31-JAN-2015  jan data 1      jan-feb data 2
--4008    01-FEB-2015  04-FEB-2015                  jan-feb data 2

--Test Case 9 (4009)
--T1    |---||---||---|
--T2  |-------------|
 
insert into t1 values (4009, date'2015-01-09', date'2015-01-17', 'jan data a 1');
insert into t1 values (4009, date'2015-01-18', date'2015-01-26', 'jan data b 1');
insert into t1 values (4009, date'2015-01-27', date'2015-02-04', 'jan-feb data 1');
insert into t2 values (4009, date'2015-01-01', date'2015-01-31', 'jan data 2');
 
--Expected Output:
--DEPTID  FROM_DATE    TO_DATE      DATA1           DATA2
--======  ===========  ===========  ==============  ==============
--4009    01-JAN-2015  08-JAN-2015                  jan data 2
--4009    09-JAN-2015  17-JAN-2015  jan data a 1    jan data 2
--4009    18-JAN-2015  26-JAN-2015  jan data b 1    jan data 2
--4009    27-JAN-2015  31-JAN-2015  jan-feb data 1  jan data 2
--4009    01-FEB-2015  04-FEB-2015  jan-feb data 1

EDIT 15/10/2015: added Kim Berg Hansen’s test cases

UPDATE 15/10/2015: solutions (spoiler alert!)


Auto-convert field to uppercase

This is just a quick note for my future reference. I needed all items with the class “uppercase” to be converted to uppercase, and I thought it would work with just some CSS:

.uppercase { text-transform:uppercase; }

This makes the items appear uppercase, but when the page is posted it actually sends the values exactly as the user typed. They’d type in “lower“, it looks like “LOWER” on screen, but gets posted as “lower“.

In many cases I could just convert the value in my PL/SQL code, but in cases where I was using Apex tabular forms, I don’t know a simple way to intercept the values before the insert occurs.

To solve this I added this to the page’s Execute when Page Loads:

//the item looks uppercase but the internal value
//is still lowercase
$(document).on('change','.uppercase',function(){
  var i = "#" + $(this).attr("id");
  $(i).val( $(i).val().toUpperCase() );
});

Or, even better, add this to the application’s global javascript file:

$(document).ready(function() {
  $(document).on('change','.uppercase',function(){
    var i = "#" + $(this).attr("id");
    $(i).val( $(i).val().toUpperCase() );
  });
});