Special Tips for List Items in Oracle D2k

In this section, I shall discuss some special tips and techniques offered by Forms with respect to lists and list items.

 

Populating List Items Dynamically in Oracle D2kList items appear as either drop-down list boxes, T-lists, or combo boxes in Forms. Mostly, list items have static values as their elements, created during design time. However, there arises a need to populate a list item with elements based on runtime criteria or from a database table. For example, one might populate a drop-down list box with all the state codes from a look-up table named STATE_TAB. This can be done either using a query or adding elements one by one at runtime. This way of populating list items programmatically without hard-coding their values at design time is what is termed dynamically populating list items.

This technique involves populating the list with a query or without a query. I will discuss populating by means of a query because it is very powerful and efficient.

One elegant way to populate list items dynamically is to use programmatically created records groups. This record group should have a two-column structure, both being of character data type. The first column should correspond to the label of the list item, and the second column, to the corresponding value.

Tip

The record group has to be created programmatically. However, it can either query or nonquery as long as it follows the two-column structure mentioned here.

Never try to populate a list item using a query directly. Always create and populate a query record group, and use this record group to do the job.

The code performs these tasks:

 

Create the record group using CREATE_GROUP_FROM_QUERY.

Populate the record group using POPULATE_GROUP or POPULATE_GROUP_FROM_QUERY.

Populate the list item using POPULATE_LIST.

The sample code is given here:

DECLARE

   rg_list_id   recordgroup;

   rg_name      VARCHAR2 (20) := 'RG_LIST';

   ret_code     NUMBER;

   --The following holds a SELECT query from which the list elements are derived.

   v_select     VARCHAR2 (300);

BEGIN

   v_select := 'SELECT state_code, state_code FROM state_tab ORDER BY 2';

   rg_list_id := FIND_GROUP (rg_name);

 

   IF NOT ID_NULL (rg_list_id)

   THEN

      DELETE_GROUP (rg_list_id);

   END IF;

 

   rg_list_id := CREATE_GROUP_FROM_QUERY (rg_name, v_select);

   ret_code := POPULATE_GROUP (rg_list_id);

   POPULATE_LIST ('LIST_ITEM', 'RG_LIST');

   DELETE_GROUP (rg_list_id);

END;

Tip

Use a form procedure, passing the input query and list item name as parameters.

A nonquery record group can also be used instead of a query record group, provided that it is created programmatically using CREATE_GROUP and populated using POPULATE_GROUP or POPULATE_GROUP_WITH_QUERY.

 

Populating a List Item with Date and Number Values in Oracle D2kSometimes, it might be necessary to populate NUMBER and DATE columns as list item element values. Because list items always retain character values only, for both the label and value, it is necessary to perform conversion to VARCHAR2 from NUMBER and DATE.

As an illustration, consider a drop-down list box showing all departments in an organization. You can assume that the DEPT table has the following structure:

 

CREATE TABLE DEPT

(ID NUMBER(6) PRIMARY KEY,

NAME VARCHAR2(30) NOT NULl);

 

The label column has its values derived from the NAME column. The value corresponding to each NAME is derived from the ID column and should be stored in the list item as a character value. This requires the use of TO_CHAR. Therefore, the query text in the preceding example changes to

v_select := 'SELECT name, TO_CHAR(id) FROM dept ORDER BY 1';

After populating the list, the value can be accessed by doing a reconversion to NUMBER using the TO_NUMBER function. The following shows how to access the ith element from the list discussed in the preceding example:

 

DECLARE

   v_id        NUMBER;

   v_id_char   VARCHAR2 (6);

   item_id     item;

BEGIN

   item_id := FIND_ITEM ('LIST_ITEM');

 

   IF ID_NULL (item_id)

   THEN

      MESSAGE ('Invalid List');

      RAISE form_trigger_failure;

   END IF;

 

   FOR i IN 1 .. GET_LIST_ELEMENT_COUNT (item_id)

   LOOP

      v_id_char := GET_LIST_ELEMENT_VALUE (item_id, i);

      v_id := TO_NUMBER (v_id);

   END LOOP;

END;

 

Tip

Use conversion functions TO_CHAR, TO_DATE, and TO_NUMBER.

On input to the list, use TO_CHAR for both date and numeric values that correspond to the Value column of the list.

On output from the list, use TO_DATE and TO_NUMBER, respectively, for date and numeric values.

Use these in the SELECT column list, which is used to populate the list.

 

Adding Items to the Beginning of a List in D2kHow many of you are aware of the fact that in Forms, you can add elements to the beginning of an already populated list item without repopulating it? This seems trivial at first thought but is a very powerful and flexible technique to be used in many demanding situations.

Use ADD_LIST_ELEMENT and specify the constant 1 for the element index. This displaces all the existing elements to one position below and makes room for the new element with index number 1. Do not specify the constant 0 instead of 1.

The code you will use is as follows:

ADD_LIST_ELEMENT(list_id, 1, <label>, <value>);

 

Adding Items in the Middle and to the End of a List in D2kSo far, I have discussed how to add elements dynamically to an empty list, how to access noncharacter list elements, and how to add to the beginning of an existing list. Now I will present a simple method to insert elements in the middle of an existing list and to append elements to the end of an existing list.

Use ADD_LIST_ELEMENT and specify the index number of the new element as

current element index + 1

where current element is the element after which the new element should be added. This displaces all the remaining elements to one position below and makes room for the new element.

To add to the end of the list, specify the constant returned by GET_LIST_ELEMENT_COUNT, which gives the number of existing elements, and then specify the index of the new element as the value of this constant incremented by 1.

The following is the code for this:

 

DECLARE

cnt NUMBER := 0;

BEGIN

cnt := GET_LIST_ELEMENT_COUNT(list_id);

ADD_LIST_ELEMENT(list_id, (cnt+1), <label>, <value>);

END;

 

In this example, you take the count of the list elements and increment it by 1. This value serves as the index for the new list element to be added. This works even when the list is wholly empty, because you initialize the count to zero before incrementing it by 1. Therefore, it adds the first element in case of an empty list.

 

Simulating a Drill-Down and Drill-Up LOV Using T-Lists in D2kLOV is the Forms acronym for List of Values. It functions in a manner similar to a pick list of choices. Drill-down LOV refers to descending through the LOV to its sublevels, starting from a highlighted element in the LOV.

Drill-down LOVs are very useful for tables involving recursive and/or parent-child relationships. Examples of such relationships are those involving a department and its employees, a manager and employees, or a company and its hierarchical representation.

In each of these cases, a foreign key is involved that is either self-referential or parent- referential. For example, the department-employee relationship involves a parent-referential foreign key from the Department table (the parent table). The manager-employees relationship is self-referential, with the primary and foreign keys being selected from the same table. Also, the information is hierarchical. The company information is another example of a hierarchical representation.

LOVs are a commonly required feature of any application involving this kind of look-ups. The features of LOVs supported by Forms are limited in the sense that

 

 

There is no way to do multiselection from an LOV.

There is no way to drill down an LOV into its sublevels.

There is no way to add new rows to the look-up table using an LOV.

Out of these limitations, the most required functionality in case of parent-child relationships, especially tree-oriented, is the drill-down.

Drill-down functionality can be incorporated in an LOV directly using a Forms-provided LOV or using list items. This section discusses the implementation details of building a drill-down LOV using list items. The same method can be followed when using a Forms-supplied LOV.

You will use a T-list and dynamically populate it using a record group. The drill-down is achieved by dynamic replacement of the same record group query again and again till there is no further drill-down. Double-clicking a parent element will show this parent element and all its children one level below. The user "drills down" the LOV to reach a deeper level. The operation is repeatable until leaf elements are reached. The string '- Parent' is appended to the parent element label to identify it as the parent. This element is always the first element of the list, irrespective of whether it has children.

Double-clicking a parent element will show its parent and all its children, that is, one level above. The user "drills up" the LOV to reach a higher level. The operation is repeatable until the root element is reached. Once at the root, the original list is restored; that is, all EMPNOs that exist as MGRs. The string '- Parent' is still appended to the parent element label until the initial list is restored.

The same T-list and the same record group are reused for the drill-down and drill-up operations.

The selection of an element is done by pressing Shift and double-clicking on the element. The normal double-clicking being reserved for drill-down, the Shift+double-click is used as an alternative for selecting an element from the LOV.

 

 

Create a block, BLOCK2, having an item named LOV in it. The properties for the block are set as follows: Database Data Block property set to No, Number Of Records Displayed set to 1, Insert Allowed set to Yes, and Update Allowed set to Yes. The properties for the LOV item are as follows: Subclass Information property set to PC_TLIST (property class).

The property class PC_TLIST.

 

Tip

Remember to create a NULL list element; that is, both the label and its value are NULL. NULL means a null value, not the string 'NULL'.

 

 

The EMP table is used to project the hierarchical relationship between manager (MGR column) and employees (EMPNO column). Assume that the EMP table has the following structure:

 

CREATE TABLE EMP

(EMPNO NUMBER(10) PRIMARY KEY,

ENAME VARCHAR2(30) NOT NULL,

MGR NUMBER(10) REFERENCES EMP (EMPNO),

HIREDATE DATE,

SAL NUMBER(11,2),

JOB VARCHAR2(20),

DEPTNO NUMBER);

 

The LOV functions the following way. At first, all the employees at the parent level are displayed. The query for doing this follows:

SELECT ename, TO_CHAR(empno) empno FROM emp WHERE

empno IN (SELECT mgr FROM emp a)

 

This SELECT statement also guarantees that the first-level EMPNO is selected.

A dynamic record group, 'RG_'||'LOV', is created from the preceding query and then populated and repopulated using rows returned by the query. The T-list is populated using this record group. You use a generic procedure for this step. The procedure is named p_populate_list:

 

PROCEDURE p_populate_list (item_name_in       VARCHAR2,

                           query_in           VARCHAR2,

                           o_retcd        OUT NUMBER)

IS

   rg_id     recordgroup;

   retcd     NUMBER;

   rg_name   VARCHAR2 (100);

   item_id   item;

BEGIN

   item_id := FIND_ITEM (item_name_in);

 

   IF ID_NULL (item_id)

   THEN

      o_retcd := -1;

      retrun;

   END IF;

 

   rg_name :=

      'RG_'

      || SUBSTR (item_name_in,

                 INSTR (item_name_in, '.', 1) + 1,

                 LENGTH (item_name_in));

   rg_id := FIND_GROUP (rg_name);

 

   IF NOT ID_NULL (rg_id)

   THEN

      DELETE_GROUP (rg_id);

   END IF;

 

   rg_id := CREATE_GROUP_FROM_QUERY (rg_name, query_in);

   retcd := POPULATE_GROUP (rg_id);

 

   IF (retcd <> 0)

   THEN

      o_retcd := retcd;

      RETURN;

   END IF;

 

   POPULATE_LIST (item_name_in, rg_id);

 

   IF NOT FORM_SUCCESS

   THEN

      o_retcd := -2;

   END IF;

 

   o_retcd := 0;

END;

 

The appropriate trigger for calling the p_populate_list procedure is WHEN-NEW-FORM-INSTANCE:

WHEN-NEW-FORM-INSTANCE

 

DECLARE

   query_in   VARCHAR2 (32767) := 'SELECT ename, TO_CHAR(empno) empno

FROM emp

WHERE empno IN (SELECT mgr FROM emp)';

   retcd      NUMBER;

BEGIN

   p_populate_list ('

                 block2.lov ', query_in, retcd);

 

   IF (retcd <> 0)

   THEN

      MESSAGE (' err:could NOT populate list item.');

      RAISE form_trigger_failure;

   END IF;

END;

 

The right place for the drill-down code is the WHEN-LIST-ACTIVATED trigger:

 

DECLARE

   query_in             VARCHAR2 (32767);

   item_name            VARCHAR2 (100) := NAME_IN ('SYSTEM.TRIGGER_ITEM');

   retcd                NUMBER;

   current_rows_mgr     NUMBER;

   current_rows_empno   NUMBER;

BEGIN

   query_in :=

         'select LPAD(ename, ((LEVEL-1)*4+LENGTH(ename)), '''')||

DECODE(TO_CHAR(empno), '

      || NAME_IN (item_name)

      || ', ''- Parent'', NULL) ename, TO_CHAR(empno) '

      || ' FROM emp '

      || 'WHERE empno = '

      || TO_NUMBER (NAME_IN (item_name))

      || 'or mgr = '

      || TO_NUMBER (NAME_IN (item_name))

      || ' START WITH empno = '

      || TO_NUMBER (NAME_IN (item_name))

      || 'CONNECT BY PRIOR empno = mgr';

   p_populate_list (item_name, query_in, retcd);

END;

 

The WHEN-LIST-ACTIVATED trigger is modified as follows (the following listing shows the complete code) to accomplish both drill-down and drill-up:

WHEN-LIST-ACTIVATED

 

DECLARE

   query_in             VARCHAR2 (32767);

   item_name            VARCHAR2 (100) := NAME_IN ('SYSTEM.TRIGGER_ITEM');

   retcd                NUMBER;

   current_rows_mgr     NUMBER;

   current_rows_empno   NUMBER;

BEGIN

   IF INSTR (GET_LIST_ELEMENT_LABEL (item_name, 1), 'Parent', 1) = 0

   THEN

      -- if current element is in the initial list

      query_in :=

            'SELECT LPAD(ename, ((LEVEL-1)*4+LENGTH(ename)), '''') ||

DECODE(TO_CHAR(empno), '

         || NAME_IN (item_name)

         || ',''- Parent'',NULL) ename, TO_CHAR(empno)'

         || 'FROM emp '

         || 'WHERE empno = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'or mgr = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'START WITH empno = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'CONNECT BY PRIOR empno = mgr ';

   ELSIF INSTR (GET_LIST_ELEMENT_LABEL (item_name, 1), 'Parent', 1) > 0

         AND (TO_NUMBER (GET_LIST_ELEMENT_VALUE (item_name, 1)) !=

                 TO_NUMBER (NAME_IN (item_name)))

   THEN

      -- if current is a child of a parent

      query_in :=

            'SELECT LPAD(ename, ((LEVEL-1)*4+LENGTH(ename)), '''') ||

DECODE(TO_CHAR(empno), '

         || NAME_IN (item_name)

         || ', ''- Parent'',NULL) ename, TO_CHAR(empno)'

         || 'FROM emp '

         || 'WHERE empno = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'or mgr = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'START WITH empno = '

         || TO_NUMBER (NAME_IN (item_name))

         || 'CONNECT BY PRIOR empno = mgr ';

   ELSIF INSTR (GET_LIST_ELEMENT_LABEL (item_name, 1), 'Parent', 1) > 0

         AND (TO_NUMBER (GET_LIST_ELEMENT_VALUE (item_name, 1)) =

                 TO_NUMBER (NAME_IN (item_name)))

   THEN

      -- if current element is a parent

      BEGIN

         current_rows_empno := TO_NUMBER (NAME_IN (item_name));

         MESSAGE (TO_CHAR (current_rows_empno), acknowledge);

 

         SELECT mgr

           INTO current_rows_mgr

           FROM emp

          WHERE empno = current_rows_empno;

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

            NULL;

      END;

 

      IF current_rows_mgr IS NOT NULL

      THEN

         query_in :=

               'SELECT LPAD(ename, ((LEVEL-1)*4+LENGTH(ename)), '''') ||

DECODE(TO_CHAR(empno), '

            || TO_CHAR (current_rows_mgr)

            || ', ''- Parent'', NULL) ename, TO_CHAR(empno) '

            || 'FROM emp '

            || 'WHERE empno = '

            || current_rows_mgr

            || 'or mgr = '

            || current_rows_mgr

            || 'START WITH empno = '

            || current_rows_mgr

            || 'CONNECT BY PRIOR empno = mgr ';

      ELSE

         query_in := 'SELECT ename, TO_CHAR(empno) empno FROM emp WHERE

empno IN (SELECT mgr FROM emp)';

      END IF;

   END IF;

 

   p_populate_list (item_name, query_in, retcd);

END;

 

Simulating the Find Feature of List of ValuesThe Find feature of LOVs enables you to enlarge or reduce the LOV list as the user types in characters of the LOV element value. This feature can be simulated using COMBO BOX and T-LIST type list items in Forms 4.5. Although it doesn't use the exact mechanism offered by LOVs, the method described here imitates the same functionality. You will assume that the list to be displayed is SITE_NAME, based on the SITE_TAB table. The SITE_TAB table has the following structure:

SITE_NO NUMBER(6) NOT NULL,

SITE_NAME VARCHAR2(20) NOT NULL.

Follow these steps:

 

Create a combo box–type list item. Name it LIST_ITEM_COMBO. The trick lies in using a combo box so that as the user types in characters, the resulting list can be populated with a record group, which is created dynamically, based on user input. A WHEN-LIST-CHANGED trigger would suffice for this.

Create a T-list–type list item below LIST_ITEM_COMBO. Name it LIST_DEST_ITEM. This holds the destination list based on the characters the user types in LIST_ITEM_COMBO.

Create a WHEN-LIST-CHANGED trigger for LIST_ITEM_COMBO as follows:

DECLARE

   rg_list_id   recordgroup;

   ret_code     NUMBER;

BEGIN

   rg_list_id := FIND_GROUP ('RG_LIST');

 

   IF NOT ID_NULL (rg_list_id)

   THEN

      DELETE_GROUP (rg_list_id);

   END IF;

 

   rg_list_id :=

      CREATE_GROUP_FROM_QUERY (

         'RG_LIST',

            'SELECT site_name, site_name

FROM site_tab

WHERE site_name LIKE '

         || ''''

         || :list_item_combo

         || '%'

         || '''');

   ret_code := POPULATE_GROUP (rg_list_id);

   POPULATE_LIST ('LIST_DEST_ITEM', 'RG_LIST');

   DELETE_GROUP (rg_list_id);

END;

See also: http://www.foxinfotech.in/2013/03/shifting-listitem-values-from12-oracle-forms.html