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"

OOW is coming closer

Gosh! Is that a damn hard work to build an oow-schedule in the biggest schedule builder I've ever seen.

Hundreds of good presentations and all of them are parallel at four days and only a few possible timeslots. So you sit before your browser and have to check in to the best presentations. But where are the best presentation when you scroll through 60 parallel slots per time-slot? It's hard work to read all the titles and authors.

and then: Tom's presentation is full - oh no! - a waitlist for those, who came late
but: Steven's isn't full yet! So let's book the last seat - and do it fast

Different presentation

last week was a hard week !

Monday I got my invitation to the Oracle World from the EOUC.

Friday Oracle rejected my presentation about Forms-Integration in BPEL, because they have the same topic. But they gave me the chance to post a different presentation.

This Wednesday I got the invitation from oracle to present BI Beans


Developing Powerful Oracle Business Intelligence Beans in Oracle Forms

BI Beans replaced Oracle Graphics as the new graphical standard in Forms. For this new java based technology forms developers do not need java experience. In the presentation you learn the easy way to install BI Beans, communication between Forms and BI Beans and how to develop powerful graphical applications.

Invitation to OOW 2006

that's it. I got an invitation for a presentation at the Oracle Open World 2006 in San Francisco.

Oracle Forms & BPEL do work hand in hand in the SOA-world

With the come up of service-oriented-architectures it is mandatory that Oracle Forms communicates with the companies automated business processes. In this presentation you'll see, how easy it is to establish a "dialogue" between Oracle Forms and BPEL. The integration will be demonstrated with an example-application: the setup in Oracle Forms, the web-services you need in and around the BPEL-engine and how you poll process-messages in Oracle Forms.

Creating a Forms Framework

Since more than 10 years I'm working with Oracle Forms and deal with the question: How to work efficient with a 4 GL like Forms.

It's very easy to create a form and run it. But it's not that easy to create big applications with hundreds of forms and not to invite the wheel every day.

So you have to invite the wheel once and use it in all forms-applications that have to be developed. The best way is to :

- create a style guide for your application
- define reference-forms and templates for your developers
- build prototypes with your framework

That sounds easy and it is easy, if you have a lot of experience and know, how to work with forms.

Here is the link to my new sourceforge-project, where I publish the sources for this framework, the guidlines to work with the framework and more...

http://sourceforge.net/projects/forms-framework/

Some topics of the style guide are
- exception handling
- dynamic message handling
- access right for forms and special functions
- dynamic menues for the start-application
- PL/SQL-libraries

Using CLOB's in Forms 6i

It's not possible to use the new datatype CLOB in Oracle Forms 6i.

So let us invent a workaround !

Assumption: Table EMP in the db has a new column Note_CLOB, A form with the block EMP and all EMP-columns without the CLOB.

Requirement: The CLOB-column from the db should be shown and editable in the form.

First create a textitem "Note_CLOB" in the block EMP:

Item-Properties:
- Data Type CHAR
- Maximum Length 32000
- Database-Item No

Item-Trigger:
- WHEN-VALIDATE-ITEM

:EMP.EMPNO := :EMP.EMPNO;

Block-Trigger:
- POST-QUERY

POQ_EMP;

- PRE-UPDATE

PRU_EMP;

These program units are needed for the form

PROCEDURE POQ_EMP IS
  V_String   VARCHAR2 (1000);
  V_Index    NUMBER := 0;
BEGIN
  LOOP
    V_String := Read_CLOB (:EMP.EMPNO, V_Index);
    IF V_String IS NOT NULL THEN
      :EMP.Note_CLOB := :EMP.Note_CLOB || V_String;
      V_Index := V_Index + 1;
    ELSE
      EXIT;
    END IF;
  END LOOP;
END;

PROCEDURE PRU_EMP IS
  V_String   VARCHAR2 (1000);
  V_Index    NUMBER := 0
BEGIN
  Clear_CLOB (:EMP.EMPNO);
  LOOP
    V_String := Substr (:EMP.Note_CLOB, 1 + V_Index * 1000, 1000);
    IF V_String IS NOT NULL THEN
      Append_CLOB (:EMP.EMPNO, V_String);
      V_Index := V_Index + 1;
    ELSE
      EXIT;
    END IF;
  END LOOP;
END;

This routines are needed in the database

PROCEDURE CLEAR_CLOB (P_EMPNO IN EMP.EMPNO%TYPE) IS
BEGIN
  UPDATE EMP SET Note_CLOB = NULL
   WHERE EMPNO = P_EMPNO;
END;

PROCEDURE APPEND_CLOB (P_EMPNO IN EMP.EMPNO%TYPE, P_STRING IN VARCHAR2) IS
BEGIN
  UPDATE EMP SET Note_CLOB = Note_CLOB || P_String
   WHERE EMPNO = P_EMPNO;
END;

FUNCTION READ_CLOB (P_EMPNO IN EMP.EMPNO%TYPE, P_INDEX IN NUMBER) RETURN VARCHAR2 IS
  V_STRING VARCHAR2 (2000);
BEGIN
  SELECT SUBSTR (Note_CLOB, 1 + P_Index * 1000, 1000)
    INTO V_String
    FROM EMP
   WHERE EMPNO = P_EMPNO;
  RETURN (V_String);
END;

with this technique it is possible to read and write 32K from a CLOB.

try and test it