Read data from CLOB data type
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Read data from CLOB data type

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Read data from CLOB data type

    How can I read the data in my 8k CLOB column ? This column has been created as 'inline' but since the size is >4K, I think it is stored offline.
    All subprograms in dbms_lob package read the data as "(CLOB)" and give me the length as 6, rather than giving me the real data.

  2. #2
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Any suggestions/comments ?

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    If you inserted data greater than 4k in size, then it got stored out-of-line. Are you saying that DBMS_LOB.GETLENGTH(<clob_column>) is showing 6K?

    Have you tried to select * from <table_name>;

    Anyway, if you want to read the CLOB from PL/SQL, the following should work:

    Code:
    CREATE OR REPLACE PROCEDURE readLOB_proc IS
        Lob_loc           BLOB;
        Buffer            RAW(32767);
        Amount            BINARY_INTEGER := 32767;
        Position          INTEGER := 1000;
        Chunksize         INTEGER;
    BEGIN
       SELECT <clob_column> INTO Lob_loc
          FROM <table_name>;
       Chunksize := DBMS_LOB.GETCHUNKSIZE(Lob_loc);
       IF (Chunksize < 32767) THEN
          Amount := (32767 / Chunksize) * Chunksize;
       END IF;
       DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
    END;
    Last edited by ebrian; 05-09-2006 at 08:37 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width