July 25, 2006

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.

July 24, 2006

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

July 21, 2006

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