Generic Audit

Looking at the column comments on this table, I can sympathise with the poor soul who after painstakingly describing 100 columns just decided that it just wasn’t worth it…

table audit_history (column comments in quotes):

id number(18) “Unique identifier”
aud_table_name varchar2(30) “audit record table”
audit_action varchar2(50) “audit action e.g. update”
aud_timestamp date “timestamp of the audit”
aud_user_id varchar2(100) “user performing the action”
aud_col_1 varchar2(4000) “audit column 1”
aud_col_2 varchar2(4000) “audit column 2”
aud_col_3 varchar2(4000) “audit column 3”
… (etc.) …
aud_col_99 varchar2(4000) “audit column 99”
aud_col_100 varchar2(4000) “audit column 100”
aud_col_101 varchar2(4000)
aud_col_102 varchar2(4000)
… (etc.) …
aud_col_139 varchar2(4000)
aud_col_140 varchar2(4000)

Tip: Don’t let anyone even think about using this kind of design for their change history auditing requirements. It might look elegant to them, but it is not good. Just, don’t.

SQL Developer Wish List

I’m starting to come around to Oracle SQL Developer. At home I only use free software so that’s obviously a big plus, but at work I’m still using PL/SQL Developer (Allround Automations) and SQL*Plus.

These are the features I like best about these products:

SQL Developer:

  • Connection management
    • Connection browser
    • Multiple simultaneous connections
    • Password persistance
    • Quick connection switching within SQL worksheet
  • Object viewer
    • Easy Query management
    • Nicer plan viewer

PL/SQL Developer:

  • Faster startup, smaller memory footprint
  • Fully configurable object browser
    • Re-order categories
    • Create/modify/delete categories
    • Colour coding
  • Data browser
    • Edit data directly in grid
    • View data, including LOBs, e.g. RTF, XML, HTML, hex, etc.
  • Window management
    • Summary tab lists all windows, indicating which are unsaved or are currently running SQL
  • Query management
    • Runs DDL and DML in a second session, easy to cancel queries
  • Smart SQL and PL/SQL Editing
    • Context-sensitive menus
    • Smarter, context-sensitive code suggestions
    • Configurable SQL beautifier
  • Session browser, configurable

I’d like to see Oracle work on some of these features before I switch over at work. I’d also like to see SQL Developer able to export LOBs when exporting tables to XML.

Lost SQL Developer Connections

I upgraded Oracle SQL Developer from to, just for the heck of it. Unfortunately, all my saved connections had disappeared! After a text search I found the connections are stored in a file called IDEConnections.xml under the folder sqldeveloper\jdev\system\oracle.onlinedb. I copied this across to a new folder that had been created (oracle.onlinedb. and bingo they’re back again.

Bonus – now I know what file to back up if I want to restore my connections later on.

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.

  1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
  2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
    • Computation Item = F100_REFRESH_INTERVAL
    • Computation Point = On New Instance (e.g. On Login)
    • Computation Type = Static Assignment
    • Computation = (default number of seconds, e.g. 60)
  3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

  • HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

  • Display As = Select List with Submit
  • Region = (any region, I prefer the Breadcrumb region if one exists)
  • Label = Refresh
  • Source Used = Always, replacing any existing value in session state
  • Source Type = Item (application or page item name)
  • Maintain session state = Per session
  • Source value or expression = F100_REFRESH_INTERVAL
  • List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

  • Source Used = Always, replacing any existing value in session state
  • Source Type = PL/SQL Expression or Function
  • Maintain session state = Per session
  • Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.

Learning APEX

I’ve been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product – there’s more than meets the eye. At first I was dependent on the wizards (of which there are many) for just about everything except SQL and PL/SQL. When I came across problems I’d just delete and re-create the entire page – but now I’m more often able to find the source of a problem and fix it. I’ve found that having a good knowledge of SQL, PL/SQL, HTML and Cascading Style Sheets is very helpful when working with APEX.

Beware the wily SHOW_ALERT

What is the return value of SHOW_ALERT if the user doesn’t click one of the available buttons? According to the documentation (Forms 6i):

Displays the given alert, and returns a numeric value when the operator selects one of three alert buttons.
SHOW_ALERT (alert_id Alert);
SHOW_ALERT (alert_name VARCHAR2);
Returns a numeric constant corresponding to the button the operator selected from the alert. Button mappings are specified in the alert design.
If the operator selects… Form Builder returns

So for example, we can use this in code like this:

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen
  -- Cancel was chosen

The alert can have two or three buttons, depending on whether you set the Button 3 Label property. For example, you could have an alert named MY_ALERT with the following settings:

Message: Do you want to save the changes you have made?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

If the user clicks Yes, No or Cancel, the function returns one of the three ALERT_BUTTON constants. Simple enough. But what if the user decides to be different and clicks the “X” (close window button)? Alternatively, what if the user presses the “Esc” key on their keyboard?

Testing with Forms 6 ( reveals that the answer depends on whether you have just two buttons defined, or all three. These results are probably the same on other versions, but you should test this on your version to make sure.

If you have three buttons, both the Close Window button and the “Esc” key cause SHOW_ALERT to return ALERT_BUTTON3. If you only have two buttons, the “Esc” key still returns ALERT_BUTTON3, for some reason, whereas the Close Window button returns ALERT_BUTTON2! The moral of the story is, make sure your code handles all three of the ALERT_BUTTON alternatives correctly! Also, make sure that the last button (2nd or 3rd) really does mean “cancel”. A very bad (hypothetical) example would be:

Message: What would you like to do?
Button 1 Label: Create a new record
Button 2 Label: Modify this record
Button 3 Label: Delete all these records

This post seems quite popular, so I thought I’d add what I think are good examples for handling alerts in Forms:

1. Two-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: [blank]

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen, or Close button clicked
  -- ESC key pressed

2. Three-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen
  -- Cancel was chosen, or Close button clicked, or ESC key pressed

PL/SQL Maintenance Nightmares (Learning to avoid…)

I’ve worked on some new PL/SQL packages and Forms which work quite well. They are efficient in their use of resources, and are easy for me to debug and maintain. After some other developers have had to do some changes (some from changes to requirements, some from bugs I didn’t find), I’ve learned that the highly modular style of code I use is difficult to modify without introducing new bugs.

The modular style is great while developing; it reduces duplication and provides useful abstractions. When it comes to maintaining that code, however, a new developer has to read all that code and follow its tortuous logic around in order to debug it. It’s much easier to just patch the existing code to force it to work.

This is obviously not the best outcome; more new code is added, instead of the existing code being fixed. The module thus becomes more complex and unmaintainable. After some time, a new developer confronts a hodge-podge of duplicated functionality and cut-and-paste monstrosities, and exclaims, “Who wrote this rubbish?” – perhaps with justification.

What I’ve learned is that after developing a new module, I must go back and refactor it to improve maintainability. This includes a number of steps (this list is not intended to be exhaustive):

  1. Delete simple wrappers for library functions/procedures, and replace all calls to them with calls to the original library functions/procedures.
  2. Don’t use constants for internal codes (e.g. names of items and blocks) – use literals instead.
  3. If a fairly simple function or procedure is only called once throughout the final code – delete it and move the code to where it was called from; the exception is if this would make the calling code more difficult to read or understand.
  4. Code that generates dynamic SQL should set the SQL in entirety in single statements if possible, instead of building it procedurally. Another developer should be able to easily find the exact SQL that will be generated just by looking at the code, instead of having to mentally build it procedurally, or having to printline the SQL and run it to see what it generates.
  5. Add comments that document the philosophy of how the code has been laid out.

Some examples to illustrate the above:

  1. We have a library procedure called lk_item.lp_show_item (pc_item, pn_visible, pn_enabled) which is used to show, hide, enable, and/or disable a form item. I wanted to write code like the following: lk_item.lp_show_item(‘my_item’, some_boolean_expression, another_boolean_expression) but I couldn’t because the parameters require PROPERTY_TRUE or PROPERTY_FALSE, which are numbers. My first cut included the following wrapper for lk_item.lp_show_item:
    PROCEDURE cp_show_hide
    (pc_item IN VARCHAR2
    ,pb_visible IN BOOLEAN
    ,pb_enabled IN BOOLEAN) IS
    IF pb_value THEN
    END IF;

    In the end, however, I only called this procedure two times, and within a single procedure. So I deleted the wrapper procedure, and called lk_item.lp_show_item directly. I kept my Boolean expressions, but wrapped them in a locally declared function that did the translation.
    IF pb_value THEN
    END IF;

    CF_PROPERTY_TF(some expression),
    CF_PROPERTY_TF(some other expression));

  2. I had a procedure that populated a large number of items in the block in response to the user selecting a new record. Because the user can select a new record using any of several different methods (effectively, different search criteria), I used a parameter called “search mode” that is set to various constant strings, e.g. “GNL_ID”, “OFF_ID_DETAILS”, “COLLAR_NO”. Some of these strings were named after significant database table columns, some were generic – they all, however, simply differentiated slightly different conditions under which the procedure must operate.

    Initially I had a set of constants that took these values; however, because this code passed these values to a database package as well, the constants were defined in the database package as well. In the end the code was much simpler and easier to read by removing all the constants and just encoding the codes as literal strings.

  3. (no example)

  4. A first cut of the code might look like (this is a very simplified example):
    cp_lexical := ‘SELECT aaa, bee, cee ‘;
    IF (some expression) THEN
    cp_lexical := cp_lexical || ‘, dee FROM bla ‘;
    cp_lexical := cp_lexical || ‘FROM dee, eff ‘;
    END IF;
    IF (some other expression) THEN
    cp_lexical := cp_lexical
    || ‘, gee WHERE eee = eff AND gee = oh ‘;
    cp_lexical := cp_lexical
    || ‘, hat WHERE eee = oh AND ii = jay ‘;
    END IF;
    cp_lexical := cp_lexical
    || ‘ AND kay = ell AND emm = enn’;

    The final, more easily maintained code would look something like:
    IF (some expression)
    AND (some other expression) THEN
    cp_lexical := ‘SELECT aaa, bee, cee, dee ‘
    || ‘FROM bla, gee ‘
    || ‘WHERE eee = eff ‘
    || ‘AND gee = oh ‘
    || ‘AND kay = ell ‘
    || ‘AND emm = enn’;
    ELSIF (some expression) THEN
    cp_lexical := ‘SELECT aaa, bee, cee, dee ‘
    || ‘FROM bla, hat ‘
    || ‘WHERE eee = oh ‘
    || ‘AND ii = jay ‘
    || ‘AND kay = ell ‘
    || ‘AND emm = enn’;
    ELSIF (some other expression) THEN
    cp_lexical := ‘SELECT aaa, bee, cee ‘
    || ‘FROM dee, eff, gee ‘
    || ‘WHERE eee = eff ‘
    || ‘AND gee = oh ‘
    || ‘AND kay = ell ‘
    || ‘AND emm = enn’;
    cp_lexical := ‘SELECT aaa, bee, cee ‘
    || ‘FROM dee, eff, hat ‘
    || ‘WHERE eee = oh ‘
    || ‘AND ii = jay ‘
    || ‘AND kay = ell ‘
    || ‘AND emm = enn’;
    END IF;

    This doesn’t solve all the maintenance problems (i.e. if someone makes a change to one part of the code they might not know that they should make the same change everywhere else that code has been duplicated). However, it does make maintenance a bit easier because the code is easy to follow.

  5. I generally follow the following pattern when encapsulating logic in procedures and packages:
    • Code that populates data in items (e.g. in response to changes in other items) is encapsulated in procedures or packages named POPULATE_xxx.
    • Code that changes item properties (but not their values) is encapsulated in procedures or packages named SETUP_xxx.
    • Code that checks data entry errors is encapsulated in procedures or packages named VALIDATE_xxx.

    These conventions are documented so that another developer can more easily read the code; for example, a when-validate-item trigger might have code like this:

    The developer might still need to follow through all the code to debug it, but they might more easily see where the code should be. Hopefully they’ll recognise that validation code should go in cp_validate_something, rather than cp_populate_something.

Fun with copy-and-paste code

Came across this in a form (6i) to be run on a 9i db. Not only is this code about 33 lines of code too long and issues any number of unnecessary database queries, its name is quite unrelated to its intended function. Needless to say it was easily replaced with a single call to INSTR.

PROCEDURE alpha_check
(ref_in IN VARCHAR2
,ref_out OUT VARCHAR2) IS
-- Procedure included to distinguish
-- ref_in between ID or reference.
  l_alpha_char  VARCHAR2 (1);
  l_alpha_pos   NUMBER;
  l_found_pos   NUMBER;
  l_search_string VARCHAR2 (100) := ' ';

  CURSOR cur_get_next_alpha(N NUMBER) IS
  SELECT SUBSTR(l_search_string,N,1)
  FROM dual;

  CURSOR cur_check_for_alpha(C VARCHAR2)IS
  SELECT INSTRB(ref_in,C, 1)
  FROM dual;

  IF ref_in IS NULL THEN
    ref_out := 'X';

  FOR I IN 1..LENGTH(l_search_string) LOOP
    OPEN cur_get_next_alpha(I);
    FETCH cur_get_next_alpha
    INTO l_alpha_char;
    CLOSE cur_get_next_alpha;

    FOR J IN 1..LENGTH(ref_in) LOOP
      OPEN cur_check_for_alpha(l_alpha_char);
      FETCH cur_check_for_alpha
      INTO l_found_pos;
      CLOSE cur_check_for_alpha;

      IF l_found_pos > 0 THEN
        ref_out := 'N';
      END IF;

  ref_out := 'Y';
    pc_ref_out := 'X';

Looks like it may have been copied from the same source as “As bad as it gets”.

Generating test data that matches existing data

I’ve had to create test data a number of times, including data for tables that had mandatory foreign keys to existing tables. It was not feasible to just create new master rows for my test data; I wanted to refer to a random sample of existing data; but the code that generates the test data had to perform reasonably well, even though it had to pick out some random values from a very large table.

Solution? A combination of the new 10g SAMPLE operator, and DBMS_RANDOM. To illustrate:

(create a “very large table”)
SQL> create table t as
2 select rownum n, dbms_random.string(‘a’,30) v
3 from all_objects;

Table created.

SQL> select count(*) from t;


(get a random sample from the table)
SQL> select n, substr(v,1,30) from t sample(0.01)
2 order by dbms_random.value;

N SUBSTR(V,1,30)
———- ——————————
11852 xSsdmFtGqkymbKCFoZwUzNxpJAPwaV
8973 RGyNjqMfVayKdiKFGvLYuAFYUpIbCw
25295 eJJtoieSWtzUTIZXCbOLzmdmWHHPOy
1924 yZucJWgkFviAIeXiSCuNeUuDjClvxt
40646 wMTumPxfBMoAcNtVMptoPchILHTXJa

6 rows selected.

SQL> set serveroutput on

(Get a single value chosen at random)
SQL> declare
2 cursor cur_t is
3 select n from t sample(0.01)
4 order by dbms_random.value;
5 l number;
6 begin
7 open cur_t;
8 fetch cur_t into l;
9 dbms_output.put_line(l);
10 close cur_t;
11* end;
SQL> /

PL/SQL procedure successfully completed.

My test code would open the cursor, fetch as many values as it needed, and then close it. If the cursor ran out of values (e.g. the sample was too small for the desired amount of test data, which varied), my code just re-opened the cursor to fetch another set of random values from the large table.

The reason I sort the sample by dbms_random.value is so that if I only want one value, it is not weighted towards rows found nearer the start of the table.

Note: If I didn’t really care about the sample being picked at random from throughout the table, I could have just selected from the table “where rownum < n".