Forms Library: General bits and pieces

This is just a post to document a standard utility functions that I like to create in a Oracle Forms library, 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.”

I’ll only create those things which I find become generally useful for a particular site. If something’s not used, get rid of it. That said, most of these things I’ve created because I use them frequently.

PROCEDURE assert (truth IN BOOLEAN, module IN VARCHAR2) IS
BEGIN
  IF truth THEN
    NULL;
  ELSE --truth may be null or false
    PKG_MESSAGE.error('Assertion failed in ' || module);
  END IF;
END assert;
PROCEDURE check_package_failure IS
-- (this is generated automatically by Oracle Forms when a block
-- relation is created)
-- This should be called after various builtins that, instead of
-- doing the honourable thing (raising an exception), set
-- FORM_SUCCESS. These builtins are*:
--  CALL_FORM CALL_QUERY CANCEL_QUERY CLEAR_BLOCK CLEAR_FORM
--  CLEAR_ITEM CLEAR_RECORD COUNT_QUERY DELETE_RECORD DOWN
--  DO_KEY DUPLICATE_ITEM DUPLICATE_RECORD EDIT_TEXTITEM ENTER
--  ENTER_QUERY EXECUTE_QUERY EXECUTE_TRIGGER EXIT_FORM
--  FETCH_RECORDS FIRST_RECORD FORMS_DDL GO_BLOCK GO_FORM
--  GO_ITEM GO_RECORD HOST INSERT_RECORD LAST_RECORD
--  LIST_VALUES LOCK_RECORD LOGON LOGON_SCREEN LOGOUT
--  NEW_FORM NEXT_BLOCK NEXT_ITEM NEXT_FORM NEXT_KEY
--  NEXT_RECORD NEXT_SET OPEN_FORM PREVIOUS_BLOCK
--  PREVIOUS_FORM PREVIOUS_ITEM PREVIOUS_BLOCK UP
-- * if you notice any builtins missing from, or that should not be
--    included in this list - please let me know!
BEGIN
  IF NOT ( Form_Success ) THEN
    RAISE Form_Trigger_Failure;
  END IF;
END check_package_failure;
PROCEDURE msg (text IN VARCHAR2) IS
-- just a convenient wrapper for PKG_DEBUG.msg
BEGIN
  PKG_DEBUG.msg(text);
END msg;

PKG_DEBUG definition

Form-level trigger: pre-select

msg(:SYSTEM.LAST_QUERY);

Form-level trigger: on-error

msg('on-error ' || ERROR_TYPE || '-' || ERROR_CODE || ' ' || ERROR_TEXT);
-- add handlers here for errors that we don't want to spit the dummy on (i.e. handle gracefully)
CASE
WHEN ERROR_TYPE||'-'||ERROR_CODE IN
  ('FRM-41105' --"You cannot query records without a saved parent record."
  ) THEN
  -- the default message is a little obtuse - give a standard response
  MESSAGE('That function is not allowed here.', ACKNOWLEDGE);
  RAISE FORM_TRIGGER_FAILURE;

WHEN ERROR_TYPE||'-'||ERROR_CODE IN
  ('FRM-40202' --"Field must be entered."
  ,'FRM-40203' --"Field must be entered completely."
  ,'FRM-40207' --"Must be in range x to y"
  ,'FRM-40209' --"Field must be of form x."
  ,'FRM-40212' --"Invalid value for field x."
  ,'FRM-40356' --"Invalid number in example record. Query not issued."
  ,'FRM-40357' --"Invalid string in example record. Query not issued."
  ,'FRM-40358' --"Invalid date in example record. Query not issued."
  ,'FRM-40359' --"Invalid date or time in example record. Query not issued."
  ,'FRM-40501' --"ORACLE error: unable to reserve record for update or delete."
  ,'FRM-40654' --"Record has been updated by another user. Re-query to see change."
  ,'FRM-40657' --"Record changed or deleted by another user."
  ,'FRM-41106' --"You cannot create records without a parent record."
  ) THEN
  -- show the error message in a popup and raise FTF
  PKG_FORM.error(ERROR_TEXT);

WHEN ERROR_TYPE||'-'||ERROR_CODE
  BETWEEN 'FRM-50000' AND 'FRM-51000' THEN
  -- almost all the FRM-5xxxx errors are to do with data format issues, e.g. date
  -- fields, numbers, etc
  PKG_FORM.error(ERROR_TEXT);

WHEN ERROR_TYPE||'-'||ERROR_CODE IN
  ('FRM-40100' --"At first record."
  ,'FRM-40102' --"Record must be entered or deleted first."
  ,'FRM-40110' --"At first block."
  ,'FRM-40111' --"At last block."
  ,'FRM-40200' --"Field is protected against update."
  ,'FRM-40201' --"Field is full. Can't insert character."
  ,'FRM-40401' --"No changes to save."
  ,'FRM-40405' --"No changes to apply."
  ,'FRM-41026' --"Field does not understand operation." (e.g. F9 on item with no list)
  ,'FRM-41050' --"You cannot update this record."
  ,'FRM-41051' --"You cannot create records here."
  ) THEN
  -- just display the default error message on the status bar (or popup if >1 message in quick succession)
  MESSAGE(ERROR_TEXT, ACKNOWLEDGE);
  RAISE FORM_TRIGGER_FAILURE;
ELSE
  PKG_DEBUG.on_error;
END CASE;

Form-level trigger: on-message

msg('on-message ' || MESSAGE_TYPE || '-' || MESSAGE_CODE || ' ' || MESSAGE_TEXT);
-- trap some messages and replace with more user-friendly message
CASE
WHEN MESSAGE_TYPE||'-'||MESSAGE_CODE IN
  ('FRM-40350' --"Query caused no records to be retrieved."
  ) THEN
PKG_FORM.note('No matching receipts found.', 'Search Results');
WHEN MESSAGE_TYPE||'-'||MESSAGE_CODE IN
  ('FRM-41800' --"List of Values not available for this field."
  ,'FRM-40400' --"Transaction complete: %s records applied and saved."
  ) THEN
  -- the default message is user-friendly enough to show on the status bar
  MESSAGE(MESSAGE_TEXT, ACKNOWLEDGE);
ELSE
  PKG_DEBUG.on_message;
END CASE;

Forms Library: PKG_MESSAGE

This is just a post to document the 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.”

Forms Package Specification: PKG_MESSAGE

PACKAGE PKG_MESSAGE IS

PROCEDURE error
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Error');
PROCEDURE note
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Note');
PROCEDURE warning
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Warning');

END PKG_MESSAGE;

Forms Package Body: PKG_MESSAGE

PACKAGE PKG_MESSAGE IS

PROCEDURE error
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Error') IS
-- requires a suitable alert called "ERROR"
  n NUMBER;
BEGIN
  msg('PKG_MESSAGE.error('''||message||''','''||popup_title||''')');
  SET_ALERT_PROPERTY('ERROR', TITLE, popup_title);
  SET_ALERT_PROPERTY('ERROR', ALERT_MESSAGE_TEXT, message);
  n := SHOW_ALERT('ERROR');
  RAISE FORM_TRIGGER_FAILURE;
END error;

PROCEDURE note
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Note') IS
--requires a suitable alert "NOTE"
  n NUMBER;
BEGIN
  msg('PKG_MESSAGE.note('''||message||''','''||popup_title||''')');
  SET_ALERT_PROPERTY('NOTE', TITLE, popup_title);
  SET_ALERT_PROPERTY('NOTE', ALERT_MESSAGE_TEXT, message);
  n := SHOW_ALERT('NOTE');
END note;

PROCEDURE warning
  (message IN VARCHAR2
  ,popup_title IN VARCHAR2 := 'Warning') IS
-- requires a suitable alert "WARNING"
  n NUMBER;
BEGIN
  msg('PKG_MESSAGE.warning('''||message||''','''||popup_title||''')');
  SET_ALERT_PROPERTY('WARNING', TITLE, popup_title);
  SET_ALERT_PROPERTY('WARNING', ALERT_MESSAGE_TEXT, message);
  n := SHOW_ALERT('WARNING');
END warning;

END PKG_MESSAGE;

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.


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


My favourite 11g thing

For too long I’ve muddled my way through using the built-in “explain plan” feature of the IDE-that-shall-remain-nameless provided by the client; but now that they’ve successfully upgraded from 9i to 11g, I’m now in a much better place. No longer will I wonder which table each step in a plan refers to, or what filters are being applied where.

DBMS_XPLAN is the best!

--get plan for a query
set pagesize 0 linesize 999 heading off feedback off echo on
explain plan for
SELECT bla FROM mytable WHERE bla;
set echo off
select * from table(dbms_xplan.display(format=>'ALL'));
--get plan for cursor in cache
set pagesize 0 linesize 999 heading off feedback off
select * from table(dbms_xplan.display_cursor(
sql_id=>'fnumywukcm2ng', cursor_child_no=> 0, format=>'ALL'));

One insert or multiple inserts?


A colleague asked me a trick* question today which I failed 🙂

* whether it was a “trick” question is probably in the eye of the beholder, though…

“What are the differences, if any, between the following two approaches to inserting multiple rows (assume v1 and v2 have different values):

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL;
INSERT INTO mytable (a)
SELECT :v2 FROM DUAL;

OR:

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL
UNION
SELECT :v2 FROM DUAL;

I quickly answered:

  1. The first approach requires one extra parse;
  2. The second approach requires a Sort Distinct (albeit of only two rows)
  3. A UNION ALL would be better, which would not require a Sort, nor would require the extra parse.

My colleague responded, there’s one very important thing I missed: Triggers! The first approach would execute the BEFORE STATEMENT and AFTER STATEMENT triggers once for each row. The second approach would only execute these triggers once.

What’s sad is that the application we’re working on has row-level logic in the BEFORE/AFTER statement triggers. If we try to optimise the code to insert more than one row in one INSERT statement, the application only runs the row-level logic for the first row inserted. Bad code! Very very bad!


Customising a Navigation List

I like the “Vertical Images List” in APEX, which allows me to create a navigation bar of icons to give users quick access to various pages in my site. It’s easy to customise each item – you can select any image, add attributes for the image if necessary, and each item in the list has a URL which can point to another page in the application, or to an arbitrary URL.

My problem, however, was that some of the URLs in my list took the user to another site, or opened a PDF, and these would open in the same window. I wanted these particular items to open a new window, but the navigation item properties don’t allow this.

To solve this, I modified the Vertical Images List template, and used one of the User Defined Attributes to add “target=_blank” to the items that I wanted. While I was in there, I made a few tweaks to customise the template further to my liking.

A. Modify the Vertical Images List template.

  1. Go to Shared Components and open the Templates (under User Interface).
  2. Scroll down to Vertical Images List (in the Lists category) and open it for editing.
  3. Modify the Template Definition (WARNING: the code for different Apex templates may differ slightly; you’ll have to use a bit of nouse to customise it to your requirements) – you can add bits like #A01#, #A02#, etc – in my case I’ve used the following convention:

#A01# = extra text to appear below the icon & link;
#A02# = tooltip text for the hyperlink;
#A03# = extra attributes for the link (HTML <A> tag).
I’ve done this in both the “List Template Current” and “List Template Noncurrent” sections.

Customising the Vertical Image List template

For example, for “List Template Noncurrent, I’ve modified the template code as follows:

<tr><td><a href=”#LINK#” TITLE=”#A02#” #A03#>
<img src=”#IMAGE_PREFIX##IMAGE#” #IMAGE_ATTR# />#TEXT#</a>
<BR>#A01#</td></tr>

B. Set User Defined Attributes.

  1. Open the Navigation List for editing (Shared Components -> Navigation -> Lists).
  2. Open the list item for editing that you wish to customise (or create a new one).
  3. In User Defined Attributes, attribute 1, add any text you wish to show beneath the link (but not highlighted as part of the ink)
  4. For attribute 2, add the title you wish to show up as a tooltip.
  5. For attribute 3, add the html attribute “target=_blank” if you wish this navigation entry to open a new window when invoked.

This is how it looks in a sample application:

If the user clicks on “Address Book”, the “target=_blank” attribute instructs the browser to open in a new window (or tab, in some cases).


Auto-print a page in APEX

My web site accepts applications for a sports team, and the last step is the applicant needs to print out a form to be signed. To make things as simple as possible, I want this form to send itself to their printer as soon as they open it. Now, I’m not a javascript expert; but instead of googling for the code, I stole it by doing a View Source on Google Mail’s print feature.

To get any APEX page to print when it is opened, all you need to do is add two bits to the page definition:

HTML Header

function Print(){document.body.offsetHeight;window.print()}

Page HTML Body Attribute

onload="Print()"

Isn’t javascript easy? I’m not sure what the “document.body.offsetHeight” is all about but I suspect it’s something to do with waiting for the whole page to load and render before the print starts.