Category: Other

InSync10 Day 1

After a scrumptious breakfast at the Armoury I headed in what I believed was the general direction of the Melbourne Convention Centre – after making a wrong turn I eventually spotted a footbridge over the river that rung a bell from my GoogleEarthing; after taking some photos I was finally at InSync10.

The first session was Connor McDonald’s 11g Features for Developers, which was an eclectic mix of bits and pieces you won’t get from reading the New Features Guide or from Oracle Marketing, along with some gratuitous use of photos of his kids.

Steven Feuerstein didn’t present next, instead he made us think by running a Developer Quiz. Much like the PL/SQL Challenge (at which, by the way, you should sign up this instant if you haven’t already), it was fun and challenging, and I suspect everyone learned at least one new thing. Me, I learned what SUBSTR returns if the 2nd parameter (which normally starts at 1) is zero. As always, Steven was completely open to criticism, and with Connor and Tom in the room he certainly didn’t get off scot free :)

As it happened, I happened to disagree on one question, which was regarding the USING clause and how many bind variables must be supplied to a given statement. One of the responses (from memory) was that “you must always supply as many bind variables as there are placeholders”. I knew that if the statement being executed was SQL, the number of bind variables must match the number of placeholders, even if some of them have the same names (e.g. INSERT INTO emp VALUES (:a, :b, :a, :b) would require four bind variables). However, I also knew that if the statement is a PL/SQL block, each unique placeholder requires a different bind variable – if the placeholder appears more than once in the block, you don’t repeat the bind variable in the USING clause. I therefore ticked this answer as “correct” – if, for example, the block was BEGIN call_something(:a, :b, :a, :b); END;, you would have to provide two bind variables, because that is how many distinct placeholders there are in the block.

There was some discussion about this, because the answer was marked incorrect – according to Steven the number of placeholders in the block above is four, not two – and I agree that the meaning of a “placeholder” is different to a “bind variable”, although I usually speak as if to conflate the two ideas. However, I still hold to the opinion that a “placeholder” in the context of a PL/SQL block is a reference to this: :a, and I would say that the one placeholder :a appears twice in the PL/SQL block. I believe I have the documentation to back me up:

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

(emphasis added) Source: Using Duplicate Placeholders with Dynamic SQL

This is really just an argument over semantics, so no big deal. Some of the other questions had much more interesting discussion, so it was well worth attending. If you’re in Perth on Friday, Steven is running it again (I won’t be able to attend, unfortunately). I presume he will be using different questions…

After that I went for a walk through Melbourne, since it was sunny outside. The climate in Melbourne, I discovered, is a tad different to Perth. Wandering along the riverside, I ended up experiencing a blast of all four seasons within the space of an hour – a lovely spring breeze, a somewhat warmish summer, then a cold blustering windy autumn (a bit out of order that) – there was a few seconds where it was difficult to remain upright – followed by a sudden rainstorm. I managed to find shelter under one of the many bridges that cross the river, waited for about ten minutes, then was able to walk back to the centre without getting any wetter. In fact, by the time I got back to the convention centre it was sunny again.

After a light lunch it was my turn to talk, and I think my presentation on Apex Themes and Templates went quite well. I appreciated the comments and questions that came back, and had some further discussion with a few people afterward as well, which was good.

I forwent Connor’s excellent Partitioning presentation which I’ve heard before, instead heard Kyle Hayle – Database Performance Made Easy – demonstrate the virtues of database tuning using a tool such as the one he’s produced at Embarcadero. I haven’t made use of many graphical tuning tools before, preferring just “the numbers”, but Kyle made an excellent case for the use of pictures instead of words for not only visualising the workload on the database (such as presented by Oracle’s Enterprise Manager, which Kyle had a hand in), but also for visualising the structure of a query. Personally, I’ve grown accustomed to using the traditional explain plan and I suspect I’ll probably continue to, but the Embarcadero product does have some features that automate some of the work I’d normally do by hand (such as examining the constraints on the tables and obtaining filter percentages).

Last of all, Tom Kyte presented The Best Way, in which he laid to rest for once and for all the answer to the age-old (and oft-repeated) question, “what is The Best Way to …?”. Finally, we can stop arguing over which way is worthy of being called Best Practice, and get on with the job ;)

Went out for a nice dinner at a small japanese restaurant, which had a great cozy atmosphere, and on the way back to the hotel was surprised by these great explosions of flame from these pillars. I could feel the heat from hundreds of meters away. At the end, a quick stop at a store allowed me to procure what I’d been coveting all day: Farmer’s Union Iced Coffee.

Forms Library: PKG_DEBUG

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

This package could do with some improvements:

  • use a database table type (instead of one defined in the forms package) so that we can just send it straight to the database package

  • perhaps a “level” for each log message (Debug/Info/Warning/Error/Fatal) plus the ability to switch the level up or down for each session

Database Objects

CREATE TABLE DEBUG_LOG
(
  log_id      NUMBER(12,0)   NOT NULL
 ,ts          TIMESTAMP      NOT NULL
 ,text        VARCHAR2(4000)
 ,sid         NUMBER
    DEFAULT SYS_CONTEXT('USERENV','SID')
 ,sessionid   NUMBER
    DEFAULT SYS_CONTEXT('USERENV','SESSIONID')
 ,module      VARCHAR2(48)
    DEFAULT SYS_CONTEXT('USERENV','MODULE')
 ,action      VARCHAR2(32)
    DEFAULT SYS_CONTEXT('USERENV','ACTION')
 ,client_info VARCHAR2(64)
    DEFAULT SYS_CONTEXT('USERENV','CLIENT_INFO')
 ,username    VARCHAR2(30)
    DEFAULT SYS_CONTEXT('USERENV','SESSION_USER')
)
CREATE SEQUENCE DEBUG_LOG_SEQ;
CREATE OR REPLACE PACKAGE DB_FORMS_DEBUG IS

TYPE logtable_type IS TABLE OF VARCHAR2(4000)
  INDEX BY BINARY_INTEGER;
TYPE date_table_type IS TABLE OF DATE
  INDEX BY BINARY_INTEGER;

PROCEDURE msg
  (text IN VARCHAR2
  ,ts IN TIMESTAMP := NULL);

PROCEDURE insert_form_debug_log
  (logtable IN logtable_type
  ,date_table IN date_table_type
  ,username IN VARCHAR2
  ,module IN VARCHAR2
  );

END DB_FORMS_DEBUG;
CREATE OR REPLACE PACKAGE BODY DB_FORMS_DEBUG IS

PROCEDURE msg
  (text IN VARCHAR2
  ,ts IN TIMESTAMP := NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0 .. LENGTH(text) / 4000 LOOP
    INSERT INTO DEBUG_LOG
      (log_id
      ,ts
      ,text)
    VALUES
      (DEBUG_LOG_SEQ.NEXTVAL
      ,NVL(ts, SYSTIMESTAMP)
      ,SUBSTR(text, i * 4000 + 1, 4000));
  END LOOP;
  COMMIT;
END msg;

PROCEDURE insert_form_debug_log
  (logtable IN logtable_type
  ,date_table IN date_table_type
  ,username IN VARCHAR2
  ,module IN VARCHAR2
  ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FORALL i IN logtable.FIRST..logtable.LAST
    INSERT INTO DEBUG_LOG
      (log_id
      ,ts
      ,text
      ,module
      ,username)
    VALUES
      (DEBUG_LOG_SEQ.NEXTVAL
      ,date_table(i)
      ,logtable(i)
      ,insert_form_debug_log.module
      ,insert_form_debug_log.username);
  COMMIT;
END insert_form_debug_log;

END DB_FORMS_DEBUG;

Form Package Specification

PACKAGE PKG_DEBUG IS

-- store the log contents in a database table, then reset the log
PROCEDURE flush_to_table;
-- debug log message
PROCEDURE msg (text IN VARCHAR2);
-- spit out debug info when an unexpected error ocurs
PROCEDURE on_error;
-- spit out debug info when a message is issued to the user
PROCEDURE on_message;
--popup a window showing the contents of the debug log
PROCEDURE show_log;
-- log current useful status info
PROCEDURE status;

END PKG_DEBUG;

Form Package Body

PACKAGE BODY PKG_DEBUG IS

-- the debug log is a circular buffer of debug log messages
TYPE debug_log_table_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TYPE log_ts_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
con_debug_log_size CONSTANT PLS_INTEGER := 200;
debug_log_table debug_log_table_type;
log_ts_table log_ts_table_type;
debug_log_pos PLS_INTEGER;

PROCEDURE flush_to_table IS
-- send the debug log buffer to database table DEBUG_LOG
  c INTEGER := 0;
  i INTEGER := 1;
  logtable DB_FORMS_DEBUG.logtable_type;
  ts_table DB_FORMS_DEBUG.ts_table_type;
BEGIN
  LOOP
    EXIT WHEN i IS NULL;
    c := c + 1;
    logtable(c) := debug_log_table(i);
    ts_table(c) := log_ts_table(i);
    i := i + 1;
    --wrap to the top of the buffer
    IF i = con_debug_log_size THEN
      i := 1;
    END IF;
    EXIT WHEN i = debug_log_pos OR NOT debug_log_table.EXISTS(i);
  END LOOP;
  IF logtable.COUNT > 0 THEN
    --this log message will only survive if the db call fails
    DB_FORMS_DEBUG.insert_form_debug_log
      (logtable => logtable
      ,date_table => ts_table
      ,module => NAME_IN('SYSTEM.CURRENT_FORM')
      ,username => [user name/id...]);
    debug_log_table.DELETE;
    log_ts_table.DELETE;
    debug_log_pos := NULL;
  END IF;
END flush_to_table;

PROCEDURE msg (text IN VARCHAR2) IS
-- add debug log message to the scrolling log
BEGIN                              
  debug_log_pos := MOD(NVL(debug_log_pos,0) + 1, con_debug_log_size);
  debug_log_table(debug_log_pos) := SUBSTR(text,1,4000);
  log_ts_table(debug_log_pos) := PKG_FORM.system_datetime;
END msg;

PROCEDURE on_error IS
-- Catch-all error handler for unexpected errors.
BEGIN
  -- This should only ever be called when an *unexpected* error occurs.
  --
  -- If an error is expected in some circumstance, it should be handled (e.g. by putting
  -- code in the on-error trigger to do something intelligent instead of calling this
  -- catch-all procedure).
  --
  msg('DBMS ERROR    : ' || DBMS_ERROR_CODE || ' ' || RTRIM(DBMS_ERROR_TEXT,CHR(10)));
  status;
  flush_to_table; -- so that support personnel may investigate
  PKG_MESSAGE.error(ERROR_TYPE || '-' || ERROR_CODE || ' ' || ERROR_TEXT, 'System Error ' || ERROR_TYPE || '-' || ERROR_CODE);
END on_error;

PROCEDURE on_message IS
BEGIN
  status;
  MESSAGE(SUBSTR(MESSAGE_TYPE || '-' || MESSAGE_CODE || ' ' || MESSAGE_TEXT, 1, 200), ACKNOWLEDGE);
END on_message;

PROCEDURE show_log IS
  i INTEGER := debug_log_pos;
BEGIN
  -- fill the onscreen item CONTROL.DEBUGLOG starting
  -- with the most recent debug line, and go backwards until
  -- it is filled or there are no more debug log messages to show
  -- It would be better for this to be done in a separate
  -- form specifically for this purpose, to avoid problems
  -- when we want to show the debug log without
  -- firing validation triggers.
  :CONTROL.debuglog := NULL;
  LOOP
    EXIT WHEN i IS NULL;
      -- "64K should be enough for everybody"
      IF :CONTROL.debuglog IS NOT NULL THEN
        :CONTROL.debuglog := SUBSTR(CHR(10) || :CONTROL.debuglog, 1, 65534);
        EXIT WHEN LENGTH(:CONTROL.debuglog) = 65534;
      END IF;
      :CONTROL.debuglog := SUBSTR(
        TO_CHAR(i,'FM0000')
        || ' ' || TO_CHAR(log_ts_table(i),'HH24:MI:SS')
        || ' ' || debug_log_table(i)
        || :CONTROL.debuglog
        , 1, 65534);
      EXIT WHEN LENGTH(:CONTROL.debuglog) = 65534;
      i := i - 1;
      --wrap to the top of the buffer
      IF i = 0 THEN
        i := con_debug_log_size - 1;
      END IF;
      EXIT WHEN i = debug_log_pos OR NOT debug_log_table.EXISTS(i);
  END LOOP;
  GO_ITEM('CONTROL.debuglog');
  check_package_failure;
END show_log;

PROCEDURE status IS
BEGIN
  msg('FORM STATUS   : ' || :SYSTEM.FORM_STATUS);
  msg('RECORD STATUS : ' || :SYSTEM.RECORD_STATUS);
  msg('TRIGGER ITEM  : ' || NVL(:SYSTEM.TRIGGER_ITEM, :SYSTEM.TRIGGER_BLOCK)
    || ' RECORD #' || :SYSTEM.TRIGGER_RECORD);
  msg('CURSOR ITEM   : ' || :SYSTEM.CURSOR_ITEM
    || ' RECORD #' || :SYSTEM.CURSOR_RECORD);
END status;

END PKG_DEBUG;

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;

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.