Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

3 mai 2006 3 03 /05 /mai /2006 16:34
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
)
/

Partager cet article

Repost 0
Francois Degrelle - dans Oracle Forms
commenter cet article

commentaires

Nicolas DENIS 05/08/2010 10:58



Hello,


 


I find a faster way for use with Oracle 10g database/Forms 10g


 


A simple query do the job :)


select level chunkID, dbms_lob.SUBSTR(cLOBData,4000,((level-1)*4000)+1) ChunckVal from dual


connect by level