Using CLOB gives ORA-06502: PL/SQL: numeric or value error

Question

Code below throws me an error: "ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 24".

DECLARE 
v_clob CLOB;
CURSOR cur_a
IS
SELECT
device_num,
Account_number,
CustomerName,
Prem_address ,
      contact_phone ,
      W_status ,
      A_status ,
      "12-1 AM" Twelve21,
      "1-2 AM" One22,
      "2-3 AM" Two23,
      "3-4 AM" Three24,
      CIn4Hrs
    FROM account a
    WHERE 1 =1 ;
BEGIN
  DBMS_LOB.CreateTemporary( v_clob, true );
  FOR rec IN cur_a
  LOOP
    v_clob := v_clob ||rec.device_num|| ',' ||rec.Account_number|| ',' ||rec.contact_phone || ',' ||rec.W_status|| ',' ||rec.A_status|| ',' ||rec.Twelve21|| ',' ||rec.One22|| ',' ||rec.Two23|| ',' ||rec.Three24|| ',' ||rec.CIn4Hrs|| ',' ||SUBSTR(rec.CustomerName,1,15)|| ',' ||SUBSTR(rec.Prem_address,1,15) || UTL_TCP.crlf;
  END LOOP;
END; 

Answer 1

You cannot use the string concatenation operator || to append to a clob. VARCHAR2 (the default type of your record fields) is max 32K. The clob's size is exceeding that. You need to wrap each element with the to_clob function, example:

v_clob || to_clob(rec.device_num) || ',' || to_clob(rec.Account_number)

etc.