Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







APEX and table column default values

Oracle Tips by Burleson Consulting

Using default values for lookup table columns

Many DBA's will design databases with built-in audit trials, and lookup tables are no exception.  In APEX lookup tables appears as "List of Values" (LOV) in a drop-down column.

Internally, we must design the tables and the end user interface (EUI) with these features:

  • We must store a complete historical record of row changes - Assume that we adopt a "current table ==>  history table" strategy where we enhance the SQL update command with a pre-process.  Before we change a row, we use a "before update" trigger to archive all "old" row values (similar to the BEFORE (BFOR) row image).  We write the old row values to a corresponding history table, where the columns are identical, and the only difference is the suffix on the table_name of "_history" (and use the 10h rowscn pseudocolumn to keep the archive time).

  • The audits must be unobtrusive to the end-user - All values for dates and people (last_update_by, created_by) will be supplied by Oracle, removing all coding from the APEX application.

For example, here we see a listing of a "Judge Name " lookup table.  Note that there is only one data column "Magistrate Name", and everything else is audit trail supplements:

So, what Oracle table defaults can we use to simplify the application DML and ensure that auditing is done at the Oracle database level? 

Oracle DDL Triggers for auditing

Here we create an Oracle DML (table-level) trigger to set all of the "created_by", "updated_by", date_created, and last_updated columns:

  if (inserting) then
      :new.created_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
      :new.created_date := sysdate;
      :new.last_update_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
      :new.last_update_date := sysdate;
      if (:old.effective_date is null) then
         :new.effective_date := sysdate;
      end if;
      if (:old.expiry_date is null) then
          :new.expiry_date := to_date('01-Jan-4000','DD-Mon-YYYY');
      end if;
  elsif (updating) then
      :new.last_update_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
      :new.last_update_date := sysdate;
  end if;
end ;

The delete portion of the htmldb code must set the update columns and the expiry column.

  :new.last_update_by := HTMLDB_CUSTOM_AUTH.GET_USERNAME;
  :new.last_update_date := sysdate;
  if (expiry_date = to_date('01-Jan-4000','DD-Mon-YYYY') ) then
       :new.expiry_date := sysdate;

This sets the delete date to sysdate unless the user sets a date for expiring the row.

Enforced application-level DML

This approach to table default values has the nice side effect that you cannot update from outside of htmldb because HTMLDB_CUSTOM_AUTH.GET_USERNAME returns a null, which is not allowed.  To insert/update from OEM or a direct logon requires that the trigger by
disabled first.

  2      SET "AIRLINE_NAME" = 'Garmany Airways Update2'
  3      WHERE  rowid = '*BARBA2oWR2FybWFueSBBaXJ3YXlzIFVwZGF0Zf4';
ERROR at line 1:
ORA-01407: cannot update

APEX support:

For APEX development support just call to gat an Oracle Certified professional for all APEX development projects.

APEX book and code samples:

Easy Oracle HTML-DB Application Express
Create Dynamic HTML with Oracle

Includes online APEX code depot

Buy it now for 30% off - Only $27.95




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational