how to overcome CLOB run out of space - ORA-06502: PL/SQL: numeric or value error

0
votes

I have a scenario where I am appending string to a clob. The issue I have is that once the CLOB reaches a certain size if I try to append more to the clob I get a ORA-06502: PL/SQL: numeric or value error. I assume that it is running out of size but I do not know how to overcome the problem (i.e. specify how to make the clob bigger).

Could someone please help me.

This is how i declare my CLOB...

LP_xml_result CLOB;

DBMS_LOB.CREATETEMPORARY(
      lob_loc => LP_xml_result
    , cache   => true
    , dur     => dbms_lob.call
);

DBMS_LOB.OPEN(
      lob_loc    => LP_xml_result
    , open_mode  => DBMS_LOB.LOB_READWRITE
);

And then I insert into it like so....

PROCEDURE lob_append(i_string IN VARCHAR2)
IS
BEGIN

    dbms_lob.append(LP_xml_result,LP_LineFeed || i_string);
END lob_append;

thanks

1
oracleplsqloracle9i
does it get bigger than 4GB? – pablomatico
Since Oracle 11 a LOB can be max (4 GB - 1) * DB_BLOCK_SIZE, i.e. 8TB to 128TB. 4GB was the limit till Oracle 10 – Wernfried Domscheit
I checked the Oracle Docs and see two possible issues. When you open a LOB then you also have to close it. However, it is not mandatory to open/close a LOB - try it without. Instead of creating a TEMPORARY LOB try LP_xml_result := EMPTY_CLOB() – Wernfried Domscheit

1 Answers

4
votes

When you do LP_LineFeed || i_string it makes an implicit cast to VARCHAR2, there you have the limit of 32K.

Do it like this:

PROCEDURE lob_append(i_string IN VARCHAR2)
IS
BEGIN

    dbms_lob.append(LP_xml_result,LP_LineFeed);
    dbms_lob.append(LP_xml_result,i_string);
END lob_append;

Regarding your problem with printing to HTP, some time ago I hat the same issue, here is my solution:

chunkSize   CONSTANT INTEGER := 5000;
pos NUMBER;

BEGIN
IF LENGTH(CLOB_VAL) > chunkSize THEN
    LOOP
        Htp.prn(SUBSTR(CLOB_VAL, pos, chunkSize));
        pos := pos + chunkSize;
        EXIT WHEN pos > LENGTH(CLOB_VAL);
    END LOOP;
ELSE
    Htp.prn(CLOB_VAL);
END IF;