The new german Talk2Gerd

Today I learned from Patrick Wolf, how easy it is to duplicate a blog into another languages. This link here is now the german version of Talk2Gerd.

And last but not least, here is a picture of my company after snowfall... and we had a lot of snow in the last years:

Libraries: PLL vs. PLX

One big problem while developing an application is the question: Should I use attached libraries as *.pll or *.plx?

Let's have a look at some cases:

A) You use pll+plx while developing the application. The Forms Builder uses the working directory C:\Forms and the FORMS_PATH has an additional path to the directory C:\Forms\Lib. The Sourcefiles have the name Lib.pll, the Menu.mmb and EMP.fmb.

If there is a generated Lib.plx in c:\forms or c:\forms\lib, then this is the version, which will be used at runtime.

This is the direction of how Forms searches for attached libraries:

1) If Lib.plx is in your working directory - take it
2) If not found, search in the FORMS_PATH for Lib.plx
3) If not found, search in the working directory for Lib.pll
4) If not found, search in the FORMS_PATH for Lib.pll
5) If the library can't be found - Forms raises an error

This is identical to menu-mmx and forms-fmx

If the plx in the local working directory is an older version than the pll, this will cause big problems while running the forms-application. Finding errors is very tricky, if the developer didn't look on the timestamps of the libraries.

B) You use only pll's in your application.

You have no problems while developing the application or at runtime. If a library in your working directory didn't exist, then the library can be found in the FORMS_PATH.

Summary:

I never use plx because I saw many projects run into the plx-trap.

Many developer say: plx is faster, but I never had performance problems with the pll's.

Other developer say: plx is only the compiled code, so nobody can steal my sourcecode or something valuable from within the code - in that case try this example:

Create a library with a package and this variable:

PACKAGE Const IS
  HiddenPW                     CONSTANT Varchar2 (100) := 'HiddenPW';
END Const; 

Generate the plx and open it in an editor. Search for "HiddenPW". You'll find the name of the variable and the string associated to the var. That's not secure in my eyes. Only the sourcecode is hidden. I think, that a pll on an Application Server is secure enough.

If the application is standard software and not for free, then you must use the plx-technique and wrapped packages in the database.

DOAG Top News: Forms 11g

The German Oracle User Group (DOAG) has launched a little Forms 11g note from me as their actual Top News.

I wrote some lines about the new features of Forms 11g and the huge capabilities in the new SOA world of tomorrow. Here is the translation:

Forms 11g unterwegs in Richtung SOA : Forms 11g's direction to SOA

After 6 years is this the first Forms Version which integrates new features into the Forms Builder. The two new important features found are:

The first one is the communication between the database and Forms via Advanced Queuing. There is a new object-typ called Event, who communicates with a queue. New data in a Queue starts an event-trigger, which runs in Forms.

The second highlight is the Javascript-API. HTML-pages can exchange data with Forms via Javascript. In the old days you could do this only using java applets. With the new technique you have WHEN-CUSTOM-JAVASCRIPT-EVENT-trigger which can be used from the developer. The communication runs in both directions and it's possible to change data in the HTML-page through Forms via Javascript

Those two new techniques are indications, that Oracle Forms is ready for the future. It is one of the main SOA-parts of the new Oracle Fusion Middleware 11g.

Additional information to Forms 11g can be found in the German Forms Community

Thanks to DOAG

New Statement of Direction, Nov. 2007

This is the actual timeline for Oracle Forms

Forms 11g new features: Events

My DOAG Conference 2007 presentation was "Forms 11g: A look behind the scenes"

The main two new features are: Advanced Queuing and Javascript-API. Today I explain the AQ-technique with Forms-Events.

Events - Interaction with Advanced Queuing

This is the first big new change in Forms Builder since 6 years. We can create Events and assign them to queues. If someone inserts new data in this AQ, the appropriate new trigger WHEN-EVENT-RAISED starts.


Allowed Properties for Events

Event Type        : Data Base, User Defined
Subscription Name : AQ-Name (LOV)
Scope             : Application, Form
Auto Subscribe    : Yes, No
Correlation ID    :
View Mode         : Browse, Locked, Removed

formsweb.cfg

maxEventWait=1000

The new parameter maxEventWait is important. The value is in milliseconds and means the intervall, in which the AQ's used by forms get refreshed against the database. Without this parameter the form refreshes only, if the user interacts with the runform.

Code-Snippet

BEGIN
:CONTROL.TI_Payload := get_event_object_property ('EV_Default', Event_Payload);
IF upper (:CONTROL.TI_Payload) = 'ATTACH DEBUG' THEN
 DEBUG.Attach;
 ...
END IF;
END;

This example is a remote control for remote debugging. You can insert a command into the queue, which is assigned to the Event EV_DEFAULT and each time, when the payload has the value ATTACH DEBUG a debug-process starts in the forms runtime.

In my Download-Section is a link to my presentation (in german).

DOAG Conference 2007

Today was the last day of this years conference. The DOAG (German Oracle User Group) is now 20 years old and so we had some slots in the conference like "Oracle 5.0 New Features)

That was a funny presentation from Dierk Lenz. He used his 20 years old slides and presented us all the new features from the database in 1988.

In the presentation he started a vmWare with DOS 6.22 and SQL*Forms 2



In this presentation we were nearly 200 people ! To have a look on the new Features of Oracle 5.0 :-)

Forms Startup (part 1)

This is the first article of a serial. Different methods of enhancing the Forms Startup-Phase are discussed here. In most projects I saw techniques, where the Browser-HTML were changed, so that the user cannot easily close the browser for example.

My first example is from Francois Degrelle. He gave me the tip with a self-hiding Internet Explorer on Forms Startup. The only parameter which has to be changed is the HTMLbodyAttrs in the formsweb.cfg:

HTMLbodyAttrs=onLoad='javascript:self.moveTo(2000,2000)'

After the forms-application-startup the browser-window moves to the x,y-position 2000, 2000. This is far behind the normal range of the visible screen. And so the user thinks, that the Browser closed hisself. The icon in the taskbar is still available, but the Browser didn't went visible, when the user clicks on the task-symbol.

pro:
- the user thinks, that the browser hides hisself
- only the forms-runtime window is visible (in separateFrame=True - mode)

contra:
- the active task of the browser is visible and can't be activated, only closed

this is actually my favorite technique

Bigger. taller. better :-)

I played a little bit with the Template-HTML and found better properties for my Blog.

The old width for example was a bit problematic, because sourcecodes were often too long for the small size of the blog. Now I have 50% more space for my snippets :-)

Easy logging and debugging in Forms

Each forms-application needs a simple way to log errors and find them. This technique can be used also to debug Forms, Reports and pure PL/SQL.

First create the table, sequence and view to store the logging-information:

CREATE TABLE Logging (
  ID                         NUMBER(8,0) NOT NULL,
  SESSION_ID                 NUMBER(8,0),
  INSERT_DATE                DATE NOT NULL,
  TEXT                       VARCHAR2(2000) NOT NULL);

CREATE SEQUENCE Logging_SEQ;

CREATE OR REPLACE VIEW V_Logging_desc 
         (ID, SESSION_ID, INSERT_DATE, TEXT)
AS SELECT ID, SESSION_ID, INSERT_DATE, TEXT
     FROM Logging
ORDER BY SESSION_ID DESC, ID DESC;

You need also a package with some functions to start the logging-process

CREATE OR REPLACE PACKAGE PK_DEBUG IS
  FUNCTION Debug_allowed RETURN BOOLEAN;
  FUNCTION Next_ID       RETURN NUMBER;

  PROCEDURE Disable;
  PROCEDURE Enable;
  PROCEDURE Destroy;
  PROCEDURE Init  (P_Debug_allowed IN BOOLEAN DEFAULT TRUE);
  PROCEDURE Write (P_Text IN VARCHAR2,
                   P_Session_ID IN NUMBER DEFAULT NULL);

  G_Debug_allowed BOOLEAN := TRUE;
  G_Session_ID    NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY PK_DEBUG IS
FUNCTION Debug_allowed RETURN BOOLEAN IS
BEGIN
  RETURN (G_Debug_allowed);
END;

FUNCTION Next_ID RETURN NUMBER IS
  V_ID NUMBER;
BEGIN
  SELECT Logging_SEQ.nextval
    INTO V_ID
    FROM DUAL;
  RETURN (V_ID);
END;

PROCEDURE Disable IS
BEGIN
  G_Debug_allowed := FALSE;
END;

PROCEDURE Enable IS
BEGIN
  G_Debug_allowed := TRUE;
END;

PROCEDURE Destroy IS
BEGIN
  Write ('----------------------stopp '
    || to_char (G_Session_ID) || '--');
  G_Session_ID := NULL;
END;

PROCEDURE Init (
  P_Debug_allowed IN BOOLEAN DEFAULT TRUE) IS
BEGIN
  G_Debug_allowed := P_Debug_allowed;
  G_Session_ID := Next_ID;
  Write ('--start ' || to_char (G_Session_ID)
    || '----------------------');
END;

PROCEDURE Write (
  P_Text       IN VARCHAR2,
  P_Session_ID IN NUMBER DEFAULT NULL) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF Debug_allowed THEN
    IF G_Session_ID IS NULL THEN
      Init;
    END IF;
    INSERT INTO Logging (ID,
      Session_ID, Insert_Date, Text)
    VALUES (Next_ID,
      NVL (P_Session_ID, G_Session_ID),
      Sysdate, P_Text);
    COMMIT;
  END IF;
END;
END;
/

You start the debug with INIT and end it with DESTROY. Error-Messages are logged through WRITE. For example:

pk_Debug.Write ('Hello World - ' || V_Test);

Parts of your debugging can be deactivated with DISABLE and from this point on nothing will be written into the logging-table till ENABLE is called.

The view V_Logging_desc shows you the logging-data, grouped by the newest session-id.

ID Session Insert-Date     Text
============================================
24    21   10.09.-12:38:48 -------stopp 21--
23    21   10.09.-12:38:48 Hello World - 42
22    21   10.09.-12:38:48 --start 21-------

Try it and have fun
Gerd

San Francisco Impressions

Here are some impressions from the city, shot in the days after the test-event

From Lombard Street to Coit Tower

17 Miles Drive

Muir Woods

Another proof: Second Life exists in reality and not in virtuality

Stone Art in Sausalito

The Golden Gate

Oracle Fusion Middleware 11g Beta Test - Last Day

Today was the last Day of the Beta-Test. After the last testings started the big Feedback-Session in Oracle's Conference Center.

All customers which tested this week and fifty Oracle experts came together, plus dozens through a Web-Conference. In the next 3 hours all products of the Fusion Middleware-Stack got feedback from us. The only tester for Oracle Forms (myself) gave this feedback to Oracle:

First of all: Forms is a really big platform in germany and this Beta 2 program showed us in the last days the first Oracle Forms, which has new functionality in the Forms Builder itself.

After that I described the new features and how important they are for the Forms Community.

In the end I had time to explain, which functionality I personally wish to have in the Forms Builder :

* an object called Web-Service at form-level
* the integration of BI-Beans
* and most important: a new PL/SQL-Editor

That was my statement and now I have to thank Oracle for the invitation to this event, Duncan and his group for the best support that you can have in such a week. It was fantastic to have the chance to work with such profis.

And the last thanks went to Rolf (my boss) who sponsored this trip, Manuela (who let me go) and Andreas, who backup'd me for this week in Krefeld. Many many thanks to you !

This must be the proof, that Duncan's career started in the NBA, before Oracle found him (I'm 6'7"):

PS: If you wonder, why I don't name the new features by name or describe, which functionality they have. Please excuse for that, but I had to sign many Documents at Oracle, which don't allow that.

Forms Beta-Test, Day 4

Duncan and I invented a new feature in mind some days ago and today I was able to develop it in Forms 11g and have now a showcase:

Remote Debugging-Start through AQ

The idea is simple: A developer enqueues a payload to a Forms-AQ, where all running forms-applications have to listen on. Then the payload is transfered to a specific forms-runtime and starts a new AQ-Trigger. The user is asked, if he wants to switch into debugging mode. After a Yes the form starts a debug.attach and developer can use his Forms Builder to attach to the clients runtime.

This is a new and interesting remote debugging extension which can be used in Forms 11g.

Forms Beta-Test, Day 3

Today Duncan and Phil Kuhn worked hard on some of my forms issues. It's great to see, how they and the team behind them work.

It's the first Oracle Forms Version since seven years, which has new functionality, new objects and new triggers in the Oracle Forms Builder!

All those, who propagated the end of Oracle Forms get invalidated, when the new release is launched.

Forms Beta-Test, Day 2

The second day of the Beta-Test started with a big overview of the new Application Server. Four sessions were presented on this tuesday. The rest ot the time was testing Oracle Forms 11g.

At the end of the day Oracle invited us to a nice restaurant near the HQ.

Invitation to Oracle Forms beta-testing

Oracle HQ in Redwood Shores invited me to participate at the 2007' Beta-Test for the Fusion product-stack.

Today we started at Oracle HQ with 16 other companies. In addition to us testers Oracle supports us with nearly 50 Oracle Cracks. For example Duncan Mills as Oracle Forms Guru

They created large test-szenarios for our 5 day testings. So we can test all parts of the new versions and look deep into them.

The spirit of the event is great!

Set Record-Status to Query after a POST-QUERY

In a block with a POST-QUERY-trigger you often have the problem, that the Record-Status changes to CHANGED, if non-basetable-items were changed through the POST-QUERY.

Setting the recordstatus back to QUERY is a good method to solve this problem. First create a procedure for setting the record-status to QUERY:

PROCEDURE Set_Record_Query_Status IS 
BEGIN
  Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'), 
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'), 
                       STATUS, 
                       QUERY_STATUS);
END; 

then use it in your POST-QUERY-triggers:

BEGIN
  SELECT someColumns
    INTO :myBlock.nonBasetable_Item
    FROM myTable
   WHERE someFilter;

  Set_Record_Query_Status;
END;

After resetting each record to QUERY-Status you don't have problems with the fetched data in this block.

Important: If the POST-QUERY changes Basetable-Items in the record you create record-locks, if the block-locking is Immediate. This problem can be solved:

BEGIN
  Set_Block_Property ('myBlock', LOCKING_MODE, Delayed);

  SELECT someColumns
    INTO :myBlock.nonBasetable_Item
    FROM myTable
   WHERE someFilter;

  Set_Block_Property ('myBlock', LOCKING_MODE, Immediate);

  Set_Record_Query_Status;
END;

try it
Gerd

Oracle Certified Trainer

Since last Friday I'm an Oracle Certified Trainer. It is the precondition to work for Oracle University.

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

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

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"

German Weekday

The big problem of the date-conversion "to_char (sysdate, 'D')" is, that depending on the NLS you get different results:

Sunday is the first day in the US
Monday is the first day in Germany

For the Green-Thursday of 2000 you get:

Green_Thursday := to_date ('23.03.2000', 'DD.MM.YYYY');
America: to_char (Green_Thursday, 'D') = 5
Germany: to_char (Green_Thursday, 'D') = 4

this is sub-optimal, because it's a format-mask, which is based on the underlying NLS and not on a strict rule.

My solution in this case is: The function German_Weekday

FUNCTION German_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 4 in Germany
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'),
                                 'D')) - 4;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

and American_Weekday

FUNCTION American_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 5 in the US
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'), 
                                 'D')) - 5;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

this function always returns the correct german 'D'-formatmask, indepent of the NLS.

have fun and use it
Gerd

Forms 11g Release Date

Nobody knows the day, when Forms 11g will be released.

But the newest official news about "when" and "where" is:

"Version 11 of Forms will be in the production version of Application Server version 11 which is currently scheduled for fiscal year 2008."

Read the post in the OTN-Discussion Forum

Oracle Develop in Munich

The start of the week I attend at the Oracle Develop in the Arabella Sheraton Munich. Nice place, but we had to run between the presentation from one hotel to another. And the coffee machine was only in the hotel, where only one slot from five slots were...

Day 1:

Monday morning I started with Frank Nimphius presentation about Forms, the future of Forms and the integration into Java, SOA, ...

Marc Sewtz and his "SQL Developer Features" was nice, because we saw a bunch of new features in the actual release.

After lunch the famous Bryn Llewellyn started his "PL/SQL Performance: Debunking the Myths". Gorgeous! Next time I hope he gets 2 hours for all the tips and tricks he explained

Parallel to the presentations Oracle served us 3 labs. Monday evening I went into the APEX 3.0 Lab, where we learned, how to work with the new version of Application Express.


Day 2:

Bad news at the coffee-front. We had to get our coffee in Hotel A and run some 100 meters down the road to Hotel B...

Tuesday I started with the next lab: "Developing and Deploying Oracle and PHP". Interesting to see, how easy it can be, working with PHP

After that Bryn presented the new PL/SQL-Enhancements of the new Oracle DB 11g. I love compound triggers ! And all the other stuff too. Accessing sequences without selecting against DUAL !!

Post-Lunch Frank explained "Building Rich UI using JavaServer Faces and AJAX. That was another highlight too me at the conference.

Interesting, how much Microsoft-Stuff was presented through many slots. The "Microsoft Interoperability with Oracle Fusion Middleware" showed the SOA-integration made by the Office-Toolstack. Nice to see a different world to Oracle.

Summary:

Presentations and labs: TOP
No Coffee: FLOP

New Forms Look & Feel

Good news for all Forms-Developer who need a new Look & Feel for their applications.

In Grant's newest interview he spoke with Francois Degrelle, about his Forms Look & Feel White Paper from April 2007.

Faster Forms-Start with synchronize

I can't believe it, but it is true in some cases !

If you have a forms-application and some form-starts are too slow in your mind, then you can try to use a synchronize to speed up the initial display. The user now thinks, that the form itself starts faster, but internally only the first display-refresh is faster.

WHEN-NEW-FORM-INSTANCE - trigger 

BEGIN
  synchronize;
  -- your WHEN-NEW-FORM-INSTANCE-code
END;

try it and believe it too !

EOUC 2007 has been canceled

oh no...

the EMEA-Oracle-User-Council-Conference in Amsterdam has been canceled:

EOUC 2007

Update Dez. 2007: the URL is now canceled too

Multi-Select from DUAL

An easy way to generate records from scratch is using an easy CONNECT BY against DUAL.

e.g. you need a Forms-LOV which shows the last 12 months.
So you have to create a record-group-select which gives you exactly 12 records. After that you combine it with sysdate. Let's see:

 SELECT Level LVL
   FROM Dual
CONNECT BY Level <= 12;

then you integrate the sysdate into the statement:

 SELECT add_months (trunc (sysdate, 'MM'), -1*Level) Month
   FROM Dual
CONNECT BY Level <= 12;

MONTH
--------
01.03.07
01.02.07
01.01.07
01.12.06
01.11.06
01.10.06
01.09.06
01.08.06
01.07.06
01.06.06
01.05.06

isn't that a pretty easy solution for getting generically the last 12 months?

Assertions

Using assertions in sourcecodes is well known in Java and other programming-languages, but not in PL/SQL. Why?

That's a good question and I solved it for myself through using this technique:

DECLARE
  e_Assertion EXCEPTION;
BEGIN
  IF condition1 = 'value'
  OR boolean = TRUE
  OR something_else THEN
    RAISE e_Assertion;
  END IF;

-- your code:
...
EXCEPTION
  WHEN e_Assertion THEN
    NULL;
  WHEN OTHERS THEN
    -- when-others-exception-handling
END;

In this example you write all your negative assertions under each other and raise the assertion-exception, which does nothing in the exception-handling.

e.g.

PROCEDURE Double_Manager_Salary (P_EMPNO IN NUMBER, P_JOB IN VARCHAR2) IS
  e_Assertion EXCEPTION;
BEGIN
  IF P_Job != 'MGR' THEN
    RAISE e_Assertion;
  END IF;

  UPDATE EMP SET
         SAL = SAL * 2
   WHERE EMPNO = P_EMPNO;

EXCEPTION
  WHEN e_Assertion THEN
    NULL;
END;

what we see here is very simple: If you assert, that only manager get doubled salaries, then you cancel the procedure directly after it starts. You jump into the e_Assertion-Exception and do nothing.

Try and use it
Gerd

Invitation to EOUC 2007

The EMEA Oracle User Council's conference is this year in Amsterdam, Netherland, from May 2nd - May 3rd.

My abstract, sent to the conference some months ago was:

Oracle Forms 10g and the integration into BPEL

And now I got an invitation for May 3rd

One Time Timer

Many times I want to code a go_item, go_block or execute_query while validating an item. But restricted functions can't be used in many triggers. So we need a workaround.

And here comes my "One Time Timer" :

Example: I have a non-basetable control-block with some items. Below this block is a multi-record block based on EMP. The control-block should be used as filter on the EMP-block.

The user wish to enter filter-criteria in the master-block and when navigating to the next item, they automatically want a new query-result in the block EMP. This is impossible with standard validation-triggers, because the navigation has to go into a block and execute a query, while validating an item.

Solution: create a form-level WHEN-TIMER-EXPIRED:

DECLARE
 V_Item   VARCHAR2 (61);
BEGIN
 V_Item := :SYSTEM.CURSOR_ITEM;

 IF One_Time_Timer.Get_Value = Const.ott_Query_in_EMP THEN
   Go_Block ('EMP');
   Execute_Query;
   Go_Item (V_Item);
 ELSIF One_Time_Timer.Get_Value = Const.ott_Something_Else
 THEN
   -- if more One-Time-Timer are needed,
   -- create one for each Branch
   NULL;
 END IF;
END;

create a Const-Package with some constants:

PACKAGE Const IS

-- Globals
 gbl_One_Time_Timer CONSTANT VARCHAR2 (61) :=
   upper ('global.One_Time_Timer');

-- One-Time-Timer
 ott_Query_in_EMP   CONSTANT VARCHAR2 (30) :=
   'Filter EMP-Block';
 ott_Something_Else CONSTANT VARCHAR2 (30) :=
   'Something else';

END;

and a package for general functions:

PACKAGE One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2;
 PROCEDURE Initialize (P_Event IN VARCHAR2);
END;

PACKAGE BODY One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2 IS
 BEGIN
   Default_Value (NULL, Const.gbl_One_Time_Timer);
   RETURN (NAME_IN (Const.gbl_One_Time_Timer));
 END;

 PROCEDURE Initialize (P_Event IN VARCHAR2) IS
   tm_id   timer;
   tm_name VARCHAR2 (30) := 'ONE_TIME_TIMER';
 BEGIN
   tm_id := Find_Timer (tm_name);
   IF ID_Null (tm_id) THEN
     tm_id := Create_Timer (tm_name, 10, NO_REPEAT);
     COPY (p_Event, Const.gbl_One_Time_Timer);
   END IF;
 END;
END One_Time_Timer;

the control-block (named "Filter") has e.g. two items: ENAME and SAL

create a WHEN-VALIDATE-ITEM on ENAME :

BEGIN
 One_Time_Timer.Initialize (Const.ott_Query_in_EMP);
END;

last step: the EMP-block needs a PRE-QUERY-trigger on block-level:

BEGIN
 IF :Filter.ENAME IS NOT NULL THEN
   :EMP.ENAME := :Filter.ENAME;
 END IF;
END;

what happens?
After changing the value of ENAME in the FILTER-block the WHEN-VALIDATE-ITEM fires. He initializes the One-Time-Timer. The "global.One_Time_Timer" get the value of Const.ott_Query_in_EMP (which is "Filter EMP-Block"). After that a timer is created which fires 10 ms later.

10ms later:
The WHEN-TIMER-EXPIRED fires and does an execute_query in the EMP-block and returns afterwards back to the original item. In the EMP-block starts the PRE-QUERY and the data of the EMP-block gets filtered through ":EMP.ENAME := :Filter.ENAME"

that's the whole story. Try it and have fun!