This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 3, 2013 10:20 PM by 986379 Go to original post RSS
  • 15. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
    986379 Newbie
    Currently Being Moderated
    HI

    as per this script only one record inserted(duplicate records) please check it my script but as per text file 3 records load into error table and reamining 54 records load one table

    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;
    EXCEPTION
    when OTHERS then
    insert into err_opt values(s);
    END;
  • 16. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
    Ashu_Neo Pro
    Currently Being Moderated
    See now, you really need to think carefully, how the code is working step by step. Then you could help yourself.

    I told you to use a when others part in exception block inside LOOP and you changed that in outer main block !!! Why ?

    Just change and recheck.
  • 17. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
    986379 Newbie
    Currently Being Moderated
    hi,


    working good

    Thanks
  • 18. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
    986379 Newbie
    Currently Being Moderated
    hI,


    iam prepared script but not working please check it my script and file also

    script
    ----------
    DECLARE
    F Utl_File.File_Type;
    S NVARCHAR2(32000);
    F1 VARCHAR2(10);
    F2 VARCHAR2(100);
    F3 VARCHAR2(100);
    F4 VARCHAR2(100);
    F5 VARCHAR2(100);
    F6 VARCHAR2(100);
    F7 VARCHAR2(100);
    F8 VARCHAR2(100);
    F9 DATE;
    F10 DATE;
    F11 CHAR(1);
    BEGIN
    Dbms_Output.Enable(100000);
    F := Utl_File.Fopen('MID5010_DOC1TP', 'OPTUM_ICD10CM_CHANGE.TXT', 'R');
    LOOP
    BEGIN
    Utl_File.Get_Line(F, S);
    S := REPLACE(S,'||','| |');
    F1 :=REPLACE(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 := 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_change1
    (
    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,
    F8,
    F9,
    F10,
    F11
    );
    EXCEPTION
    WHEN No_Data_Found THEN
    EXIT;
    WHEN OTHERS THEN
    INSERT INTO Err_Opts VALUES
    (S
    );
    END;
    End Loop;
    END;
    --------------file
    Z93.1||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.2||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.3||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.4||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.5||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.50||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.51||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.52||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.59||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.6||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.8||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z93.9||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.0||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.1||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.2||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.3||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.4||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.5||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.6||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.7||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.8||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.81||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.82||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.83||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.84||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.89||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z94.9||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.0||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.1||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.2||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.3||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.4||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.5||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.8||N|N|N|N|N|N|01-01-2013|09-18-2012|N|
    Z95.81||N|N|N|N|N|N|01-01-2013|09-18-2012|N|

    please help me
  • 19. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
    986379 Newbie
    Currently Being Moderated
    Hi ,

    i have file like ||,/,|

    00BY0||EXCISION LUMBAR SPINAL CORD OPEN|EXCISION / LUMBAR SPINAL CORD / OPEN|Excision / Lumbar Spinal Cord / Open|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|

    so this time how to write script.

    Please check it my script and this script executed but all reocrds load into error table

    DECLARE
    F UTL_FILE.FILE_TYPE;
    S NVARCHAR2(32000);
    F1 VARCHAR2(100);
    F2 VARCHAR2(100);
    F3 VARCHAR2(100);
    F4 VARCHAR2(100);
    F5 VARCHAR2(100);
    F6 DATE;
    F7 DATE;
    F8 DATE;
    F9 DATE;
    F10 DATE;
    F11 CHAR(1);
    V_FF_STG_SID NUMBER(10) :=1001;
    V_PROCESSED VARCHAR2(1):='Y';
    BEGIN
    DBMS_OUTPUT.enable(100000);
    F := UTL_FILE.FOPEN('MID5010_DOC1TP', 'OPTUM_ICD10PCS_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 := TO_DATE(REGEXP_SUBSTR (S, '[^|]+', 1, 7) ,'dd-mm-yyyy');
    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 IST_1002_02B_PCS_BASE
    (
    ICD10_CODE,
    STATUS,
    SHORT_DESCRIPTION,
    LONG_DESCRIPTION,
    FULL_DESCRIPTION,
    CODE_EFFECTIVE_DATE,
    CHANGE_EFFECTIVE_DATE,
    TERMINATION_DATE,
    RELEASE_DATE,
    FILE_DATE,
    VALIDITY,
    FF_STG_SID,
    PROCESSED
    )
    VALUES
    (
    F1,
    F2,
    F3,
    F4,
    F5,
    F6,
    F7,
    REPLACE(F8,TO_DATE('01-01-1000','dd-mm-yyyy'), NULL),
    F9,
    F10,
    F11,
    V_FF_STG_SID,
    V_PROCESSED
    );
    EXCEPTION
    WHEN no_data_found THEN
    EXIT;
    WHEN OTHERS THEN
    INSERT INTO err_pcs_base VALUES
    (S
    );
    END;
    END LOOP;
    END;
1 2 Previous Next

Legend

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