LOBs the wrong and the wright way

I've been troubleshooting an ORA-06502: PL/SQL: numeric or value error although there is an official explanation of what causes this problem it is all to often not obvious where to look to resolve it, no line numbers or anything.

Although clobs can in theory store gigs and gigs of data if you dont handle them correctly Oracle will throw up an error long before you even get to 1 gig of data. Secondly if you do not do things correctly then Oracle will re-index a clob every time you append data to it, this uses up unnessary processing power on the database server.

So if you are writing clobs make sure you

1. CREATE TEMP CLOB
2. OPEN CLOB
3. WRITE/APPEND CLOB
4. CLOSE CLOB
5. FREE CLOB

The code might look a bit like this:

DECLARE

V_CLOB CLOB;
V_IN VARCHAR2(10) := 'TEXT IN';
V_IN_LEN NUMBER;

BEGIN

--Creates clob or blob in temp tablespace
dbms_lob.createtemporary(V_CLOB, TRUE);

--opens the clob in read/write mode
dbms_lob.open(V_CLOB, dbms_lob.lob_readwrite);

--if you have lots of data to append you can use a loop here

--get lenth off data to add to clob
V_IN_LEN := LENGTH(V_IN);

--use writeappend to add text to the clob
dbms_lob.writeappend(V_CLOB, V_IN_LEN, V_IN);

--if you were using a loop you would probably end it here

--close clob
dbms_lob.close(V_CLOB);

--although it's closed you can still access the clob here

dbms_output.put_line(V_CLOB);

--when you dont need the clob data anymore ditch it!
DBMS_LOB.FREETEMPORARY(V_CLOB);

END;