1 2 Previous Next 19 Replies Latest reply: Apr 4, 2013 12:20 AM by 986379 Go to original post RSS
      • 15. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
        986379
        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
          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.
          • 18. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
            986379
            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
              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