Purpose
Here is a Java Bean to handle CLOB/NCLOB database columns in a Forms application.
It needs a database package to read the LOB data from the database, split into chunks in order to send them to Forms and finally to the JavaBean, and also to send back the modified text from the JavaBean to the database LOB column.
Here is a Java Bean to handle CLOB/NCLOB database columns in a Forms application.
It needs a database package to read the LOB data from the database, split into chunks in order to send them to Forms and finally to the JavaBean, and also to send back the modified text from the JavaBean to the database LOB column.
This package (PKG_CLOB), provided in the zip file, have functions and procedures to handle both CLOB and NCLOB columns.
It uses the EXECUTE IMMEDIATE function to Read and Write via full SQL orders passed as argument, so it is generic.
It uses the EXECUTE IMMEDIATE function to Read and Write via full SQL orders passed as argument, so it is generic.
It has been successfully tested with Forms 10.1.2 and XE database.
The Java code
bigtextarea.java
The Java code
bigtextarea.java
The implementation class of the Bean Item
oracle.forms.fd.TextArea
The methods you can call
Add text to the text item
Set_Custom_Property('BLOCK.ITEM',1,'ADD_TEXT','text
e.g. :
LC$Text := 'Text to send to the bean' ;
Set_Custom_Property( 'BL.BEAN', 1, 'ADD_TEXT', LC$Text ) ;
Use this method to read the database CLOB/NCLOB content then send it to the bean.
Here is a sample code you could write in a Post-Query trigger for instance:
Declare
LN$Length pls_integer ;
LC$Chunk Varchar2(32767) ;
LN$Cpt pls_integer := 0 ;
LC$Req Varchar2(1024);
Begin
----------------------------------
-- Build the Select SQL order --
----------------------------------
LC$Req := 'Select NTEXT From TEST_CLOB Where ID=1' ;
---------------------------------------
-- select the database CLOB column --
---------------------------------------
If pkg_clob.select_nclob(LC$Req) then
Loop
-------------------------
-- get the chunks... --
-------------------------
LC$Chunk := pkg_clob.get_nchunk ;
Exit when LC$Chunk is null ;
LN$Cpt := LN$Cpt + 1 ;
--------------------------------------
-- ... then send them to the Bean --
--------------------------------------
Set_Custom_Property('BLOCK3.BEAN',1, 'ADD_TEXT',LC$Chunk);
End loop ;
---------------------------------
-- display the complete text --
---------------------------------
Set_Custom_Property('BLOCK3.BEAN',1, 'SHOW','');
---------------------------
-- get the text length --
---------------------------
LN$Length := Get_Custom_Property('BLOCK3.BEAN',1,'GET_LENGTH') ;
message('length=' || LN$Length);
Else
message('problem reading CLOB');
End if ;
end;
In this sample, we are selecting the content of the NTEXT NCLOB column from the TEST_CLOB table.
Display the complete text
Set_Custom_Property('BL.BEAN',1,'SHOW','');
Use this method when all teh text chunks have been sent to the bean.
LN$Length pls_integer ;
LC$Chunk Varchar2(32767) ;
LN$Cpt pls_integer := 0 ;
LC$Req Varchar2(1024);
Begin
----------------------------------
-- Build the Select SQL order --
----------------------------------
LC$Req := 'Select NTEXT From TEST_CLOB Where ID=1' ;
---------------------------------------
-- select the database CLOB column --
---------------------------------------
If pkg_clob.select_nclob(LC$Req) then
Loop
-------------------------
-- get the chunks... --
-------------------------
LC$Chunk := pkg_clob.get_nchunk ;
Exit when LC$Chunk is null ;
LN$Cpt := LN$Cpt + 1 ;
--------------------------------------
-- ... then send them to the Bean --
--------------------------------------
Set_Custom_Property('BLOCK3.BEAN',1, 'ADD_TEXT',LC$Chunk);
End loop ;
---------------------------------
-- display the complete text --
---------------------------------
Set_Custom_Property('BLOCK3.BEAN',1, 'SHOW','');
---------------------------
-- get the text length --
---------------------------
LN$Length := Get_Custom_Property('BLOCK3.BEAN',1,'GET_LENGTH') ;
message('length=' || LN$Length);
Else
message('problem reading CLOB');
End if ;
end;
In this sample, we are selecting the content of the NTEXT NCLOB column from the TEST_CLOB table.
Display the complete text
Set_Custom_Property('BL.BEAN',1,'SHOW','');
Use this method when all teh text chunks have been sent to the bean.
Clear the text
Set_Custom_Property('BL.BEAN',1,'CLEAR','');
The properties you can get from the JavaBean
Get the current text length
Varchar2 := Get_Custom_Property( 'BL.BEAN', 1, 'GET_LENGTH' ) ;
Get the current text chunks
Varchar2 := Get_Custom_Property( 'BL.BEAN', 1, 'GET_TEXT' ) ;
Varchar2 := Get_Custom_Property( 'BL.BEAN', 1, 'GET_TEXT' ) ;
Use this method in a loop to get every chunk, then store them in the corresponding CLOB/NCLOB database column.
Here is a sample code you can use in a Post-Insert or Post-Update trigger:
Declare
LC$Chunk Varchar2(32767) ;
LC$Req Varchar2(1024) ;
LC$Msg Varchar2(4000);
Begin
--------------------------------
-- set the UPDATE SQL order --
--------------------------------
LC$Req := 'Update TEST_CLOB Set NTEXT = :1 Where ID=1' ;
-------------------------------------
-- Init the transfer for a NCLOB --
-------------------------------------
PKG_CLOB.Init_Transfer ( 'NCLOB' );
Loop
LC$Chunk := Get_Custom_Property('BLOCK3.BEAN',1,'GET_TEXT') ;
Exit When LC$Chunk Is Null ;
---------------------------------------
-- Send the chunks to the database --
---------------------------------------
PKG_CLOB.Set_NChunk ( LC$Chunk ) ;
End loop;
---------------------------------------------
-- Update the CLOB/NCLOB database column --
---------------------------------------------
LC$Msg := PKG_CLOB.Transfer( LC$Req, 'NCLOB' ) ;
If LC$Msg = 'OK' Then
Forms_ddl('Commit') ;
Else
Message(LC$Msg);
End if ;
End;
LC$Chunk Varchar2(32767) ;
LC$Req Varchar2(1024) ;
LC$Msg Varchar2(4000);
Begin
--------------------------------
-- set the UPDATE SQL order --
--------------------------------
LC$Req := 'Update TEST_CLOB Set NTEXT = :1 Where ID=1' ;
-------------------------------------
-- Init the transfer for a NCLOB --
-------------------------------------
PKG_CLOB.Init_Transfer ( 'NCLOB' );
Loop
LC$Chunk := Get_Custom_Property('BLOCK3.BEAN',1,'GET_TEXT') ;
Exit When LC$Chunk Is Null ;
---------------------------------------
-- Send the chunks to the database --
---------------------------------------
PKG_CLOB.Set_NChunk ( LC$Chunk ) ;
End loop;
---------------------------------------------
-- Update the CLOB/NCLOB database column --
---------------------------------------------
LC$Msg := PKG_CLOB.Transfer( LC$Req, 'NCLOB' ) ;
If LC$Msg = 'OK' Then
Forms_ddl('Commit') ;
Else
Message(LC$Msg);
End if ;
End;
The writting process is done through three steps:
- init the transfer by cleaning the target CLOB/NCLOB package's variable with Init_Transfer()
- send the chunks to the database package to populate the package CLOB/NCLOB variable with Set_Chunk()
- update the database row with the current CLOB/NCLOB variable with Transfer()
The sample dialog
. Download the bigtextarea.zip file
. Unzip the file . copy the bigtextarea.jar file in the <ORACLE_HOME>/forms/java directory . Edit your /forms/server/formsweb.cfg file to add bigtextarea.jar .
. If needed, create the database sample table (create_table_test_clob.sql)
. create the database package (pkg_clob.sql) . Open, compile and run the BIGTEXT.fmb module (Oracle Forms 9.0.2)
0 comments:
Post a Comment