OraTransplant

PL/SQL enhancements in version 11

I was just browsing the session catalog for Oracle OpenWorld 2006 Develop to see whether it is worth while to visit. I stumbled upon a session by Bryn Llewellyn, PL/SQL product manager from Oracle entitled “PL/SQL Enhancements in the Next Major Release of Oracle Database”.

The session abstract itself already gives a good idea of the new features he will be talking about. It sounds very promising:

Native compilation now requires no C compiler, because the PL/SQL compiler generates the machine code DLL directly. The file system directory is no longer needed, and only the PLSQL_Code_Type parameter remains.

A brand-new systemwide function result cache speeds performance, by avoiding expensive recalculation. Declarative mechanisms are provided to purge a cached result when, for example, table data it relies on changes.

Dependencies are now tracked at the level of the element (subprogram, variable, type declaration, cursor, and so on) in a package or the column in tables. A PL/SQL unit that depends only on elements that are not changed is now not invalidated when other elements are changed. This has the added benefit that the frequency of ORA-04068 errors in a multiuser environment is reduced.

Through Oracle Database 10g, there are some gaps in PL/SQL’s method 4 dynamic SQL functionality. These are now filled. For example, a DBMS_Sql cursor that was opened for a select statement whose select list or bind requirements are not known until runtime can now be converted to a ref cursor to return to a client-side application.

The new compound trigger allows code to be defined that fires at “before statement,” “before each row,” “after each row,” and “after statement” times in a single compilation unit. The unit can declare global state that lasts just for the duration of the firing SQL statement.

These are just a few of the PL/SQL enhancements brought by the next major release of Oracle Database. In this session, you learn about them all.

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: