Geeks With Blogs
My lessons learned while developing!

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!

Posted on Wednesday, March 24, 2010 2:41 PM | Back to top


Comments on this post: Write, Read and Update Oracle CLOBs with PL/SQL

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
I had to remove the UTL_RAW.CAST_TO_RAW part as we store in plain text.

Works fine otherwise. Thanks!!
Left by Sujit on Jul 03, 2012 2:34 PM

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
Just wanted to update for anyone using newer releases of Oracle and have stumbled across this (like I did), you no longer have to use dbms_lob.write to write to CLOBs. Below are some *very* simple examples:

CREATE OR REPLACE PROCEDURE PRC_WR_CLOB (
p_document IN VARCHAR2)

AS

BEGIN

INSERT INTO TBL_CLOBHOLDERDDOC (CLOBHOLDERDDOC)
VALUES (p_document);

END;
/

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

AS

BEGIN

UPDATE TBL_CLOBHOLDERDDOC
SET CLOBHOLDERDDOC = p_document
WHERE CLOBHOLDERDDOCID = p_id;

END;
/
Left by Kyle on Sep 05, 2012 11:54 AM

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
Kyle,
Ive a question for you.
i got a clob field iam dispaying on webpage using oracle mod-plsql like htp.p(cursor1.clob-field, chr(10), <br|);
as you can see iam trying to replace new line with br so the browser can understand but iam having problem if characters are more than 4000. So how can i achieve this fir clob fields...regards
Left by scott mike on Sep 06, 2012 9:04 PM

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
What is p_document?
Please help to get the below done
Update the CLOB column from other table value/comma delimited file
Left by Kiran on Jan 20, 2014 5:23 AM

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
thanks for this! i think this would help me generate a large single-line text file. :D
Left by trinna tan on Jul 14, 2014 7:41 PM

# re: Write, Read and Update Oracle CLOBs with PL/SQL
Requesting Gravatar...
I have a byte[] in my C# application that i want to store in a BLOB column.

How would i store this? Would i have to cast to RAW?

Any advice?
Left by rupert on May 28, 2016 8:44 AM

Your comment:
 (will show your gravatar)


Copyright © robertphyatt | Powered by: GeeksWithBlogs.net