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!

Undo

Retrieving data from the database and changing the data is really easy. But what, if the user changes data and want to do an UNDO?

Doing a new query is the easiest way. The limitations are:

- in a multi-record-block you have to position in the correct record after the query
- if the query was executed via ENTER-QUERY mode you can't jump to the old record because the query-result has changed.

So you have to use a new technique.

The solution is this function. All database-items get their old values back:

PROCEDURE Undo IS
  V_Block  VARCHAR2 (30) := :SYSTEM.CURSOR_BLOCK;
  V_Field  VARCHAR2 (61);
  V_Item   VARCHAR2 (61);
BEGIN
  Validate (Item_Scope);
  IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN
    V_Field := Get_Block_Property (V_Block, FIRST_ITEM);
    V_Item := V_Block || '.' || V_Field;
    WHILE V_Field IS NOT NULL
    LOOP
      IF Get_Item_Property (V_Item, ITEM_TYPE) 
         IN ('DISPLAY ITEM', 'CHECKBOX', 'LIST', 
             'RADIO GROUP',  'TEXT ITEM')
      AND Get_Item_Property (V_Item, BASE_TABLE) = 'TRUE' 
      THEN
        COPY (Get_Item_Property (V_Item, DATABASE_VALUE), 
              V_Item);
      END IF;
      V_Field := Get_Item_Property (V_Item, NextItem);
      V_Item := V_Block || '.' || V_Field;
    END LOOP;
  END IF;
END;

Best practice is to start this undo-procedure from a menu (e.g. EDIT - UNDO) or handle it through a shortcut.

Equal and UnEqual

Sometimes you have to check the Equality of two variables.

Writing "IF A = B THEN" is not the solution for all cases. If one variable is NULL the whole statement is NULL and NULL becomes FALSE in an IF-Statement. So you have to work with a different technique:

FUNCTION Equal (P_String1  IN VARCHAR2,
                P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 = P_String2
  OR (P_String1 IS NULL AND P_String2 IS NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

FUNCTION UnEqual (P_String1  IN VARCHAR2,
                  P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 != P_String2
  OR (    P_String1 IS     NULL
      AND P_String2 IS NOT NULL)
  OR (    P_String1 IS NOT NULL
      AND P_String2 IS     NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

Now you can easily use Equal and UnEqual

IF UnEqual (Var1, Var2) THEN
  -- do something
ELSE
  -- do something different
END IF;

try it

ON-ERROR and ON-MESSAGE-trigger

Many developer have problems with messages which popup in forms, for example "FRM-40401: No changes to save".

Then they look for workarounds and one of the easiest is manipulating the :system.message_level:

KEY-COMMIT - trigger on form-level (quick and dirty)

BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 5;
END;

or KEY-COMMIT - trigger

DECLARE
 V_Message_Level NUMBER;
BEGIN
 V_Message_Level := :System.Message_Level;
 :System.Message_Level := 25;
 COMMIT;
 :System.Message_Level := V_Message_Level;
END;

these are not best practices. Because you have to write those codes in hundreds of procedures and tons of code.

Filtering the errors and messages is the key to have a powerful message-handling. Here is an easy procedure which shows you the technique:

ON-ERROR - trigger on form-level

DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
  V_DBMS_Error_Code  NUMBER;
  V_DBMS_Error_Text  VARCHAR2 (2000);
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;
 V_DBMS_Error_Code := DBMS_Error_Code;
  V_DBMS_Error_Text := DBMS_Error_Text;
 
  IF V_Error_Code IN (40401, 40405) THEN
    /*
    || 40401, 40405 - no changes to save / apply  get filtered
    */
    NULL;
  ELSIF V_Error_Code IN (-1034, -3114) THEN
    /*
    || -1034, -3114 - not connected to database
    */
    Message ('Not connect to database, exiting Form');
    Exit_Form (no_validate);
  ELSIF V_Error_Code IN (40508, 40735)
  AND   V_DBMS_Error_Code BETWEEN -20999 AND -20000 THEN
    /*
    || -20000 errors are raised by RAISE_APPLICATION_ERROR 
    || They are handled in a different way
    */
    Show_and_Log_DB_Error (V_DBMS_Error_Text);
  ELSE
    /*
    || All other errors went into Show_and_Log_Error, where they
    || get inspected, analyzed and logged.
    */
    Show_and_Log_Error (V_Error_Code);
  END IF;
END;

OOW Summary

Here you see the Howard-Street, during the OOW 2006. The whole street was one big tent:



























Regis Louis and his overview about JDeveloper 11g was very refreshing, because the toolset is the center of Oracles new Fusion-Technology.




















Steven Feuerstein discused in his presentations new ways to create exception-handling in PL/SQL and how to use a professionell unit-testing-software like utPLSQL.




















Very interesting was his announcement, that he will publish a new application named Quest Code Tester, which helps you creating test-cases for automated PL/SQL-unittests. Production Releases are available in 6 months. This is the link to the new homepage for all tools around those new applications:




http://www.toadworld.com/




Bryn Llewellyn (creator of PL/SQL) showed us in his "Meet the Guru"-hour the new features of the Oracle Database 11.

The most powerful new topic is the "edition". This means that you can create a complete new version of a package / view / table. With an easy "alter system set edition = ..." you change to different versions.

e.g. when you have a set of new packages and want test them on the production-db. So you create the new packages in a new "edition". After that you can change the usage of the two versions online while the database is running. Testing the new packages and reseting to the old ones is done in seconds !














These were some of the most interesting news of the Oracle Open World

Oracle Open World 2006

Big, bigger, moscone! This is what I'm aware about, when thinking back to this years Oracle OpenWorld.

42000 people attend Larry's big show and it become more and more each year


Larry's announcement this year was: "We give you Red Hat Linux-Support, better than anybody else in the market and less expensive".


This years toy :

Arriving in San Francisco

Yesterday was the starting day of my oow-vacation.

Here are some impressions of the first night. Today at 7 AM was starting time of the Nike's Woman Marathon in San Francisco at the Union Square

The best runner started at 6:40.

AOUG Conference 2006

Yesterday I was invited to hold a presentation at the yearly Austrian Oracle User Group in Vienna.

Did you know Vienna? It's such a lovely city with castle's, old buildings and coffee-houses. You'll love it, too.

My presentation was "Oracle Forms 10g and the communication with SOA via BPEL"