Fun with CLOBS! If you are using Oracle, if you have to deal with text that is over 4000 bytes, you will probably find yourself dealing with CLOBs, which can go up to 4GB. They are pretty tricky, and it took me a long time to figure out these lessons learned. I hope they will help some down-trodden developer out there somehow.

Here is my original code, which worked great on my Oracle Express Edition: (for all examples, the first one writes a new CLOB, the next one Updates an existing CLOB and the final one reads a CLOB back)

CREATE OR REPLACE PROCEDURE PRC_WR_CLOB (
       p_document      IN VARCHAR2,
       p_id            OUT NUMBER)

IS
     lob_loc CLOB;

BEGIN

   INSERT INTO TBL_CLOBHOLDERDDOC (CLOBHOLDERDDOC)
       VALUES (empty_CLOB())
       RETURNING CLOBHOLDERDDOC, CLOBHOLDERDDOCID INTO lob_loc, p_id;

   DBMS_LOB.WRITE(lob_loc, LENGTH(UTL_RAW.CAST_TO_RAW(p_document)),
1, UTL_RAW.CAST_TO_RAW(p_document));


END;
/


CREATE OR REPLACE PROCEDURE PRC_UD_CLOB (
       p_document      IN VARCHAR2,
       p_id            IN NUMBER)

IS
     lob_loc CLOB;

BEGIN

       SELECT CLOBHOLDERDDOC INTO lob_loc FROM TBL_CLOBHOLDERDDOC
       WHERE CLOBHOLDERDDOCID = p_id FOR UPDATE;

   DBMS_LOB.WRITE(lob_loc, LENGTH(UTL_RAW.CAST_TO_RAW(p_document)),
1, UTL_RAW.CAST_TO_RAW(p_document));

END;
/



CREATE OR REPLACE PROCEDURE PRC_RD_CLOB (
   p_id IN NUMBER,
   p_clob OUT VARCHAR2)
IS

   lob_loc  CLOB;

BEGIN

   SELECT CLOBHOLDERDDOC INTO lob_loc
   FROM   TBL_CLOBHOLDERDDOC
   WHERE  CLOBHOLDERDDOCID = p_id;

   p_clob := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lob_loc,
DBMS_LOB.GETLENGTH(lob_loc), 1));

END;
/

As you can see, I had originally been casting everything back and forth between RAW formats using the UTL_RAW.CAST_TO_VARCHAR2() and UTL_RAW.CAST_TO_RAW() functions all over the place, but it had the nasty side effect of working great on my Oracle express edition on my developer box, but having all the CLOBs above a certain size display garbage when read back on the Oracle test database server .

So...I kept working at it and came up with the following, which ALSO worked on my Oracle Express Edition on my developer box:

CREATE OR REPLACE PROCEDURE PRC_WR_CLOB (
    p_document      IN VARCHAR2,
    p_id        OUT NUMBER)

IS
      lob_loc CLOB;

BEGIN

    INSERT INTO TBL_CLOBHOLDERDOC (CLOBHOLDERDOC)
        VALUES (empty_CLOB())
        RETURNING CLOBHOLDERDOC, CLOBHOLDERDOCID INTO lob_loc, p_id;

    DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document);

 
END;
/



CREATE OR REPLACE PROCEDURE PRC_UD_CLOB (
    p_document      IN VARCHAR2,
    p_id        IN NUMBER)

IS
      lob_loc CLOB;

BEGIN

    SELECT CLOBHOLDERDOC INTO lob_loc FROM TBL_CLOBHOLDERDOC
    WHERE CLOBHOLDERDOCID = p_id FOR UPDATE;

    DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document);

END;
/


CREATE OR REPLACE PROCEDURE PRC_RD_CLOB (
    p_id IN NUMBER,
    p_clob OUT VARCHAR2)
IS

    lob_loc  CLOB;

BEGIN

    SELECT CLOBHOLDERDOC INTO lob_loc
    FROM   TBL_CLOBHOLDERDOC
    WHERE  CLOBHOLDERDOCID = p_id;

    p_clob := DBMS_LOB.SUBSTR(lob_loc, DBMS_LOB.GETLENGTH(lob_loc), 1);

END;
/


Unfortunately, by changing my code to what you see above, even though it kept working on my Oracle express edition, everything over a certain size just started truncating after about 7950 characters on the test server!

Here is what I came up with in the end, which is actually the simplest solution and this time worked on both my express edition and on the database server (note that only the read function was changed to fix the truncation issue, and that I had Oracle worry about converting the CLOB into a VARCHAR2 internally):

CREATE OR REPLACE PROCEDURE PRC_WR_CLOB (
       p_document      IN VARCHAR2,
       p_id            OUT NUMBER)

IS
     lob_loc CLOB;

BEGIN

   INSERT INTO TBL_CLOBHOLDERDDOC (CLOBHOLDERDDOC)
       VALUES (empty_CLOB())
       RETURNING CLOBHOLDERDDOC, CLOBHOLDERDDOCID INTO lob_loc, p_id;

   DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document);


END;
/


CREATE OR REPLACE PROCEDURE PRC_UD_CLOB (
       p_document      IN VARCHAR2,
       p_id            IN NUMBER)

IS
     lob_loc CLOB;

BEGIN

       SELECT CLOBHOLDERDDOC INTO lob_loc FROM TBL_CLOBHOLDERDDOC
       WHERE CLOBHOLDERDDOCID = p_id FOR UPDATE;

   DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document);

END;
/



CREATE OR REPLACE PROCEDURE PRC_RD_CLOB (
   p_id IN NUMBER,
   p_clob OUT VARCHAR2)
IS

BEGIN

   SELECT CLOBHOLDERDDOC INTO p_clob
   FROM   TBL_CLOBHOLDERDDOC
   WHERE  CLOBHOLDERDDOCID = p_id;

END;
/

I hope that is useful to someone!