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

23 comments:

Anonymous said...

Excellent , was very usefull

Anonymous said...

Really Superb.
Works great.

Anonymous said...

GRACIAS
MUY BUEN TRABAJO!!!

Anonymous said...

what is the une of empno? and ist belong to the table? im trying to use your codes right now thanx

best regard

Anonymous said...

WHEN I TRY YOUR CODE IT PROMPT ME AN ERROR

erro306 at line 4, column 18
wrong number or types of argument in call to '||'

- think the problem is in the concatenation. wherein concatenating a clob and varchar2.

Gerd Volberg said...

please can you debug it and show me the line of code, where you have the problem? And what database version did you use?

Anonymous said...

im using oracle 8.0 as a database and forms 6i as a front end

Anonymous said...

-Im getting a problem in this part.

PROCEDURE APPEND_CLOB (P_STRING IN VARCHAR2) IS
BEGIN UPDATE E_FINAN
SET FINANCIAL = FINANCIAL || P_String
WHERE ID = :B_1.ID AND NUM= :B_1.NUM;
END;

-it prompt me an error of
Error 306 at line 3, column 22
wrong number or types of argiments in call '||'

-the version of my database is

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

thanx again and best regard. God bless

Anonymous said...

the FINANCIAL there has a clob data type in the database

Anonymous said...

-is there any other way to concatenate a clob datatype and varchar 2 because it always prompt me that error.

-is forms 6i is also applicable for concatenating a clob datatype to varchar2?

thanx for your help.

Anonymous said...

-when i try it in the database the procedure append_clob was created

SQL> ed
Wrote file afiedt.buf

1 create or replace PROCEDURE APPEND_CLOB (P_STRING IN VARCHAR2) IS
2 BEGIN
3 UPDATE E_FINAN
4 SET FINANCIAL = FINANCIAL || P_String
5 WHERE ID = '602' AND NUM= 'P1102156';
6* END;
SQL> /

Procedure created.

Anonymous said...

please help me... thanx

Anonymous said...

hello BTW im Eduard. im still working with the project right now bout clob datatype and i thanking your codes because it has a big help to me but u have still a problem. im the anonymous that ask you question in this blog thanx.

im still waiting for your answer and hope to share the knowledge that God has given you thanx

Jonathan Whitehead said...

Thanks for posting this information Gerd, it help me out of a tricky spot with upgrading our db from 9i to 10g but still running forms6i until we get them upgraded to forms 10g.

Unknown said...

This is great. How does this need to be tweaked to get insert accomplished? suppose the user enters a brand new clob record through the forms?

Gerd Volberg said...

The user only can create a new record. And one of the columns is the CLOB. So you don't have to worry about creating a new CLOB, because it is implicitly done throne the insert into table.

Rosales said...

when the clob exceeds the 32000 fault. remember that a clob can accommodate 4GB of text. the method proposed is inefficient in the text above 32,000 characters.

Gerd Volberg said...

Hi Rosales, that's, why I wrote "it is possible to read and write 32K from a CLOB". An exception handling could be helpful on strings >32K

hiten said...

If you are using oracle form in e-biz (oracle apps) environment, have a look at an alternative solution on the following link

http://tenthsense.blogspot.com/2012/12/display-clob-data-in-oracle-forms.html

This solution does not have the limitation of 32K.

Unknown said...

updating a record having clob column is not working.
error-frm40401

Gerd Volberg said...

FRM 40401 means "no changes to commit". That's more an info than an error. Did you check, if the data is changed and saved in the database after commiting it in Forms?

Thomas said...

I tried this

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

and work fine
do i missed something ?

Gerd Volberg said...

it's up to you Thomas. In my case I work with a nonbasetable-item.