The Templating Way

Today, grasshopper, you will learn the Way of the Template. The Templating Way is the path by which complex output is produced in a harmonious fashion.

The Templating Way does not cobble a string together from bits and pieces in linear fashion.

htp.p('<HTML><HEAD><TITLE>'||:title
||'</TITLE></HEAD><BODY>'
||:body||'</BODY></HTML>');

The Templating Way separates the Template from the Substitutions; by this division is harmony achieved.

DECLARE
  template VARCHAR2(200)
  := q'[
       <HTML>
        <HEAD>
         <TITLE> #TITLE# </TITLE>
        </HEAD>
        <BODY> #BODY# </BODY>
       </HTML>
      ]';
BEGIN
  htp.p(
    REPLACE( REPLACE( template
    ,'#TITLE#', :title)
    ,'#BODY#',  :body)
    );
END;

It is efficient – each substitution expression is evaluated once and once only, even if required many times within the template.

The Templating Way makes dynamic SQL easy to write and debug. It makes bugs shallower.

SELECT REPLACE(REPLACE(REPLACE(q'[
  CREATE OR REPLACE TRIGGER #OWNER#.#TABLE#_BI
  BEFORE INSERT ON #OWNER#.#TABLE#
  FOR EACH ROW
  BEGIN
    IF :NEW.#COLUMN# IS NULL THEN
      SELECT #TABLE#_SEQ.NEXTVAL
      INTO :NEW.#COLUMN#
      FROM DUAL;
    END IF;
  END;
]', '#OWNER#', USER)
  , '#TABLE#', cc.table_name)
  , '#COLUMN#', cc.column_name) AS ddl
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_type = 'P'
AND c.constraint_name = cc.constraint_name
AND cc.column_name like '%NO';

The Templating Way is simple, but looks complex to the uninitiated. It is readable, and affords maintainability.


A fascinating SQL problem

Can you solve this problem in SQL – i.e. without resorting to a procedural solution?

SQL combine multiple identifiers to create a group id for duplicate records

“I have a data extract with three different identifiers: A, B, C
Each identifier may appear in more than one row, and each row may have one or more of these three identifiers (i.e the column is populated or null).
I want to be able to group all records that have any combination of either A, B or C in common and assign them the same group id.
Extract table showing what the eventual groups should be:

A    | B    | C    | Group
====   ====   ====   =====
p      NULL   NULL   1
p      r      NULL   1
q      NULL   NULL   2
NULL   r      NULL   1
NULL   NULL   s      2
q      NULL   s      2

So, the input data is a table with three columns (A, B, and C), some of which are NULL. The output is a third column, “Group”, which will be assigned a number which classifies the row into a “group”. Each group will be distinct in that none of its members will have a value in A, B or C that appears in any row in any other group.

This question is fascinating because it cannot be solved, I believe, without some form of iteration. If I get the row (p) along with (q), they are in two different groups; but, if I add the row (p,q), all of a sudden my original rows are now in the same group along with the new row.

The solution will probably have to examine each row in consideration with the entire record set – an operation of O(n^2), if my understanding of CS theory is correct. I suspect a solution using at least a CTE and/or the MODEL clause will be required.

UPDATE:
An elegant solution, using a hierarchical query and Oracle’s CONNECT_BY_ROOT function, has been posted by Vincent Malgrat.


Priority #1: Keep it simple

Every place has a different way of assigning priority and/or severity to defect reports – some bigger places have many different ways (unfortunately). I’ve not been subjected to Prince2 training so here’s my take on this subject.

I reckon, the simpler the scheme, the more likely it will be used consistently. Every defect should have just a single priority/severity (call it what you will): Critical, High, Medium or Low.

  • Critical – problem significantly affects ability to test the system; “showstopper” – all other work to be delayed until the issue is resolved – an example might be “unable to log in”; “screen x opens with error every time”; “function y causes my computer to explode”
  • High – problem affects critical functionality; should be fixed as a matter of priority over other issues – an example would be “error x always/often occurs at process point y”
  • Medium – functionality not working as per specified requirements; must eventually be fixed (at least before Go Live) – an example would be “default value not being set correctly”; “navigation does not work correctly”
  • Low – cosmetic issue; “nice to have” function; or error/warning occurs very infrequently but doesn’t significantly affect correct processing; ok to Go Live if not fixed

That’s it. Notice how each category is unambiguous in what it means to the developers, testers and others. I’d expect a system to normally have mostly Medium issues, several Highs, hopefully no Criticals, and maybe some Lows. I’d expect some issues to be reclassified up or down as they are assessed, as developers negotiate with the testers and business reps.

I’m certain that there’s all sorts of great reasons why someone needs more levels, or needs to separate the “priority” concept from the “severity” or “impact” concepts, but to my mind there’s not a lot gained from forcing all your testers, developers, and change managers to learn a complicated system, and classify and update their records. When you need a 2D or 3D matrix of priority vs severity vs whatever printed and posted on your cubicle wall, it’s time to ask, “is all this really necessary?”.

Keep it simple, and everyone will not only use it, everyone else will understand it.

P.S. did you notice that Apex’s builtin “feedback” feature only has one level? It’s either a bug report, or it’s not (e.g. an enhancement request or comment). I love that.


Current Record Visual Attribute problem

If you have a multi-record block, and you use the handy Current Record Visual Attribute (CRVA) to set, say, a different background colour on all the items in the currently selected record, you may have run into this problem.

If you want to conditionally switch the visual attribute for certain items, at the item instance (i.e. record) level, this causes the CRVA to be overwritten, and the current record won’t be consistently highlighted. To get around this problem, a bit more code is required.

For example, let’s say you have a multi-record block called EMP, and it has two items that are sometimes gray, sometimes white – SALARY and ROLE. You have defined the following visual attributes:
EDITABLE_ITEM – white background
READONLY_ITEM – gray background
CURRENT_RECORD – blue background

Package Specification

PACKAGE EMP_BLOCK IS
PROCEDURE highlight_current_record;
PROCEDURE when_clear_block;
PROCEDURE when_remove_record;
END EMP_BLOCK;

Package Body

PACKAGE EMP_BLOCK IS

record_highlighted INTEGER;

PROCEDURE highlight_current_record IS
  rec INTEGER := GET_BLOCK_PROPERTY('EMP', CURRENT_RECORD);
  PROCEDURE set_visattr
    (itemn IN VARCHAR2
    ,rec IN NUMBER
    ,visattr IN VARCHAR2) IS
  BEGIN
    IF visattr IS NULL THEN
      -- (we could, if needed, make this more intelligent about
      -- detecting whether the record is NEW/INSERT/CHANGED,
      -- and examine the INSERT_ALLOWED/UPDATE_ALLOWED
      -- properties accordingly)
      IF GET_ITEM_INSTANCE_PROPERTY
        (itemn
        ,record_highlighted
        ,UPDATE_ALLOWED) = 'TRUE' THEN
        set_visattr(itemn, record_highlighted, 'EDITABLE_ITEM');
      ELSE
        set_visattr(itemn, record_highlighted, 'READONLY_ITEM');
      END IF;
    ELSE
      SET_ITEM_INSTANCE_PROPERTY
        (itemn
        ,rec
        ,VISUAL_ATTRIBUTE
        ,visattr);
    END IF;
  END set_visattr;
BEGIN
  -- Note: if record_highlighted is null, then no record
  -- is currently highlighted
  IF rec != record_highlighted THEN
    --un-highlight the record that was highlighted
    set_visattr('EMP.SALARY', record_highlighted);
    set_visattr('EMP.ROLE', record_highlighted);
  END IF;
  --highlight the newly selected record
  set_visattr('EMP.SALARY', rec, 'CURRENT_RECORD');
  set_visattr('EMP.ROLE', rec, 'CURRENT_RECORD');
  record_highlighted := rec;
END highlight_current_record;

PROCEDURE when_clear_block IS
BEGIN
  record_highlighted := NULL;
END when_clear_block;

PROCEDURE when_remove_record IS
BEGIN
  IF record_highlighted = :SYSTEM.TRIGGER_RECORD THEN
    record_highlighted := NULL;
  END IF;
END when_remove_record;

END EMP_BLOCK;

Block-level triggers on EMP:

when-new-record-instance

EMP_BLOCK.highlight_current_record;

when-clear-block

EMP_BLOCK.when_clear_block;

when-remove-record

EMP_BLOCK.when_remove_record;

Also, whenever your code modifies the UPDATE_ALLOWED property on SALARY or ROLE, it must then call EMP_BLOCK.highlight_current_record again.


Forms Library: PKG_FORM

This is just a post to document a standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome.

“No warranty is express or implied about the suitability of this code for any purpose.”

Specification

PACKAGE PKG_FORM IS

PROCEDURE centre_window (windown IN VARCHAR2);
PROCEDURE check_record_is_saved;
PROCEDURE commit;
FUNCTION current_record (blockn IN VARCHAR2) RETURN INTEGER;
FUNCTION current_record_status (blockn IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE delay_action (timern IN VARCHAR2);
PROCEDURE set_current_record_status
  (blockn IN VARCHAR2
  ,istatus IN NUMBER);
PROCEDURE set_mdi_window_title (clock IN BOOLEAN := TRUE);

-- gets the date/time from the app server
FUNCTION system_datetime RETURN DATE;

END PKG_FORM;

Package Body

PACKAGE PKG_FORM IS

cAPP_TITLE CONSTANT VARCHAR2(100) := '[insert app title here]';

PROCEDURE centre_window (windown IN VARCHAR2) IS
  cMAIN_WINDOW CONSTANT VARCHAR2(100) := '[insert main window name here]';
  x NUMBER;
  y NUMBER;
  main_win WINDOW := FIND_WINDOW(cMAIN_WINDOW);
  window_id WINDOW := FIND_WINDOW(windown);
BEGIN
  x := GET_WINDOW_PROPERTY(main_win,X_POS)
     + (GET_WINDOW_PROPERTY(main_win,WIDTH )
        - GET_WINDOW_PROPERTY(window_id,WIDTH )) / 2;
  y := GET_WINDOW_PROPERTY(main_win,Y_POS)
     + (GET_WINDOW_PROPERTY(main_win,HEIGHT)
        - GET_WINDOW_PROPERTY(window_id,HEIGHT)) / 2;
  SHOW_WINDOW(window_id, x, y);
END centre_window;

PROCEDURE check_record_is_saved IS
BEGIN
  IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
    PKG_MESSAGE.error('Please save or cancel your changes first.');
  END IF;
END check_record_is_saved;

PROCEDURE commit IS
  msglevel VARCHAR2(2) := :SYSTEM.MESSAGE_LEVEL;
BEGIN
  msg('PKG_FORM.commit');
  -- set message level to avoid FRM-40401 "No changes to save."
  :SYSTEM.MESSAGE_LEVEL := '5';
  COMMIT_FORM;
  :SYSTEM.MESSAGE_LEVEL := msglevel;
  IF :SYSTEM.FORM_STATUS != 'QUERY' THEN
    PKG_MESSAGE.error('Unable to save changes.');
  END IF;
END commit;

FUNCTION current_record (blockn IN VARCHAR2) RETURN INTEGER IS
BEGIN
  RETURN GET_BLOCK_PROPERTY(blockn, CURRENT_RECORD);
END current_record;

FUNCTION current_record_status (blockn IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN GET_RECORD_PROPERTY(
    GET_BLOCK_PROPERTY(blockn, CURRENT_RECORD),
    blockn,
    STATUS);
END current_record_status;

PROCEDURE delay_action (timern IN VARCHAR2) IS
-- requires a suitable WHEN-TIMER-EXPIRED form-level trigger
  timer_id TIMER;
BEGIN
  IF ID_NULL(FIND_TIMER(timern)) THEN
    msg('CREATE_TIMER('||timern||')');
    timer_id := CREATE_TIMER(timern,1,NO_REPEAT);
  END IF;
END delay_action;

PROCEDURE set_current_record_status
  (blockn IN VARCHAR2
  ,istatus IN NUMBER) IS
BEGIN
  SET_RECORD_PROPERTY(
     GET_BLOCK_PROPERTY(blockn, CURRENT_RECORD),
     blockn,
     STATUS,
     istatus);
END set_current_record_status;

PROCEDURE set_mdi_window_title (clock IN BOOLEAN := TRUE) IS
  --scaling factor is the number of milliseconds in a second
  cSCALING_FACTOR CONSTANT NUMBER := 1000;
  --don't update the time more than once every 10 seconds
  cMIN_UPDATE_FREQUENCY CONSTANT NUMBER := 10;
  date_time DATE;
  timer_id TIMER;
  seconds PLS_INTEGER;
  ms PLS_INTEGER;
BEGIN
  IF clock THEN
    date_time := system_datetime;
    SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, TITLE,
	        cAPP_TITLE || ' - '
	        || TO_CHAR(date_time,'Dy FMDD Mon YYYY HHFM:MIpm'));
    IF ID_NULL(FIND_TIMER('CLOCK')) THEN
      --get the seconds portion of the current time
      seconds := (date_time - TRUNC(date_time,'MI')) * 86400;
      --update the time at the end of the minute (or thereabouts)
      ms := GREATEST((61 - seconds) * cSCALING_FACTOR
	         ,cMIN_UPDATE_FREQUENCY * cSCALING_FACTOR);
      timer_id := CREATE_TIMER('CLOCK', ms, NO_REPEAT);
    END IF;
  ELSE
    SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, TITLE, cAPP_TITLE);
  END IF;
END set_mdi_window_title;

FUNCTION system_datetime RETURN DATE IS
-- gets the date/time from the app server
-- should perform better than calling SYSDATE all the time
-- WARNING: this assumes that the builtin date format includes the time component!
BEGIN
  RETURN TO_DATE(:SYSTEM.EFFECTIVE_DATE,
    GET_APPLICATION_PROPERTY(BUILTIN_DATE_FORMAT));
END system_datetime;

END PKG_FORM;

Example form-level trigger: when-timer-expired

DECLARE
  timern VARCHAR2(100) := GET_APPLICATION_PROPERTY(TIMER_NAME);
BEGIN
  msg('when-timer-expired ' || timern);
  CASE timern
    WHEN 'CLOCK' THEN
      PKG_FORM.set_mdi_window_title;
    WHEN 'PKGNAME_XYZ' THEN
      PKGNAME.xyz;
    -- ... etc. ...
  ELSE
    PKG_MESSAGE.error('Unexpected timer name: ' || timern, 'System Error');
  END CASE;
END;

Forms Library: PKG_ITEM

This is just a post to document a standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome.

“No warranty is express or implied about the suitability of this code for any purpose.”

Specification
The main point of this package is to (a) easily make form items more proactive regarding business requirements, e.g. by making it simple to make items conditionally editable, readonly or mandatory; and (b) mitigate the pain of diagnosing bugs with the way items work (by logging when most of these procedures are called).

PACKAGE PKG_ITEM IS

-- set item to value, without changing item state if possible
PROCEDURE assign (itemn IN VARCHAR2, value IN VARCHAR2);

FUNCTION current_record (itemn IN VARCHAR2) RETURN INTEGER;
PROCEDURE disable_button (itemn IN VARCHAR2);
PROCEDURE enable_button (itemn IN VARCHAR2);
PROCEDURE go_next (itemn IN VARCHAR2);
PROCEDURE go_prev (itemn IN VARCHAR2);
PROCEDURE hide (itemn IN VARCHAR2);
FUNCTION is_navigable (itemn IN VARCHAR2) RETURN BOOLEAN;
PROCEDURE set_editable
  (itemn    IN VARCHAR2
  ,rec      IN INTEGER  := NULL
  ,editable IN BOOLEAN  := TRUE
  ,set_null IN BOOLEAN  := FALSE);
PROCEDURE set_readonly (itemn IN VARCHAR2, rec IN INTEGER := NULL);
PROCEDURE set_required (itemn IN VARCHAR2, rec IN INTEGER := NULL);
PROCEDURE set_valid (itemn IN VARCHAR2);
PROCEDURE set_visattr (itemn IN VARCHAR2, currec IN BOOLEAN);
PROCEDURE show (itemn IN VARCHAR2);

END PKG_ITEM;

Package Body

PACKAGE PKG_ITEM IS

cVISATTR_NORMAL CONSTANT VARCHAR2(100) := 'NORMAL_ITEM';
cVISATTR_CURRENT CONSTANT VARCHAR2(100) := 'CURRENT_RECORD';
cVISATTR_DISPLAY CONSTANT VARCHAR2(100) := 'DISPLAY_ITEM';
cVISATTR_REQUIRED CONSTANT VARCHAR2(100) := 'REQUIRED_ITEM';

PROCEDURE assign (itemn IN VARCHAR2, value IN VARCHAR2) IS
-- set item to value, without changing item state if possible
BEGIN
  IF NAME_IN(itemn) != value
    OR (NAME_IN(itemn) IS NULL AND value IS NOT NULL)
    OR (NAME_IN(itemn) IS NOT NULL AND value IS NULL) THEN
    PKG_DEBUG.msg(itemn || ':=' || value);
    COPY(value, itemn);
  END IF;
END assign;

FUNCTION current_record (itemn IN VARCHAR2) RETURN INTEGER IS
--gets the record number for the block for the given item
BEGIN
  RETURN GET_BLOCK_PROPERTY
    (SUBSTR(itemn,1,INSTR(itemn,'.')-1)
    ,CURRENT_RECORD);
END current_record;

PROCEDURE disable_button (itemn IN VARCHAR2) IS
BEGIN
  msg('PKG_ITEM.disable_button('||itemn||')');
  IF :SYSTEM.CURSOR_ITEM = UPPER(itemn) THEN
    DO_KEY('NEXT_ITEM');
    check_package_failure;
  END IF;
  SET_ITEM_PROPERTY(itemn, ENABLED, PROPERTY_FALSE);
END disable_button;

PROCEDURE enable_button (itemn IN VARCHAR2) IS
  item_id ITEM := FIND_ITEM(itemn);
BEGIN
  msg('PKG_ITEM.enable_button('||itemn||')');
  SET_ITEM_PROPERTY(item_id, ENABLED, PROPERTY_TRUE);
  SET_ITEM_PROPERTY(item_id, NAVIGABLE, PROPERTY_TRUE);
END enable_button;

PROCEDURE go_next (itemn IN VARCHAR2) IS
--This is used when doing a GO_ITEM in order to implement a NEXT_ITEM.
--This will issue a NEXT_ITEM if the item we've just gone to
--is not currently navigable on the current record.
BEGIN
  msg('PKG_ITEM.go_next('||itemn||')');
  GO_ITEM(itemn);
  check_package_failure;
  IF GET_ITEM_INSTANCE_PROPERTY(:SYSTEM.CURSOR_ITEM
	,GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK, CURRENT_RECORD)
	,NAVIGABLE) = 'FALSE' THEN
    NEXT_ITEM;
    check_package_failure;
  END IF;
END go_next_if_not_navigable;

PROCEDURE go_prev (itemn IN VARCHAR2) IS
--This is used when doing a GO_ITEM in order to implement a PREVIOUS_ITEM.
--This will issue a PREVIOUS_ITEM if the item we've just gone to
--is not currently navigable on the current record.
BEGIN
  msg('PKG_ITEM.go_prev('||itemn||')');
  GO_ITEM(itemn);
  check_package_failure;
  IF GET_ITEM_INSTANCE_PROPERTY(:SYSTEM.CURSOR_ITEM
	,GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK, CURRENT_RECORD)
	,NAVIGABLE) = 'FALSE' THEN
    PREVIOUS_ITEM;
    check_package_failure;
  END IF;
END go_prev_if_not_navigable;

PROCEDURE hide (itemn IN VARCHAR2) IS
BEGIN
  msg('PKG_ITEM.hide('||itemn||')');
  IF :SYSTEM.CURSOR_ITEM = UPPER(itemn) THEN
    NEXT_ITEM;
    check_package_failure;
  END IF;
  SET_ITEM_PROPERTY(itemn, VISIBLE, PROPERTY_FALSE);
END hide;

FUNCTION is_navigable (itemn IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  RETURN GET_ITEM_PROPERTY(itemn, NAVIGABLE) = 'TRUE';
END is_navigable;

PROCEDURE set_editable
  (itemn    IN VARCHAR2
  ,rec      IN INTEGER  := NULL
  ,editable IN BOOLEAN  := TRUE
  ,set_null IN BOOLEAN  := FALSE) IS
  item_id ITEM;
BEGIN
  msg('PKG_ITEM.set_editable('||itemn||','||rec||')');
  IF editable THEN
    item_id := FIND_ITEM(itemn);
    IF rec IS NULL THEN
      IF :SYSTEM.CURSOR_ITEM != UPPER(itemn) THEN
        SET_ITEM_PROPERTY(item_id, VISIBLE, PROPERTY_TRUE);
        SET_ITEM_PROPERTY(item_id, ENABLED, PROPERTY_TRUE);
      END IF;
      SET_ITEM_PROPERTY(item_id, NAVIGABLE, PROPERTY_TRUE);
      SET_ITEM_PROPERTY(item_id, INSERT_ALLOWED, PROPERTY_TRUE);
      SET_ITEM_PROPERTY(item_id, UPDATE_ALLOWED, PROPERTY_TRUE);
      SET_ITEM_PROPERTY(item_id, VISUAL_ATTRIBUTE, cVISATTR_NORMAL);
    ELSE
      SET_ITEM_INSTANCE_PROPERTY(item_id, rec, NAVIGABLE, PROPERTY_TRUE);
      SET_ITEM_INSTANCE_PROPERTY(item_id, rec, INSERT_ALLOWED, PROPERTY_TRUE);
      SET_ITEM_INSTANCE_PROPERTY(item_id, rec, UPDATE_ALLOWED, PROPERTY_TRUE);
      SET_ITEM_INSTANCE_PROPERTY(item_id, rec, VISUAL_ATTRIBUTE, cVISATTR_NORMAL);
    END IF;
  ELSE
    set_readonly(itemn, rec);
    --clear any data the user may have entered into the item (current record only)
    IF set_null AND NAME_IN(itemn) IS NOT NULL THEN
      msg(itemn||':=NULL');
      COPY(NULL, itemn);
    END IF;
  END IF;
END set_editable;

PROCEDURE set_readonly (itemn IN VARCHAR2, rec IN INTEGER := NULL) IS
  item_id ITEM := FIND_ITEM(itemn);
BEGIN
  msg('PKG_ITEM.set_readonly('||itemn||','||rec||')');
  IF rec IS NULL THEN
    SET_ITEM_PROPERTY(item_id, NAVIGABLE, PROPERTY_FALSE);
    SET_ITEM_PROPERTY(item_id, INSERT_ALLOWED, PROPERTY_FALSE);
    SET_ITEM_PROPERTY(item_id, UPDATE_ALLOWED, PROPERTY_FALSE);
    SET_ITEM_PROPERTY(item_id, VISUAL_ATTRIBUTE, cVISATTR_DISPLAY);
  ELSE
    SET_ITEM_INSTANCE_PROPERTY(item_id, rec, NAVIGABLE, PROPERTY_FALSE);
    SET_ITEM_INSTANCE_PROPERTY(item_id, rec, INSERT_ALLOWED, PROPERTY_FALSE);
    SET_ITEM_INSTANCE_PROPERTY(item_id, rec, UPDATE_ALLOWED, PROPERTY_FALSE);
    SET_ITEM_INSTANCE_PROPERTY(item_id, rec, VISUAL_ATTRIBUTE, cVISATTR_DISPLAY);
  END IF;
END set_readonly;

PROCEDURE set_required (itemn IN VARCHAR2, rec IN INTEGER := NULL) IS
BEGIN
  msg('PKG_ITEM.set_required('||itemn||','||rec||')');
  IF rec IS NULL THEN
    SET_ITEM_PROPERTY(itemn, REQUIRED, PROPERTY_TRUE);
    SET_ITEM_PROPERTY(itemn, VISUAL_ATTRIBUTE, cVISATTR_REQUIRED);
  ELSE
    SET_ITEM_INSTANCE_PROPERTY(itemn, rec, REQUIRED, PROPERTY_TRUE);
    SET_ITEM_INSTANCE_PROPERTY(itemn, rec, VISUAL_ATTRIBUTE, cVISATTR_REQUIRED);
  END IF;
END set_required;

PROCEDURE set_valid (itemn IN VARCHAR2) IS
BEGIN
  msg('PKG_ITEM.set_valid('||itemn||')');
  SET_ITEM_PROPERTY(itemn, ITEM_IS_VALID, PROPERTY_TRUE);
END set_valid;

PROCEDURE set_visattr (itemn IN VARCHAR2, currec IN BOOLEAN) IS
  rec INTEGER;
BEGIN
  msg('PKG_ITEM.set_visattr('||itemn||')');
  rec := item_current_record(itemn);
  IF currec THEN
    SET_ITEM_INSTANCE_PROPERTY(itemn, rec, VISUAL_ATTRIBUTE, cVISATTR_CURRENT);
  ELSE
    SET_ITEM_INSTANCE_PROPERTY(itemn, rec, VISUAL_ATTRIBUTE, cVISATTR_NORMAL);
  END IF;
END set_current_item_visattr;

PROCEDURE show (itemn IN VARCHAR2) IS
BEGIN
  msg('PKG_ITEM.show('||itemn||')');
  SET_ITEM_PROPERTY(itemn, VISIBLE, PROPERTY_TRUE);
END show;

END PKG_ITEM;

Example usage:

PKG_ITEM.set_editable('EMP.SALARY'
  ,rec      => PKG_FORM.current_record('MYBLOCK')
  ,editable => (:EMP.ROLE != 'CEO')
  );

The above example makes the SALARY item readonly if the employee’s role is ‘CEO’ – you would call this from the post-query trigger on the block, so on rows where the role is not ‘CEO’, SALARY will be editable.

Setting the visual attributes at the row level like this on multi-row blocks, however, has one side-effect. If you have a Current Record Visual Attribute (CRVA) set on the block (or the form), this code will overwrite that, so the CRVA will not be effective for these items. There is a workaround (to be published).


Negative, Captain

Seen in the wild:

... WHERE substr(amount,0, 1) != '-'

If you wanted to query a table of monetary transactions for any refunds (i.e. where the transaction amount is negative), how would you do it? Perhaps you’d think about avoiding problems that might occur if the default number format were to change, hm?

(before you say it: no, there is no index on amount, so it wasn’t a misguided attempt to avoid an index access path…)


I’m off to InSync10

Perhaps you were introduced to APEX in a similar way to me.

When I started using APEX, I was pleased to see a good selection of simple themes and templates I could use straight away. There was no need to worry about creating a consistent look-and-feel across my applications, I could dive right into building the features and quickly produce good-looking prototypes and finished products very quickly.

As I became familiar with the product, I soon started exploring those sections in Shared Components entitled “Themes” and “Templates”. They looked intimidating at first, but as I fiddled with them I soon learned their usefulness and power – and soon was able to customise the look and feel of my web sites, so that they no longer looked so generic and “APEX-ey”.

I’ll be speaking on APEX Themes and Templates at the InSync 2010 conference in Melbourne, 16-17 August. This will be my first time speaking away from my home base in Perth.

The draft program has been published, and it looks like it will be huge. Over 130 presentations are already scheduled, my primary interest being the “Developer” and “Database and Technology” streams, which include the following:

  • Meet the CBO in Version 11g (Penny Cookson)
  • DEV Super Session: PL/SQL Developer Quiz Thyself (Steven Feuerstein)
  • Indexing New Features in Oracle 11g Release 1 and Release 2 (Richard Foote)
  • Oracle SQL High Performance Tuning (Guy Harrison)
  • (Obscure) Tools of the Trade for Tuning Oracle SQLs (Tony Jambu)
  • DBA Super Session: Reorganising Objects – when and how, and DEV Super Session: Things You Know (Tom Kyte)
  • 11g Features for Developers, and Partitioning (Connor McDonald)
  • DEV Super Session: Real Application Testing – I Love It (Mogens Norgaard)

…and that’s just some of the sessions I’m particularly interested in. Unfortunately I’m going to miss a number of them because they run concurrently – I’ll have some choices to make closer to the day.

Back on the topic of APEX, I’ve been playing with 4.0 in a VM, and comparing it with the previous version (3.2.1). Nothing really startling, but it’s interesting nonetheless to see how the themes and templates have been improved. Overall the new version looks quite good – I just need to find time to upgrade my “real” instances.

I hope to see you in Melbourne!


If at first you don’t succeed… it’s impossible.

How many times have you tried something, got either an error or unexpected results, and decided what you were trying to do was not possible? Have you later on discovered someone quietly doing the impossible?

I think this phenomenon is a form of the “correlation-implies-causation” fallacy.

Unfortunately, this seems to happen too often, if the kind of questions I see quite often are any guide. A recent example is: “Why cannot I select from more than one table in Oracle?”. Here, the author seems to have followed the following thought process:

  1. “SELECT * FROM table1” returns some rows.
  2. “SELECT * FROM table1, table2” returns no rows.
  3. Therefore, you can’t query more than one table in one SQL statement in Oracle.

In this case, the writer had not realised that table2 had no rows in it; what complicated things somewhat was that in one session, the second query was returning rows – because he’d inserted some rows into table2 in that session but hadn’t issued a COMMIT, so those rows were not visible by other sessions.

For a person inexperienced in SQL or Oracle, this sort of mistake is forgivable; but I suspect we all make this sort of mistake quite often. I know I have!

When trying something new, it takes diligent research and testing to determine whether one’s approach is simply wrong, or if unrelated factors (e.g. getting the syntax wrong, or the environment is not set up correctly) are causing failure. This gets more tiresome and frustrating (a “gumption trap”, in Persig‘s parlance) when one was halfway through solving some other problem, and this unexpected problem gets in the way.

Sometimes you just have to go to bed and see if it becomes clearer the next day. If the problem persists, ask a question on StackOverflow!

P.S. if a Google search reveals “doing X is impossible”, ask “Why?”


Quick Draw Fail

It rarely pays to answer too quickly.

Yesterday’s question at the PL/SQL Challenge was about aliases for expressions in cursor-based record types.

DECLARE
   CURSOR profitable_internet_stocks
   IS
      SELECT SUM (share_price) FROM nasdaq_listings
       WHERE profits > 0 AND sector = 'INTERNET';
   few_and_far_in_between   profitable_internet_stocks%ROWTYPE;
BEGIN
   OPEN profitable_internet_stocks;
   FETCH profitable_internet_stocks
   INTO few_and_far_in_between;
   CLOSE profitable_internet_stocks;
   /*[display_statement]*/
END;

The question asked what code, inserted where the “display_statement” comment is, would display the result of the query. Without thinking too hard about it, I ticked the fourth option as correct:

DBMS_OUTPUT.PUT_LINE (few_and_far_in_between.sum_share_price);


I had this stupid idea that Oracle would step in and magically create an alias for the column*. A millisecond after clicking “Accept”, I realised I’d got it wrong. I’d assumed that at least one of the answers were likely to be correct, which is obviously not true.

* EDIT: Gary Myers has blogged about this too and explains that Oracle does, indeed, create an alias.