1 2 Previous Next 19 Replies Latest reply: Apr 4, 2013 12:20 AM by 986379 RSS

    ORA-06502: PL/SQL: numeric or value error: character string buffer too smal

    986379
      Hi experts,


      please check it my query

      DECLARE
      f utl_file.file_type;
      s NVARCHAR2(32000);
      f1 VARCHAR2(10);
      f2 CHAR(1);
      F3 VARCHAR2(100);
      F4 VARCHAR2(100);
      F5 VARCHAR2(100);
      F6 DATE;
      F7 DATE;
      F8 DATE;
      F9 DATE;
      F10 DATE;
      f11 char(1);
      BEGIN
      DBMS_OUTPUT.ENABLE(100000);
      f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');

      LOOP
      BEGIN
      UTL_FILE.GET_LINE(f, s);

      f1 := REGEXP_SUBSTR (s, '[^|]+', 1, 1);
      f2 := REGEXP_SUBSTR (s, '[^|]+', 1, 2);
      F3 := REGEXP_SUBSTR (S, '[^|]+', 1, 3);
      F4 := REGEXP_SUBSTR (S, '[^|]+', 1, 4);
      F5 := REGEXP_SUBSTR (S, '[^|]+', 1, 5);
      F6 := REGEXP_SUBSTR (S, '[^|]+', 1, 6);
      F7 := REGEXP_SUBSTR (S, '[^|]+', 1, 7);
      F8 := REGEXP_SUBSTR (S, '[^|]+', 1, 8);
      F9 := REGEXP_SUBSTR (S, '[^|]+', 1, 9);
      F10 := REGEXP_SUBSTR (S, '[^|]+', 1, 10);
      f11 := REGEXP_SUBSTR (s, '[^|]+', 1,11);

      insert into OPTUM_ICD10CM_BASE
      (CODE,
                                                             STATUS,
                                                             SHORT_DESCRIPTION,
                                                             LONG_DESCRIPTION,
                                                             FULL_DESCRIPTION,
                                                             CODE_EFFECTIVE_DATE,
                                                             CHANGE_EFFECTIVE_DATE,
                                                             TERMINATION_DATE,
                                                             RELEASE_DATE,
      CREATION_DATE,
      VALIDITY)
      VALUES(F1,
      F2,
      F3,
      F4,
      F5,
      F6,
      F7,
      F8,
      F9,
      F10,
      f11);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      EXIT;
      END;
      END LOOP;

      UTL_FILE.FCLOSE(F);
      END;
      ------------

      and text format like this below


      A50.0||Short|Long|Full|01-01-2009|01-2009||01-01-2013|09-18-2012|C|
      A50.1||Short|Long|Full|01-01-2009|01-01-2009||001-2013|09-18-2012|C|
      A50.2||Short|Long|Full|01-01-2009|01-01-2009|67|01-01-2013|09-18-2012|C|
      A50.3||Short|Long|Full|011-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A50.4||Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|5|
      A50.5|R|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A50.6||Short|Long||01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A50.7||Short||Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      2345||Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.2|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.3|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.4|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.5|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.6|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.7|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.8|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A60.9|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A70.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A70.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A70.2|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A70.3|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A70.4|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      B222|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.2|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.3|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.4|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.5|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.6|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.7|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.8|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A4.9|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.2|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.3|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      D642|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.5|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.6|D|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.7|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A001|C|Short Updated|Long Updated|Full Updated|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A009|C|Short Updated|Long Updated|Full Updated|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.10|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A0109|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.2|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.3|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.4|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.5|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.6|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.7|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A30|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A316|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A317|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|


      but i getting a error like ORA-06502: PL/SQL: numeric or value error: character string buffer too small

      please help me
        • 1. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
          jeneesh
          Multiple issues:

          1. You are defining the size of the variable f2 as 1 ( f2 CHAR(1); ). Then assigning the value "Short" to it (f2 := REGEXP_SUBSTR (s, '[^|]+', 1, 2); )

          2. Variables f6 to f10 are defined as date. Then you are assigning string values to it

          3. Why did you define the variable S as NVARCHAR2 ?

          4. Why dont you use external table instead of UTL_FILE?
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
            986379
            hI,


            I am changed query but same error

            DECLARE
            f utl_file.file_type;
            s VARCHAR2(32000);
            f1 VARCHAR2(100);
            f2 CHAR(1);
            F3 VARCHAR2(100);
            F4 VARCHAR2(100);
            F5 VARCHAR2(100);
            F6 DATE;
            F7 DATE;
            F8 DATE;
            F9 DATE;
            F10 DATE;
            f11 char(1);
            BEGIN
            DBMS_OUTPUT.ENABLE(100000);
            f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');

            LOOP
            BEGIN
            UTL_FILE.GET_LINE(f, s);

            f1 := REGEXP_SUBSTR (s, '[^|]+', 1, 1);
            f2 := REGEXP_SUBSTR (s, '[^|]+', 1, 2);
            F3 := REGEXP_SUBSTR (S, '[^|]+', 1, 3);
            F4 := REGEXP_SUBSTR (S, '[^|]+', 1, 4);
            F5 := REGEXP_SUBSTR (S, '[^|]+', 1, 5);
            F6 := REGEXP_SUBSTR (S, '[^|]+', 1, 6);
            F7 := REGEXP_SUBSTR (S, '[^|]+', 1, 7);
            F8 := REGEXP_SUBSTR (S, '[^|]+', 1, 8);
            F9 := REGEXP_SUBSTR (S, '[^|]+', 1, 9);
            F10 := REGEXP_SUBSTR (S, '[^|]+', 1, 10);
            f11 := REGEXP_SUBSTR (s, '[^|]+', 1,11);

            insert into OPTUM_ICD10CM_BASE
            (CODE,
                                                                   STATUS,
                                                                   SHORT_DESCRIPTION,
                                                                   LONG_DESCRIPTION,
                                                                   FULL_DESCRIPTION,
                                                                   CODE_EFFECTIVE_DATE,
                                                                   CHANGE_EFFECTIVE_DATE,
                                                                   TERMINATION_DATE,
                                                                   RELEASE_DATE,
            CREATION_DATE,
            VALIDITY)
            VALUES(F1,
            F2,
            F3,
            F4,
            F5,
            F6,
            F7,
            F8,
            F9,
            F10,
            f11);
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
            EXIT;
            END;
            END LOOP;

            UTL_FILE.FCLOSE(F);
            END;

            please check it and help me
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
              Ramin Hashimzadeh
              you have || whithout characters.
              use to_date to convert string to date
              • 4. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                Chanchal Wankhade
                Hi,

                Dont assign lengh more than 4000 to varchar2.

                If you are trying to upload data from excel,txt file then why dont you use sql loader or external table.
                • 5. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                  Ashu_Neo
                  Something should be there to get the subsequent positions by regular expression.
                  As "||", this blank is creating problem with you. ( Already raised by others).

                  As Jeenesh told , better used it through external tables.

                  Else to get all inserted in right column, you have to do something some temporary fixes for the time being.
                  f2 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 2); 
                  
                  -- It will insert a space in place of blank data, but regular expression for all position will work. Use replace for all fn, you are using.
                  By the way it'll be a temporary fix for giving data set.
                  OR
                  Increase size of f2 and f11 to CHAR(2); maintain same as in respective target table columns. And use REPLACE(s,'||','|NA|'). "NA" stands for not available data for column. Again it might be a temporary fix.

                  Check and let me know.

                  Edited by: Ashu_Neo on Apr 1, 2013 12:06 PM
                  • 6. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                    Nimish Garg
                    This may be helpful
                    http://nimishgarg.blogspot.in/2013/03/ora-06502-plsql-numeric-or-value.html
                    • 7. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                      986379
                      hi
                      as of ur idea i am modified the script but again error not valid month

                      DECLARE
                      f utl_file.file_type;
                      s VARCHAR2(32000);
                      f1 VARCHAR2(100);
                      f2 CHAR(1);
                      F3 VARCHAR2(100);
                      F4 VARCHAR2(100);
                      F5 VARCHAR2(100);
                      F6 DATE;
                      F7 DATE;
                      F8 DATE;
                      F9 DATE;
                      F10 DATE;
                      f11 char(1);
                      BEGIN
                      DBMS_OUTPUT.ENABLE(100000);
                      f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');

                      LOOP
                      BEGIN
                      UTL_FILE.GET_LINE(f, s);

                      f1 := REGEXP_SUBSTR (s, '[^|]+', 1, 1);
                      f2 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 2);
                      F3 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 3);
                      F4 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 4);
                      F5 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 5);
                      F6 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 6);
                      F7 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 7);
                      F8 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 8);
                      F9 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 9);
                      F10 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 10);
                      f11 := REGEXP_SUBSTR (REPLACE(s,'||','| |') , '[^|]+', 1, 11);

                      insert into OPTUM_ICD10CM_BASE
                      (CODE,
                                                                             STATUS,
                                                                             SHORT_DESCRIPTION,
                                                                             LONG_DESCRIPTION,
                                                                             FULL_DESCRIPTION,
                                                                             CODE_EFFECTIVE_DATE,
                                                                             CHANGE_EFFECTIVE_DATE,
                                                                             TERMINATION_DATE,
                                                                             RELEASE_DATE,
                      CREATION_DATE,
                      VALIDITY)
                      VALUES(F1,
                      F2,
                      F3,
                      F4,
                      F5,
                      F6,
                      F7,
                      F8,
                      F9,
                      F10,
                      f11);
                      EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      EXIT;
                      END;
                      END LOOP;

                      UTL_FILE.FCLOSE(F);
                      END;


                      please check it
                      • 8. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                        Ashu_Neo
                        This is going to get messy with temporary fixes!

                        You have to be clear enough what to use. a space or NA for character column and a default date for date columns black data replacement.

                        For F6 to F10 are date columns. Can replace to a default date.
                        Like:-
                        select REGEXP_SUBSTR (replace('A50.0||Short|Long|Full|01-01-2009|01-2009||01-01-2013|09-18-2012|C|','||','|'||to_date('01-01-1000','dd-mm-yyyy')||'|'), '[^|]+', 1, 8) dd from dual
                        / 
                        • 9. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                          986379
                          HI

                          again got a same error
                          i wil sent you new scriprt for same reqr please chek it but getting a error like PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got DATE

                          DECLARE
                          v_line VARCHAR2(2000);
                          v_file utl_file.file_type;
                          v_dir VARCHAR2(250);
                          v_filename VARCHAR2(50);
                          BEGIN
                          v_dir :='MID5010_DOC1TP';
                          v_filename := 'OPT_CM_BASE.txt';
                          v_file := utl_file.fopen(v_dir, v_filename, 'r');
                          LOOP
                          BEGIN
                          utl_file.get_line(v_file, v_line);
                          EXCEPTION
                          WHEN no_data_found THEN
                          EXIT;
                          END ;
                          v_line := REPLACE(v_line,'|','|~');
                          INSERT
                          INTO optum_icd10cm_base VALUES
                          (
                          REPLACE(TRANSLATE(regexp_substr(v_line,'[^|]+',1,1),'a~','a'),'.'),
                          TRANSLATE(regexp_substr(v_line,'[^|]+',1,2),'a~','a'),
                          TRANSLATE(regexp_substr(v_line,'[^|]+',1,3),'a~','a'),
                          TRANSLATE(regexp_substr(v_line,'[^|]+',1,4),'a~','a'),
                          TRANSLATE(regexp_substr(v_line,'[^|]+',1,5),'a~','a'),
                          CASE
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,6)) < 10
                          THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line,'[^|]+',1,6),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                          ELSE to_date(TRANSLATE(regexp_substr(v_line,'[^|]+',1,6),'a~','a'),'mm-dd-yyyy')
                          END,
                          CASE
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,7)) < 10
                          THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr('A60.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|' ,'[^|]+',1,7),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                          ELSE to_date(TRANSLATE(regexp_substr(v_line,'[^|]+',1,7),'a~','a'),'mm-dd-yyyy')
                          END,
                          CASE
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,8)) < 10
                          THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr('A60.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|' ,'[^|]+',1,8),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                          ELSE to_date(TRANSLATE(regexp_substr(v_line,'[^|]+',1,8),'a~','a'),'mm-dd-yyyy')
                          END,
                          CASE
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,9)) < 10
                          THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr('A60.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|' ,'[^|]+',1,9),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                          ELSE to_date(TRANSLATE(regexp_substr(v_line,'[^|]+',1,9),'a~','a'),'mm-dd-yyyy')
                          END,
                          CASE
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,10)) < 10
                          THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr('A60.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|' ,'[^|]+',1,10),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                          ELSE to_date(TRANSLATE(regexp_substr(v_line,'[^|]+',1,10),'a~','a'),'mm-dd-yyyy')
                          END,
                          TRANSLATE(regexp_substr(v_line,'[^|]+',1,11),'a~','a')
                          );
                          -----commit;
                          END LOOP;
                          utl_file.fclose(v_file);
                          END;

                          thanks for more help
                          • 10. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                            Ashu_Neo
                            Last new script is beyond my readability!

                            Just modified your previous block as per different data formats for some columns in your data file.
                            DECLARE
                            f utl_file.file_type;
                            s NVARCHAR2(32000);
                            f1 VARCHAR2(10);
                            f2 CHAR(1);
                            F3 VARCHAR2(100); 
                            F4 VARCHAR2(100); 
                            F5 VARCHAR2(100); 
                            F6 DATE;
                            F7 DATE; 
                            F8 DATE; 
                            F9 DATE; 
                            F10 DATE;
                            f11 char(1); 
                            --cnt integer :=0;  -- Added for testing
                            
                            BEGIN
                            DBMS_OUTPUT.ENABLE(100000);
                            f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');
                            
                            LOOP
                            BEGIN 
                            UTL_FILE.GET_LINE(f, s);
                            --cnt := cnt + 1;
                            s:= REPLACE(s,'||','| |');
                            f1 := REGEXP_SUBSTR (s, '[^|]+', 1, 1);
                            f2 := REGEXP_SUBSTR (s, '[^|]+', 1, 2);
                            F3 := REGEXP_SUBSTR (S, '[^|]+', 1, 3);
                            F4 := REGEXP_SUBSTR (S, '[^|]+', 1, 4);
                            F5 := REGEXP_SUBSTR (S, '[^|]+', 1, 5);
                            F6 := to_date(REGEXP_SUBSTR (S, '[^|]+', 1, 6) ,'dd-mm-yyyy');
                            F7 := CASE 
                                  WHEN LENGTH(REGEXP_SUBSTR (S, '[^|]+', 1, 7)) < 10
                                   THEN to_date(REGEXP_SUBSTR (S, '[^|]+', 1, 7) ,'mm-yyyy')
                                  ELSE
                                   to_date(REGEXP_SUBSTR (S, '[^|]+', 1, 7) ,'dd-mm-yyyy')
                                   END;
                            --F8 := REGEXP_SUBSTR (S, '[^|]+', 1, 8);
                            F8 := to_date(REGEXP_SUBSTR(replace(s,'| |','|'||'01-01-1000'||'|'), '[^|]+', 1, 8),'dd-mm-yyyy');
                            F9 := to_date(REGEXP_SUBSTR (S,'[^|]+', 1, 9) ,'dd-mm-yyyy');
                            F10 := to_date(REGEXP_SUBSTR (S, '[^|]+', 1, 10) ,'mm-dd-yyyy');
                            f11 := REGEXP_SUBSTR (s, '[^|]+', 1,11);
                            /*
                            dbms_output.put_line(' f1:'||f1);
                            dbms_output.put_line(' f2:'||f2);
                            dbms_output.put_line(' f3:'||f3);
                            dbms_output.put_line(' f4:'||f4);
                            dbms_output.put_line(' f5:'||f5);
                            dbms_output.put_line(' f6:'||f6);
                            dbms_output.put_line(' f7:'||f7);
                            dbms_output.put_line(' f8:'||f8);
                            dbms_output.put_line(' f9:'||f9);
                            dbms_output.put_line(' f10:'||f10);
                            dbms_output.put_line(' f11:'||f11);
                            */
                            
                            insert into optum_icd10cm_base
                            (code,
                            status,
                            short_description,
                            long_description,
                            full_description,
                            code_effective_date,
                            change_effective_date,
                            termination_date,
                            release_date,
                            creation_date,
                            validity)
                            VALUES(f1,
                            replace(f2,' ',''),
                            f3,
                            f4,
                            f5,
                            f6,
                            f7,
                            replace(f8,to_date('01-01-1000','dd-mm-yyyy'), null),
                            f9,
                            f10,
                            f11);
                            
                            EXCEPTION
                            WHEN no_data_found THEN
                            EXIT;
                            /*
                            WHEN OTHERS THEN
                            dbms_output.put_line('f1 and f2 -'||f1||' AND '||f2||' Line No '||cnt||' Error '||sqlerrm);
                            */
                            END;
                            END LOOP;
                            UTL_FILE.FCLOSE(F);
                            END;
                            /
                            Even your existing data files. i found 3 bad records, which you can't handle by assuming some format. If you can change for 3 records manually, then it will be fine.
                            But rest all 54 records are getting inserted.
                            A50.1||Short|Long|Full|01-01-2009|01-01-2009||001-2013|09-18-2012|C| -- error 001 is not a part of date format
                            A50.2||Short|Long|Full|01-01-2009|01-01-2009|67|01-01-2013|09-18-2012|C|  -- error 67 is not a date format
                            A50.3||Short|Long|Full|011-2009|01-01-2009||01-01-2013|09-18-2012|C| -- error 011 is not a date format
                            Thanks!
                            • 11. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                              986379
                              HI


                              I have small doubt in this script possible to 3 bad records inserted into error table like corrected records load into one table bad records load into another table .
                              before you told like that 3 bad records that records load into error table same script
                              please help me

                              DECLARE
                              F UTL_FILE.FILE_TYPE;
                              S NVARCHAR2(32000);
                              F1 VARCHAR2(10);
                              F2 CHAR(1);
                              F3 VARCHAR2(100);
                              F4 VARCHAR2(100);
                              F5 VARCHAR2(100);
                              F6 DATE;
                              F7 DATE;
                              F8 DATE;
                              F9 DATE;
                              F10 DATE;
                              F11 CHAR(1);
                              BEGIN
                              DBMS_OUTPUT.enable(100000);
                              F := UTL_FILE.FOPEN('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');
                              LOOP
                              BEGIN
                              UTL_FILE.GET_LINE(F, S);
                              S := REPLACE(S,'||','| |');
                              F1 := REGEXP_SUBSTR (S, '[^|]+', 1, 1);
                              F2 := REGEXP_SUBSTR (S, '[^|]+', 1, 2);
                              F3 := REGEXP_SUBSTR (S, '[^|]+', 1, 3);
                              F4 := REGEXP_SUBSTR (S, '[^|]+', 1, 4);
                              F5 := REGEXP_SUBSTR (S, '[^|]+', 1, 5);
                              F6 := TO_DATE(REGEXP_SUBSTR (S, '[^|]+', 1, 6) ,'dd-mm-yyyy');
                              F7 :=
                              CASE
                              WHEN LENGTH(REGEXP_SUBSTR (S, '[^|]+', 1, 7)) < 10 THEN
                              TO_DATE(REGEXP_SUBSTR (S, '[^|]+', 1, 7) ,'mm-yyyy')
                              ELSE
                              TO_DATE(REGEXP_SUBSTR (S, '[^|]+', 1, 7) ,'dd-mm-yyyy')
                              END;
                              F8 := TO_DATE(REGEXP_SUBSTR(REPLACE(S,'| |','|'||'01-01-1000'||'|'), '[^|]+', 1, 8),'dd-mm-yyyy');
                              F9 := TO_DATE(REGEXP_SUBSTR (S,'[^|]+', 1, 9) ,'mm-dd-yyyy');
                              F10 := TO_DATE(REGEXP_SUBSTR (S, '[^|]+', 1, 10) ,'mm-dd-yyyy');
                              F11 := REGEXP_SUBSTR (S, '[^|]+', 1,11);
                              INSERT
                              INTO OPTUM_ICD10CM_BASE
                              (
                              CODE,
                              STATUS,
                              SHORT_DESCRIPTION,
                              LONG_DESCRIPTION,
                              FULL_DESCRIPTION,
                              CODE_EFFECTIVE_DATE,
                              CHANGE_EFFECTIVE_DATE,
                              TERMINATION_DATE,
                              RELEASE_DATE,
                              CREATION_DATE,
                              VALIDITY
                              )
                              VALUES
                              (
                              F1,
                              REPLACE(F2,' ',''),
                              F3,
                              F4,
                              F5,
                              F6,
                              F7,
                              REPLACE(F8,TO_DATE('01-01-1000','dd-mm-yyyy'), NULL),
                              F9,
                              F10,
                              F11
                              );
                              EXCEPTION
                              when NO_DATA_FOUND then
                              EXIT;
                              END;
                              END LOOP;
                              UTL_FILE.FCLOSE(F);
                              EXCEPTION
                              WHEN UTL_FILE.invalid_path THEN
                              raise_application_error (-20000, 'ERROR: Invalid PATH FOR file.');
                              WHEN OTHERS THEN
                              DBMS_OUTPUT.PUT_LINE ( 'Error Encountered.....' || SQLCODE || ' - ' || SQLERRM );
                              END;
                              • 12. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                jeneesh
                                I will repeat - Learn external tables and use it. You will get bad records in seperate file, if you use external table.

                                Or if you want to stick on utl_file - check DML Error logging
                                • 13. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                  986379
                                  hi

                                  i didnt get idea so please fix the code in my script


                                  Thanks
                                  • 14. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                    Ashu_Neo
                                    You can change it in exception block like this.
                                    -- create a log table
                                    create table log_t (data_col varchar2(4000))
                                    /
                                    
                                    -- and use it below in exception in side the loop
                                    . . . . . .
                                    . . . . . . 
                                    EXCEPTION
                                    WHEN no_data_found THEN
                                    EXIT;
                                    
                                    WHEN OTHERS THEN
                                         insert into log_t values(s);
                                    
                                    END;
                                    END LOOP;
                                    For bad, discard log files, you have to use SQL Loader or external table files.

                                    Thanks!
                                    1 2 Previous Next