July 20, 2007

New launch of my Forms Framework project

A place, where you host your open-source-project must be

- easy to use
- easy to administer
- easy to find

and two days ago I found Google Code. That's a gorgeous place to share own projects !

http://code.google.com/p/forms-framework/

Actually I started deploying the main PL/SQL library + Forms Template

have fun with this project and the next releases
Gerd

July 19, 2007

Check Form_Success for each Built-In

Many Built-Ins have the problem, that they don't throw exceptions. (only in the ON-ERROR)

Example: You want to navigate to the block Customer and wrote a typo:


Go_Block ('CUSTOMR');
Do_something_after_Go_Block;

Go_Block can't navigate to the block, because you wrote CUSTOMR. But no exception is thrown inside the PL/SQL-Block. This means, the code didn't stop and the execution of Do_something_after_Go_Block starts. This is a big problem in most cases!

Solution: Create a procedure Check_Builtin

PROCEDURE Check_Builtin IS
BEGIN
IF NOT Form_Success THEN
RAISE Form_Trigger_Failure;
END IF;
END;

Use this procedure after each Built-In:

BEGIN
Go_Block ('CUSTOMR');
Check_Builtin;
Do_something_after_Go_Block;
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
-- do something ...
END;

Also you can create your own Built-In for example Goto_Block instead of Go_Block: This new procedure works internally with the new Check_Builtin

PROCEDURE Goto_Block (P_Block IN VARCHAR2) IS
BEGIN
Go_Block (P_Block);
Check_Builtin;
Do_something_after_Go_Block;
END;

and then :

BEGIN
Goto_Block ('CUSTOMR');
Do_something_after_Go_Block;
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
-- do something ...
END;

Important: When you use this technique you have to write an exception-handling and check the FORM_TRIGGER_FAILURE.

This technique is similiar to Oracle's Check_Package_Failure, but this procedure can only be used, when you work with master-detail-relations.

use Check_Builtin
Gerd

July 10, 2007

Concatenate big Default-Where-Clauses

The simplest way to concatenate a Where-Clause is this:

Example: An empty form with table DEPT. Let's say you have three rules, which controls the concatenation of the blocks Default-Where. The problem is: From the second IF on you have to check the value of V_Default_Where. If it is filled you must concatenate ' AND ' before each new part of the string.


DECLARE
V_Default_Where VARCHAR2 (2000);
BEGIN
IF Rule_1_is_TRUE THEN
V_Default_Where := 'DEPTNO IN (10, 20, 30)';
END IF;

IF Rule_2_is_TRUE THEN
IF V_Default_Where IS NOT NULL THEN
V_Default_Where := V_Default_Where ||
' AND DNAME != ''SALES'' ';
ELSE
V_Default_Where := 'DNAME != ''SALES'' ';
END IF;
END IF;

IF Rule_3_is_TRUE THEN
IF V_Default_Where IS NOT NULL THEN
V_Default_Where := V_Default_Where ||
' AND LOC IS NOT NULL';
ELSE
V_Default_Where := 'LOC IS NOT NULL';
END IF;
END IF;

Set_Block_Property ('DEPT', DEFAULT_WHERE,
V_Default_Where);
Go_Block ('DEPT');
Execute_Query;
END;

These IF's are not maintainable. Changes in the where-clauses result in two changes in the code:

...
V_Default_Where := V_Default_Where ||
' AND LOC IS NULL';
ELSE
V_Default_Where := 'LOC IS NULL';
...

A better approach is to use the ' AND ' at the beginning of each concatenation:

DECLARE
V_Default_Where VARCHAR2 (2000);
BEGIN
IF Rule_1_is_TRUE THEN
V_Default_Where := ' AND DEPTNO IN (10, 20, 30)';
END IF;

IF Rule_2_is_TRUE THEN
V_Default_Where := V_Default_Where ||
' AND DNAME != ''SALES'' ';
END IF;

IF Rule_3_is_TRUE THEN
V_Default_Where := V_Default_Where ||
' AND LOC IS NOT NULL';
END IF;

Set_Block_Property ('DEPT', DEFAULT_WHERE,
Substr (V_Default_Where, 6));
Go_Block ('DEPT');
Execute_Query;
END;


the Substr (V_Default_Where, 6) eliminates the first ' AND '.

Really easy and readable code!

PS: Read in the comments, why I didn't use "1=1"