1 2 Previous Next 26 Replies Latest reply: Apr 1, 2013 4:45 AM by 986379 RSS

    01861. 00000 -  "literal does not match format string"

    986379
      hi,

      I have some problem like in date columns data like 09-2009,09-09-2009 how to read data from f=in txt file
      my script check it

      DECLARE
      V_LINE VARCHAR2(2000); -- Data line read from input file

      V_FILE UTL_FILE.FILE_TYPE;

      V_DIR VARCHAR2(250);

      V_FILENAME VARCHAR2(50);

      V_1ST_COMMA NUMBER;
      V_2ND_COMMA NUMBER;
      V_3RD_COMMA NUMBER;
      V_4TH_COMMA NUMBER;
      V_5TH_COMMA NUMBER;
      V_6TH_COMMA NUMBER;
      V_7TH_COMMA NUMBER;
      V_8TH_COMMA NUMBER;
      V_9TH_COMMA NUMBER;
      V_10TH_COMMA NUMBER;
      V_11TH_COMMA NUMBER;

      V_CODE VARCHAR(30);
      V_STATUS CHAR;
      V_SHORT_DESCRIPTION VARCHAR2(100);
      V_LONG_DESCRIPTION VARCHAR2(100);
                                    V_FULL_DESCRIPTION VARCHAR2(100);
                                    V_CODE_EFFECTIVE_DATE DATE;
                                    V_CHANGE_EFFECTIVE_DATE DATE;
                                    V_TERMINATION_DATE DATE;
                                    V_RELEASE_DATE DATE;
      V_CREATION_DATE DATE;
      V_VALIDITY CHAR;
      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_1ST_COMMA := INSTR(V_LINE,'|',1,1);

      V_2ND_COMMA := INSTR(V_LINE,'|',1,2);

      V_3RD_COMMA := INSTR(V_LINE,'|',1,3);

      V_4TH_COMMA := INSTR(V_LINE,'|',1,4);

      V_5TH_COMMA := INSTR(V_LINE,'|',1,5);

      V_6TH_COMMA := INSTR(V_LINE,'|',1,6);

      V_7TH_COMMA := INSTR(V_LINE,'|',1,7);

      V_8TH_COMMA := INSTR(V_LINE,'|',1,8);

      V_9TH_COMMA := INSTR(V_LINE,'|',1,9);

      V_10TH_COMMA := INSTR(V_LINE,'|',1,10);

      V_11TH_COMMA := INSTR(V_LINE,'|',1,11);


      V_CODE := SUBSTR(V_LINE, 1,V_1ST_COMMA-1) ;

      V_STATUS := SUBSTR(V_LINE, V_1ST_COMMA+1, V_2ND_COMMA-V_1ST_COMMA-1);

      V_SHORT_DESCRIPTION := SUBSTR(V_LINE, V_2ND_COMMA+1,V_3RD_COMMA-V_2ND_COMMA-1);

      V_LONG_DESCRIPTION := SUBSTR(V_LINE, V_3RD_COMMA+1,V_4TH_COMMA-V_3RD_COMMA-1);

      V_FULL_DESCRIPTION := SUBSTR(V_LINE, V_4TH_COMMA+1,V_5TH_COMMA-V_4TH_COMMA-1);

      V_CODE_EFFECTIVE_DATE := TO_DATE(SUBSTR(V_LINE, V_5TH_COMMA+1,V_6TH_COMMA-V_5TH_COMMA-1),'MM-DD-YYYY');

      V_CHANGE_EFFECTIVE_DATE := TO_DATE(SUBSTR(V_LINE, V_6TH_COMMA+1,V_7TH_COMMA-V_6TH_COMMA-1),'MM-DD-YYYY');

      V_TERMINATION_DATE := TO_DATE(SUBSTR(V_LINE, V_7TH_COMMA+1,V_8TH_COMMA-V_7TH_COMMA-1),'MM-DD-YYYY');

      V_RELEASE_DATE := TO_DATE(SUBSTR(V_LINE, V_8TH_COMMA+1,V_9TH_COMMA-V_8TH_COMMA-1),'MM-DD-YYYY');

      V_CREATION_DATE := TO_DATE(SUBSTR(V_LINE, V_9TH_COMMA+1,V_10TH_COMMA-V_9TH_COMMA-1),'MM-DD-YYYY');

      V_VALIDITY := SUBSTR(V_LINE, V_10TH_COMMA+1, V_11TH_COMMA-V_10TH_COMMA-1);


      ----DBMS_OUTPUT.PUT_LINE(V_EMPNO ||' '|| V_ENAME || ' ' || V_JOB || ' ' || V_MGR ||' ' || V_HIREDATE);


      INSERT INTO OPTUM_ICD10CM_BASE
      VALUES (V_CODE,
      V_STATUS,
      V_SHORT_DESCRIPTION,
      V_LONG_DESCRIPTION,
                                                             V_FULL_DESCRIPTION,
                                                             V_CODE_EFFECTIVE_DATE,
                                                             V_CHANGE_EFFECTIVE_DATE,
                                                             V_TERMINATION_DATE,
                                                             V_RELEASE_DATE,
      V_CREATION_DATE,
      V_VALIDITY);

      END LOOP;

      UTL_FILE.FCLOSE(V_FILE);

      /*EXCEPTION
      when UTL_FILE.INVALID_PATH then
      RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
      when UTL_FILE.INVALID_FILEHANDLE then
      RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
      when UTL_FILE.READ_ERROR then
      RAISE_APPLICATION_ERROR (-20055, 'Read Error');
      when UTL_FILE.INTERNAL_ERROR then
      RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
      when UTL_FILE.FILE_OPEN then
      RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
      when UTL_FILE.INVALID_MAXLINESIZE then
      RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
      when UTL_FILE.INVALID_FILENAME then
      RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
      WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'); */
      END;
        • 1. Re: 01861. 00000 -  "literal does not match format string"
          jeneesh
          Use EXTERNAL TABLE instead of UTL_FILE.


          Can you show us sample data?
          • 2. Re: 01861. 00000 -  "literal does not match format string"
            Ramin Hashimzadeh
            I have some problem like in date columns data like 09-2009,09-09-2009 how to read data from f=in txt file
            what do you want to do? I dont understand you task about :
            f=in txt file
            • 3. Re: 01861. 00000 -  "literal does not match format string"
              986379
              i want plsql script for read the data from file and insert into table using utl_files only
              • 4. Re: 01861. 00000 -  "literal does not match format string"
                jeneesh
                983376 wrote:
                i want plsql script for read the data from file and insert into table using utl_files only
                WHY ? Why UTL_FILE only? Your file name seems to be fixed..

                Post sample data form your file..
                • 5. Re: 01861. 00000 -  "literal does not match format string"
                  Ramin Hashimzadeh
                  show your sample data from your file. Here is the example for you :
                    DECLARE
                        f utl_file.file_type;
                        s nvarchar2(500);
                    begin
                        f := utl_file.fopen('mydir', 'fname', 'R');  
                        
                        LOOP
                          BEGIN        
                            UTL_FILE.GET_LINE(f, s);
                            
                            insert into mytable(mycolumn)
                            values(s);
                          EXCEPTION
                            WHEN NO_DATA_FOUND THEN
                              EXIT;
                          END;
                        END LOOP;
                        
                        utl_file.fclose(f);
                    end;  
                  ---
                  Ramin Hashimzadeh
                  • 6. Re: 01861. 00000 -  "literal does not match format string"
                    986379
                    hi

                    my file below like that

                    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|
                    • 7. Re: 01861. 00000 -  "literal does not match format string"
                      Ramin Hashimzadeh
                      DECLARE
                            f utl_file.file_type;
                            s nvarchar2(500);
                            f1 nvarchar2(500);
                            f2 nvarchar2(500);
                            f3 nvarchar2(500);    
                            ....        
                        begin
                            f := utl_file.fopen('mydir', 'fname', '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);
                                
                                insert into mytable(mycolumn1,mycolumn2,mycolumn3,...)
                                values(f1,f2,f3,...);
                              EXCEPTION
                                WHEN NO_DATA_FOUND THEN
                                  EXIT;
                              END;
                            END LOOP;
                            
                            utl_file.fclose(f);
                        END 
                      • 8. Re: 01861. 00000 -  "literal does not match format string"
                        986379
                        Hi

                        JUST now ur script used but i got error not enouh values like that because of table having lot of columns but pass one variable like buffer varcial
                        please check it my script as per before text format

                        DECLARE
                        f utl_file.file_type;
                        s nvarchar2(500);
                        begin
                        f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');

                        LOOP
                        BEGIN
                        UTL_FILE.GET_LINE(f, s);

                        insert into OPTUM_ICD10CM_BASE
                        values(s);
                        EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                        EXIT;
                        END;
                        END LOOP;

                        UTL_FILE.FCLOSE(F);
                        end;
                        • 9. Re: 01861. 00000 -  "literal does not match format string"
                          986379
                          Hi

                          JUST now ur script used but i got error not enouh values like that because of table having lot of columns but pass one variable like buffer varcial
                          please check it my script as per before text format

                          DECLARE
                          f utl_file.file_type;
                          s nvarchar2(500);
                          begin
                          f := utl_file.fopen('MID5010_DOC1TP', 'OPT_CM_BASE.txt', 'R');

                          LOOP
                          BEGIN
                          UTL_FILE.GET_LINE(f, s);

                          insert into OPTUM_ICD10CM_BASE
                          values(s);
                          EXCEPTION
                          WHEN NO_DATA_FOUND THEN
                          EXIT;
                          END;
                          END LOOP;

                          UTL_FILE.FCLOSE(F);
                          end;
                          • 10. Re: 01861. 00000 -  "literal does not match format string"
                            jeneesh
                            Typical scenario to use external table..

                            Just create an external table and do as
                            insert into target
                            select ..
                            from ext_table;
                            • 11. Re: 01861. 00000 -  "literal does not match format string"
                              986379
                              i am prepared n script as per ur script but i got a some error

                              ORA-12899: value too large for column "MIDEV5010C"."OPTUM_ICD10CM_BASE"."CODE" (actual: 68, maximum: 10);

                              DECLARE
                              f utl_file.file_type;
                              s nvarchar2(500);
                              f1 nvarchar2(500);
                              f2 nvarchar2(500);
                              F3 NVARCHAR2(500);
                              F4 NVARCHAR2(500);
                              F5 NVARCHAR2(500);
                              F6 NVARCHAR2(500);
                              F7 NVARCHAR2(500);
                              F8 NVARCHAR2(500);
                              F9 NVARCHAR2(500);
                              F10 NVARCHAR2(500);
                              f11 nvarchar2(500);
                              begin
                              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;
                              • 12. Re: 01861. 00000 -  "literal does not match format string"
                                jeneesh
                                There is a line in your sample

                                A50.2||Short|Long|Full|01-01-2009|01-01-2009|*67*|01-01-2013|09-18-2012|C|

                                Date value is coming as 67..! Except for that line,the below code should work
                                SQL> DECLARE
                                  2     V_LINE VARCHAR2(2000); -- Data line read from input file
                                  3     V_FILE UTL_FILE.FILE_TYPE;
                                  4     V_DIR VARCHAR2(250);
                                  5     V_FILENAME VARCHAR2(50);
                                  6  BEGIN
                                  7     V_DIR :='MID5010_DOC1TP';
                                  8     V_FILENAME := 'OPT_CM_BASE.txt';
                                  9     V_FILE := UTL_FILE.FOPEN(V_DIR, V_FILENAME, 'r');
                                 10     LOOP
                                 11       BEGIN
                                 12         UTL_FILE.GET_LINE(V_FILE, V_LINE);
                                 13       EXCEPTION
                                 14         WHEN NO_DATA_FOUND THEN
                                 15         EXIT;
                                 16       END ;
                                 17       v_line := replace(v_line,'|','|~');
                                 18       INSERT INTO OPTUM_ICD10CM_BASE
                                 19       VALUES (
                                 20             translate(regexp_substr(v_line,'[^|]+',1,1),'a~','a'),
                                 21             translate(regexp_substr(v_line,'[^|]+',1,2),'a~','a'),
                                 22             translate(regexp_substr(v_line,'[^|]+',1,3),'a~','a'),
                                 23             translate(regexp_substr(v_line,'[^|]+',1,4),'a~','a'),
                                 24             translate(regexp_substr(v_line,'[^|]+',1,5),'a~','a'),
                                 25             case when length(regexp_substr(v_line,'[^|]+',1,6)) < 10 then
                                 26                     to_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,6),'a~','a'),'0'),'mm-yyyy')
                                 27             else
                                 28                to_date(translate(regexp_substr(v_line,'[^|]+',1,6),'a~','a'),'mm-dd-yyyy')
                                 29             end,
                                 30             case when length(regexp_substr(v_line,'[^|]+',1,7)) < 10 then
                                 31                     to_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,7),'a~','a'),'0'),'mm-yyyy')
                                 32             else
                                 33                to_date(translate(regexp_substr(v_line,'[^|]+',1,7),'a~','a'),'mm-dd-yyyy')
                                 34             end,
                                 35             case when length(regexp_substr(v_line,'[^|]+',1,8)) < 10 then
                                 36                     to_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,8),'a~','a'),'0'),'mm-yyyy')
                                 37             else
                                 38                to_date(translate(regexp_substr(v_line,'[^|]+',1,8),'a~','a'),'mm-dd-yyyy')
                                 39             end,
                                 40             case when length(regexp_substr(v_line,'[^|]+',1,9)) < 10 then
                                 41                     to_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,9),'a~','a'),'0'),'mm-yyyy')
                                 42             else
                                 43                to_date(translate(regexp_substr(v_line,'[^|]+',1,9),'a~','a'),'mm-dd-yyyy')
                                 44             end,
                                 45             case when length(regexp_substr(v_line,'[^|]+',1,10)) < 10 then
                                 46                     to_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,10),'a~','a'),'0'),'mm-yyyy')
                                 47             else
                                 48                to_date(translate(regexp_substr(v_line,'[^|]+',1,10),'a~','a'),'mm-dd-yyyy')
                                 49             end,
                                 50             translate(regexp_substr(v_line,'[^|]+',1,11),'a~','a')
                                 51             );
                                 52    commit; --"Added for trouble shooting'
                                 53  END LOOP;
                                 54  UTL_FILE.FCLOSE(V_FILE);
                                 55  END;
                                 56  /
                                
                                PL/SQL procedure successfully completed.
                                
                                {code}
                                
                                Edited by: jeneesh on Mar 29, 2013 5:35 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                • 13. Re: 01861. 00000 -  "literal does not match format string"
                                  Ashu_Neo
                                  ...........
                                  ...........
                                  f1 := REGEXP_SUBSTR (s, '[^*]+', 1, 1);   -- '[^*]+' is wrong, check below for replacement..
                                  f2 := REGEXP_SUBSTR (s, '[^*]+', 1, 2);
                                  .........
                                  F9 := REGEXP_SUBSTR (S, '[^*]+', 1, 9);
                                  F10 := REGEXP_SUBSTR (S, '[^*]+', 1, 10);
                                  f11 := REGEXP_SUBSTR (s, '[^*]+', 1,11);
                                  ............
                                  ............
                                  Here pattern is wrong. Use '[^|]+' instead.

                                  you can verify it by yourself by passing it like this in a query for test.
                                    1* select REGEXP_SUBSTR ('A50.0||Short|Long|Full|01-01-2009|01-2009||01-01-2013|09-18-2012|C|', '[^|]+', 1, 2) dd from dual
                                  SQL> /
                                  
                                  DD
                                  -----
                                  Short
                                  Thanks!
                                  • 14. Re: 01861. 00000 -  "literal does not match format string"
                                    986379
                                    Hi

                                    That script executed just now but error like not valid month please check it

                                    DECLARE
                                    V_LINE VARCHAR2(2000); -- Data line read from input file
                                    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 (
                                    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_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,6),'a~','a'),'0'),'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_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,7),'a~','a'),'0'),'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_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,8),'a~','a'),'0'),'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_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,9),'a~','a'),'0'),'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_date(ltrim(translate(regexp_substr(v_line,'[^|]+',1,10),'a~','a'),'0'),'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;

                                    Regards,
                                    venkat
                                    1 2 Previous Next