Read data from CLOB data type

[ebrian]

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:

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;