Handle a CLOB column with Forms 6i and 9i

03.05.2006

I notice sometimes questions about how to handle CLOB columns in a Forms 6i / 9i application.

It seems to be very difficult to use CLOB variables within Forms to handle correctly the whole datum.

For the purpose of creating a file on disk with the content of a database CLOB column, this is a workaround.

It needs a stored package that can handle the CLOB column and variable.

CREATE OR REPLACE PACKAGE Pkg_Clob
IS

-- populate the CLOB column --
FUNCTION select_clob ( PN$id IN NUMBER ) RETURN BOOLEAN ;

-- Get and return chunks of 4000 bytes --
FUNCTION Get_Chunk RETURN VARCHAR2 ;

END ;
/

CREATE OR REPLACE PACKAGE BODY Pkg_Clob
IS

GL$Clob CLOB ; -- global CLOB variable
GN$Pos PLS_INTEGER := 1 ; -- global current pos in the CLOB

-- Get the content of the CLOB column --
FUNCTION select_clob ( PN$id IN NUMBER ) RETURN BOOLEAN
IS
BEGIN

    SELECT TEXT
    INTO    GL$Clob
    FROM    TEST_CLOB
    WHERE   ID = PN$id ;

    GN$Pos := 1 ;

    RETURN TRUE ;

EXCEPTION
WHEN OTHERS THEN
RETURN FALSE ;
END select_clob ;

-- Return a 4000 bytes chunk of the selected CLOB --
FUNCTION Get_Chunk RETURN VARCHAR2
IS
    LC$Chunk VARCHAR2(4000) ;
BEGIN
    LC$Chunk := SUBSTR( GL$Clob, GN$Pos, 4000 ) ;
    GN$Pos := GN$Pos + 4000 ;
    RETURN LC$Chunk ;
EXCEPTION
    WHEN OTHERS THEN
   RETURN NULL ;
END Get_Chunk ;

END ;
/

Then I can get Chunk of 4000 bytes in the Forms application and pass them to the TEXT_IO.PUT() or CLIENT_TEXT_IO.PUT() functions:

PROCEDURE Get_CLOB_Chunks IS

LC$Chunk Varchar2(4000) ;
LN$Cpt pls_integer := 0 ;

BEGIN

If pkg_clob.select_clob(11) then

    Loop
      LC$Chunk := pkg_clob.get_chunk ; -- got the 4000 bytes chunk
      Exit when LC$Chunk is null ;
      LN$Cpt := LN$Cpt + 1 ;
    End loop ;

End if ;

Message('number of chunks read = ' || ln$cpt ) ;

END;

This the description of the sample table tested:

CREATE TABLE TEST_CLOB
(
ID NUMBER(5) PRIMARY KEY,
TEXT CLOB NULL
)
/