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