-2

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;
Devadas Wagle
  • 37
  • 1
  • 1
  • 5
  • Please format so this is readable – Daniel Marcus Apr 18 '18 at 18:35
  • Also describe what attempts you have made to solve the error. – Patrick Apr 18 '18 at 18:38
  • Sorry I was not able to edit, some one did it for me. Thank you. Patrik, it is larger piece of code I have shortened to make it easy to ask here. I tried to cut short the number of records by using rownum < 400. Then it works fine. There 6000 records resulting in the query. – Devadas Wagle Apr 18 '18 at 18:46

1 Answers1

4

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.

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45