Oracle Forms 4.5 is a powerful application development environment. FormBuilder, with the addition of Object Groups, Object Libraries and property palettes for graphics, makes the next generation even more productive.

Unfortunately a number of features and creatures (binary units generating stress) can slow down productivity. This paper presents 20 of the author’s favorites from version 4.5. Most of the features presented here remain viable in FormBuilder 5.0 and continue to plague developers. In this paper I have created twenty categories of issues and listed the appropriate work-arounds.

Code when presented in this paper is written using generic techniques for ease of inclusion in libraries, packages, and for reuse within a form or template.

To navigate to another window, block, or field without firing mandatory tests or code in the When-Validation-Item trigger, add the following code:

Set_form_property(name_in('system.current_form'),validation_unit,block_scope);

Reset validation to item_scope or default_scope when done. (eg: When-Window-Activated)

If field has already been marked invalid, this will require setting the field back to a valid state.

Set_item_property(name_in(‘system.current_item’),item_is_valid,property_true);

Note: When navigating in the same block, using a mouse, this technique won’t work.

Mouse Click on a Scroll-Bar won't fire any trigger. Everywhere else on the form, it works fine. (FB50 still a problem)

To change the color of specific records to something other than the color of the current record use:

Display_Item(‘item_name’,visual_attribute);

Perform this function in pre-record and post-record triggers. Once you’ve entered a field, its attributes can’t be changed. Additionally, Current Record Attribute and Visual Attribute at the field and record level may override the change. Code will need to be added to re-color the field.

Note: Mirrored items will take on the same visual attribute of their base items.

The full set of RGB colors displayed in FormBuilder is not available at run time for all field attributes. Changing the color of the toolbar canvas makes a great hint that you are in query mode, in addition to hiding and showing relevant buttons and menu items.

"yyyy" format mask yields 0097, not 1997 when only 97 is entered.

Use the When-Validate-Item on date fields for accepting 2-digit and 4-digit years.

copy(to_date_rr(name_in('system.cursor_value')),:system.trigger_item);

FUNCTION TO_DATE_RR (ic_date in varchar2) RETURN varchar2 IS
vd_date date;
oc_date varchar2(11);
BEGIN
vd_date := to_date(ic_date,'dd-MON-yyyy');
if vd_date is null then

oc_date := null;
elsif to_char(vd_date,'yyyy') < 50 then -- Not required in v5.0 or PL/SQL v2
oc_date := to_char(vd_date,'dd-MON-20yy');-- Not required in v5.0 or PL/SQL v2
elsif to_char(vd_date,'yyyy') < 100 then -- In v5.0 use the code below -v-
oc_date := to_char(vd_date,'dd-MON-19yy');-- to_char(vd_date,’dd-MON-rr’);
else
oc_date := ic_date;
end if;
return oc_date;
Exception when others then
raise form_trigger_failure;
END;

PL/SQL v1 to_char(date,’mask’) does not recognize the ‘rr’ format

[Fixed in Forms 5.0, PL/SQL v2, and a proposed 4.5.x release]

When-Mouse-DoubleClick or Key-LISTVAL on dates, provides users with a useful result a majority of the time.

copy(to_char(sysdate,'dd-mon-yyyy'),:system.trigger_item);

The Count_Query value is not available programmatically and Last_Record can be slow. Use DBMS_SQL to return the record count.

FUNCTION HITS RETURN integer IS
v_cid integer;
v_value integer;
v_rows integer;
v_last_query varchar2(2000) := name_in(‘system.last_query’);
v_fpos integer := instr(v_last_query,'FROM');
v_lpos integer := instr(upper(v_last_query),'ORDER BY');
v_len integer := null;
v_query_hits varchar2(2000);
BEGIN
if v_lpos > 0 then
v_len := v_lpos-v_fpos;
end if;
v_query_hits := 'SELECT COUNT(*) CNT '||
substr(name_in(v_last_query, v_fpos, v_len);
v_cid := dbms_sql.open_cursor;
dbms_sql.parse(v_cid, v_query_hits, 2); -- dbms_sql.v7);
dbms_sql.define_column(v_cid, 1, v_value);
v_rows := dbms_sql.execute(v_cid);
v_rows := dbms_sql.fetch_rows(v_cid);
dbms_sql.column_value(v_cid, 1, v_value);
dbms_sql.close_cursor(v_cid);
RETURN v_value;
END HITS;

You can also pass the Last_Query as a lexical parameter to Reports for a hard copy of the current query result.

Disable post-query trigger processing by using a global-flag. However, you’ll need to populate details on a pre-record trigger to display non-base table items.

You can also implement a timer and estimate completion time based on COUNT_HITS.

Note: Populating details on pre-record slows down scrolling and other record navigation processes.

Mirrored items are useful for displaying data in tabular and forms modes, and multiple canvases in the same application w/ little additional coding.

On the mirrored item, set the BASE_TABLE property and QUERY_ONLY to false, to prevent occasional invalid SQL.

Hint: Use a consistent prefix for naming mirrored fields so you can test and manipulate the different versions programmatically.

Note: See Display_Item bug above.

A poplist on a base_table item will prevent the record from displaying when queried. There are two options for handling this.

Option 1:

Union All to add invalid items to list.

Option 2:

Create non-base_table field for the poplist and populate it in the post-query and when-list-changed triggers.

Can't get alert properties.

Can't get record_group properties.

Note: To test for a populated record_group, which is useful when creating record_groups on the fly use:

v_rowcount := Get_Group_Row_Count(v_rg_id);

if v_rowcount <= 0 then

v_status := Populate_Group(v_rg_id );

end if;

A Drill Down can be programed to call other lists of values by adding additional options to the record group using "union all select <detail>, ‘ See Details’ from dual".

Mandatory Field won't call an Auto LOV if null. Try a default value of '%' in mandatory fields to force an auto-LOV w/o setting the validate w/LOV property.

In Developer/2000 you can't delete columns mapped in an LOV.

[FB50 still exists]

Referenced menus do not bring roles with them. You must create the roles in both the original and referenced menu.

Menu items can't be disabled from a role. You can't turn an item off based on having a role. Add "disable" in the WHEN_NEW_FORM_INSTANCE trigger in all forms or your template.

Packages have a limit on size. The size is even less if you want to run the debugger.

Exceeding sizes can cause a GPF. Always do a "save as" before running in debug mode.

A Null parameter value can't be checked or used in a function; and a default value can cause very unpredictable results. (FB50 Fixed)

Navigation in ENTER_QUERY mode can cause an auto select of the first option in a poplist, check box, or radio group. Disabling navigation to these objects prevents the inadvertent selection of a value when tabbing. (FB5.0 fixed)

Record groups can be used to populate non-base-table blocks.

Scrolling can be slow when searching through a large set. There are two workarounds for this:

Fix #1: Setting number of records to a high value to force a query of all records. Problem: This causes the scrollbar to go gray.

Fix #2: Set display to "actual" on the fields. Set the block records to 1/3 of the set.

Set_Property to Disabled and Query only is not allowed. This causes an ORA error.

You must set the displayed property before the enabled property, but the enable property is automatically disabled when you set the display property off.

Note: Disabling fields on startup is better than enabling them. It causes fewer problems in forms operations.

You can't set properties on the current field.

Solution: Add a null size field on each canvas for setting focus and having a place to go while setting field properties. You can automatically navigate out of this field and to the first field in the form in the .. trigger

You can base a block on two tables and put the join in the WHERE clause. Then you can order by the proper column (only if updates and inserts are not allowed or are performed in on-insert and on-update triggers).

Note: Set the key-mode property from "unique" to "non-updatable" or "updatable" to get rid of the rowID

You can also use the select from (select ..) Construct to create a dynamic view. Place the select statement in the BASE_TABLE property.

Note: Put the select statement in parentheses.

For an updatable table, create a package function in the database that returns the sort column when the foreign key column in the base table is passed as an argument.

order by = sort_pkg.dept(dept_no)

Record_Status - Default values and post query to non-base table items often changes status from New to Insert, and Query to Changed. In post-query and pre-record triggers add the following:

set_record_property(:system.trigger_record,i_block,status,new_status); or

set_record_property(:system.trigger_record,i_block,status,query_status);

Note: Checking the value of record_status does not always return the correct result.

Top_record is a block_property, and not a :system variable.

Multi-record buttons can fire on the wrong record if set to navigable=false

Go_record(name_in(‘system.mouse_record’);

When used to pop-up text-box, change the label to reflect information present.

(Eg: ‘Comment’ vs. ‘No Comments’)

For auto Edit Box on navigation:

Set display of comment field to (size 0,0)

Call Edit_field; next_field; on When-new-item-instance trigger.

Alternatively: Use Tabs and Canvases for multiple comments with multi-line text field.

Inheritance at multi-levels available: Inherited triggers can be set to before, after, over-ride, etc.

The Trigger On-error in a Property_Class is not over ridden by a trigger on a field.

The Trigger Dup_record - Duplicates the pkey

Adding a procedure to a package can causes a PL/SQL Check #50510 error. To fix, rename the spec, compile the spec, and rename it back.

Convert Can't create text version of forms w/ Libraries

Remove Attached Libraries and then convert to text.

No Horizontal scrollbar in Developer Sql Editor

Use Display_Item for dynamic prompts. FB50 provides dynamic prompts as properties of items.

I. Michael Snyder is a Principal at Dulcian, Inc. His 17 years of systems development and consulting experience includes Oracle Forms, Reports, Oraterm, Web Server, PL/SQL and Database Administration.

Michael Snyder is a Scout Master for Boy Scout Troop 772, has taught Database Concepts at the George Washington

University, and has a Masters Degree in Information Technology. Michael is the co-author of an "Advanced Developer 2000 v2.0

Developers Guide" due out in October 1998, published by Que.