Ran into an PL/SQL error when dealing with a clob variable. The following PL/SQL will cause an error as c_info is more than 32k. This should not be a problem as c_info is a clob. However, because the concatenate (||) is in there, the program is treating it as a varchar.
DECLARE
c_info CLOB;
BEGIN
FOR i IN 1 .. 32000
LOOP
c_info := c_info ||to_char(i);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
V_ATTACH_ERROR_CHAR_COUNT NUMBER;
BEGIN
V_ATTACH_ERROR_CHAR_COUNT := DBMS_LOB.getlength (c_info);
DBMS_OUTPUT.PUT_LINE (
‘Variable Length:’
|| V_ATTACH_ERROR_CHAR_COUNT);
END;
END;
The fix for this is to change this
c_info := c_info || to_char(i);
To this:
c_info := c_info || to_clob(to_char(i));
If there are additional variables needed in the concatenate string, then each section needs to be within a to_clob to keep everything as a clob.
Special thanks to https://stackoverflow.com/questions/49906725/using-clob-gives-ora-06502-pl-sql-numeric-or-value-error/49906909#49906909 for the solution!