Using CLOB’s in PL/SQL (10g)

We recently had a customer complain of getting the following error in the custom PL/SQL code:

ORA-06502: PL/SQL: numeric or value error

This is a generic error, so we had to do a little digging.  What we found is, the program is generating an email message, and has a CLOB defined for the message body.  They were appending to the CLOB using the following syntax:

l_clob := l_clob || ‘some text or variable’;

This worked fine for any variable or text that was a VARCHAR2, but when it tried to add any numbers after about 2000 bytes, it would throw the above error.  What we determined is that Oracle performs an implicit TO_CHAR function on the variable when it appends it, and treats the CLOB variable as a VARCHAR2 instead of a CLOB.  Here is a simple procedure that generates the error:

CREATE OR REPLACE PROCEDURE clob_test_error
AS
   l_clob     CLOB;
BEGIN
  FOR i IN 1 .. 5000
   LOOP
      l_clob   := l_clob || i;
   END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
And you can see it generates the error:

shersh@XE> set serveroutput on timing on
shersh@XE> exec clob_test_error;
Error: ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56

It’s an easy fix, by adding an explicit TO_CHAR conversion of the variable as below, the procedure completes successfully:

CREATE OR REPLACE PROCEDURE clob_test_using_to_char
AS
   l_clob     CLOB;
  BEGIN
  FOR i IN 1 .. 5000
   LOOP
      l_clob   := l_clob || TO_CHAR (i);
   END LOOP;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
  END;

Running this one completes without error:

shersh@XE> exec clob_test_using_to_char;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.84

So we’re all good right?  Well then I thought maybe there is a more efficient way to do this using DBMS_LOB.  Lo and behold, DBMS_LOB seems faster.  I did not do any extended stats gathering, but I am guessing not doing the TO_CHAR and using the DBMS_LOB function is more efficient.  Here is the code and results:

CREATE OR REPLACE PROCEDURE clob_test_using_dbms_lob
AS
   l_clob     CLOB;
BEGIN
   DBMS_LOB.createtemporary (lob_loc => l_clob, cache => TRUE);
   FOR i IN 1 .. 5000
   LOOP
      DBMS_LOB.write (lob_loc => l_clob,
                      amount => LENGTH (i),
                      offset => 1,
                      buffer => i
      );
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;

And the results:

shersh@XE> exec clob_test_using_dbms_lob;
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

So when using CLOB’s in PL/SQL, I would recommend using the built in DBMS_LOB functions instead of treating CLOB’s as a VARCHAR.