This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 3, 2013 10:20 PM by 986379 RSS

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

986379 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points