0

Im using a simple while loop to retrive comments from one table, split the comments to 75 chars each row and insert it in a new table. But some comments are encountering exception because the comments have single quotes ('). Any idea how to rectify without any data loss?

Counter := 1;

while comments is not null LOOP
  op :=substr(comments,1,75);

  begin
    insert into Comments_Splitted_TXT (id_comment,comm_no,COUNT_Comment,Comments)
    values (COMMENTS_SPLITTED_SEQUENCE.currval,Comments_Splitted_TXT_SEQUENCE.NEXTVAL,Counter,op);    
  exception 
    when others then dbms_output.put_line(COMMENTS_SPLITTED_SEQUENCE.currval);
  end;

  comments:= substr(comments,76);

  Counter :=Counter+1;

end loop;

Error message:

  1. 00000 - "value too large for column %s (actual: %s, maximum: %s)" *Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes
Annie Jeba
  • 373
  • 1
  • 3
  • 15
  • 1
    It shouldn't matter whether the comments contain quotes or not. The code looks fine. What is the exact error you are getting? When encountering an error, you should stop and not simply output the sequence number and continue. – Thorsten Kettner Oct 12 '16 at 04:42
  • It should still work. So again: What exact exception do you get? Please remove begin/exception when.../end around the insert, so you actually see it. – Thorsten Kettner Oct 12 '16 at 04:56
  • A cause could be multibyte characters (http://stackoverflow.com/questions/39405026/ora-12899-value-too-large-for-column-despite-of-same-length), but that can't be, because 5000 Bytes will easily hold 75 chars of course. What values for %s do you get in the message? Or does it really show %s? – Thorsten Kettner Oct 12 '16 at 05:34
  • No, I mean the error should read something like "value too large for column COMMENTS (actual: 5001, maximum: 5000)". I'd like to know what lengths Oracle reports. – Thorsten Kettner Oct 12 '16 at 05:45
  • ORA-12899: value too large for column "EHA01"."COMMENTS_SPLITTED_TXT"."COMMENTS" (actual: 77, maximum: 75) ORA-06512: at "EHA01.PKG_SPLIT_COMMENTS", line 64 ORA-06512: at line 1 – Annie Jeba Oct 12 '16 at 06:09
  • 2
    So the column is defined with a length of *75*, not 5000. And so it *can* be a multibyte issue. Is the column defined as 75 CHARS or 75 BYTES? See the SO link I posted above. Maybe it helps. – Thorsten Kettner Oct 12 '16 at 06:15
  • You are completly right. I haven't noticed that i have set the column datatype in table as Varchar2(75). The issue is resolved now. Thank you so much. – Annie Jeba Oct 12 '16 at 06:25

0 Answers0