Write, Read and Update Oracle CLOBs with PL/SQL
- by robertphyatt
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!
