OraTransplant

Difference between call_form, new_form and open_form

I never seem able to remember the difference between new-form, open_form and call_form. I’m just writing down the definitions from the online help in this article for my own (and perhaps others) reference:

CALL_FORM

Runs an indicated form while keeping the parent form active. Oracle Forms runs the called form with the same Runform preferences as the parent form. When the called form is exited Oracle Forms processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.

CALL_FORM can be instructed to hide or not hide the calling form, replace or not replace the menu, open the new form in query only mode, share or not share library data.

NEW_FORM

Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the higher call active and treats it as a call to the new form. Oracle Forms releases memory (such as database cursors) that the terminated form was using.

Oracle Forms runs the new form with the same Runform options as the parent form. If the parent form was a called form, Oracle Forms runs the new form with the same options as the parent form.

NEW_FORM can be instructed to continue the database transaction, rollback to the current savepoint or perform a full rollack. It can also be instructed to open the new form in query only mode and to share or not share library data.

OPEN_FORM

Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

OPEN_FORM can be instructed for the two forms to share the same database session and/or library data.

Commit time business rules

This post will explain two things. First is how to setup a business rule that needs to be checked at commit time and no sooner. The other thing is preventing confusing error messages in Oracle Forms when you do so. By default, Oracle Forms will first inform the user that the records where applied and saved followed by an error message about a failing commit.

We wanted to implement a business rule that has to be valid at commit time and no sooner. A well known example for this is a master record that must have at least one detail record. You cannot check this in normal triggers. When inserting the master record there are no detail records yet. The only correct moment to check these kind of rules is at the commit moment.

You can use a trick with a deferred check constraint to accomplish this. You create a column in a table. On that column you place a deferred check constraint. Then you create some database triggers to maintain the value of that column. Make sure the initial value of that column violates the check constraint. Since the constraint is initially deferred it won’t be checked until commit time. Make sure that some database triggers modified the column to a valid value before the commit moment. Read on for a full example. You can also read about the concept in the Oracle Database Concepts guide

That wasn’t too hard, but it led to a problem in Oracle Forms. Oracle Forms would wrongfully notify the user that the records were saved. Immediately after that notification Forms would raise an error saying the commit has failed. That’s very confusing to a user, so we had to work around that too. Keep on reading for that full example.

Let us begin with setting up a simple example of a commit time business rule. In this case I’m creating a master and detail table and I want to make sure that each master record has at least one detail record. First create a Master and Detail table:

create table tab_master (
  id  number(10),
  constraint tab_master_pk
 primary key
 (id));

create table tab_detail (
  id         number(10),
  master_id  number(10),
  constraint tab_detail_fk
 foreign key (master_id)
 references tab_master (id));

Add a column to the master table which we will use to enforce the business rule:

alter table tab_master add (num_details number(10,0));

Now add appropriate triggers to both the master and detail tables to maintain the column in the master table:

-- Note: this is just a concept, and by no means production
-- grade code (e.g. you should also handle things like
-- updating the FK column)
create trigger tab_master_bir
before insert on tab_master for each row
begin
  -- always force the number of details to 0 on an insert
  -- even if the num_details is specified in the insert
  :new.num_details:=0;
end;
/

create trigger tab_detail_air
after insert on tab_detail for each row
begin
  -- increase the detail count in the master record
  update tab_master
  set num_details=num_details+1 where id=:new.master_id;
end;
/

create trigger tab_detail_adr
after delete on tab_detail for each row
begin
  -- decrease the detail count in the master record
  update tab_master
  set num_details=num_details-1 where id=:new.master_id;
end;
/

Put a deferred check constraint to check if the business rule is violated:

alter table tab_master add
constraint chk_num_details
check(num_details>0) initially deferred;

You can now test to insert a master row without a detail:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (ENIS.CHK_NUM_DETAILS) violated

Inserting a master row and a detail row in the same transaction does work:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> insert into tab_detail(id, master_id) values (2,1);

1 row created.

ENIS@dev01> commit;

Commit complete.

Now go to Oracle Forms to build a form on these tables. Create a default master-detail form using the Forms wizards. For table TAB_MASTER there is no need to include the NUM_DETAILS column in the Form. That’s just something for the database triggers and constraints. Make a relationship between the Master and Detail block. You should end up with a Form looking like this:

With this Form you can enter a new master record and some details and save without problems:

But now see what happens when we violate the rule and do not enter any detail records:

Forms first issues a FRM-40400 message informing the user that the record(s) were applied and saved. After dimissing that alert box, the user is confronted with a cryptic error message: “FRM-99999: Error 408 occured. See the release notes file (relnotes) for information about this error”. It looks like Forms is informing the user about a succesfull save before actually performing a COMMIT.

Unfortunately the release notes (of Forms 10.1.2.0.2) do not explain this error. It turns out that Forms wasn’t build to handle commit time errors. You can of course handle the FRM-99999 error in an ON-ERROR trigger and look at the dbms_error_code and dbms_error_text. Those will tell you that a deferred constraint was violated. You can then present a more informational message to the user.

However, this doesn’t change the fact that Forms first issues a FRM-40400 message to inform the user that the records were saved. If you then display an error after this, the user will be confused.

So, you really want to prevent the FRM-40400 message. Many thanks to the Oracle support Engineer that came up with the solution. You can fix this by overriding the ON-COMMIT trigger. In that trigger you check the deferred constraints. If that check fails you display an error. If the check does not fail, you can perform a normal forms_commit.

To check a deferred constraint, you can issue a:

set constraint constraint_name immediate;

So insert the followin code in the ON-COMMIT trigger of your Form:

declare
	v_dummy number;
begin
  -- check the constraint
  forms_ddl ('set constraint chk_num_details immediate');
  if not form_success and dbms_error_code=-2290 then
    -- constraint violated
    v_dummy := show_alert('master_detail_alert');
    raise form_trigger_failure;
  else
  	commit_form;
  end if;
end;

If you now enter a master without a detail and try to save:

If you acknowledge the error by clicking OK, the Forms commit is aborted. When you then enter some details and try to save again:

Why you need indexes on FK columns

I’ve been developing Oracle systems for about 10 years. Over the years all environments I worked had the rule to create indexes for each foreign key. I knew it had something to do with locking, but never knew the exact details. Until today….

I just started working at Eurotransplant last November, so I wasn’t involved in building the system. As it turns out quite a large number of foreign keys do not have indexes. Together with a quirk in Oracle Forms this led to a locking nightmare.

A note on metalink quite clearly describes the locking related to referential integrity. It was originally written for Oracle v7.0, but it includes notes where newer versions (v7.1.6, v9.0.1 and v9.2.0) changed their behavior. We’re still running v8.1.7.4 so I guess we’re using the v7.1.6 specs of this document.

When you Update or Delete a record in the parent table, a share lock is required on the child table. This lock is held until the end of the transaction (commit or rollback). Other users can also Update or Delete records from the same parent table and they also will acquire a shared lock on the detail table. The problems start when someone wants to make changes to the detail table. They cannot acquire the lock and will hang until the lock is freed.

Most of our transactions come from Oracle Forms. These applications tend to issue DML and follow it with a commit immediately. So, normally a user would not hold this locks for a considerable amount of time. However, we also have an ON-MESSAGE trigger that prevents a message from showing in the status bar but shows it in an actual alert box. So, when a user makes changes and presses Save, he/she gets an alert box stating “FRM-40400 Transaction complete: # records applied and saved”. As it turns out the changes haven’t been applied and saved yet. The DML statement have been sent by Oracle Forms, but the COMMIT is only issued after the user dismissed the alert.

Due to trigger logic changing some records can take a considerable amount of time. Users tend to press Save and then continue work in another application or will do something else. After 10-20 seconds the DML is completed and Forms presents the alert box to the user. By then, the user is doing something and who know how long it will take before he/she dismisses the alert box. All that time, he/she still holds the locks, including the shared table lock on the child table

Lesson learned: Create indexes on all foreign keys or at least for the ones where the parent table can be changed. For this locking problem, foreign keys to static tables (e.g. domain values) do not have to be indexed.

Use color scheme colors in Oracle Forms

I just found out how to set Color properties for objects in Oracle Forms that refer to the colors of the currently used ColorScheme.

The colorScheme is something you can set in the webforms.cfg or set in the URL calling Oracle Forms. There are 7 different documented colorSchemes available in Oracle Forms and one undocumented one (blaf). But what if you give your user the freedom to select their own colorScheme. What color would you then use for items in your Forms? Wouldn’t it be nice if you can just refer to the colors from the colorScheme? Well you can!

We’re having some issues with different color palettes in Oracle Forms. It messes up the background color of items which have no background color set. I’m working on that with Oracle Support. When searching for more information on MetaLink I stumbled across one document that refers to some undocumented colors.

It appears that the undocumented colors “OLAFVeryLight”, “OLAFLight”, “OLAFDark” and “OLAFVeryDark” exist. You cannot select them from a list in Oracle Forms, but you can just type them in the properties palette. This must be a very unknown feature. None of these colors give any hit on Google and only 4 forum entries on MetaLink refer to these colors.

Works like a charm although I bet it is not officially supported by Oracle. Also, you never know if they will keep this feature in a future version or perhaps officially document it.

Update: A binary search over all files of Oracle Forms revealed that the colors “automatic”, “OLAF0″, “OLAF1″, “OLAF2″, “OLAF3″ and “OLAF4″ are also supported. Do mind that all these names are case sensitive.