1 2 Previous Next 26 Replies Latest reply: Apr 1, 2013 4:45 AM by 986379 Go to original post RSS
      • 15. Re: 01861. 00000 -  "literal does not match format string"
        986379
        HI

        can you tel me how to pass dynamically instaed of 11 columns just define one variable i sent you query my script please modify

        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;
        • 16. Re: 01861. 00000 -  "literal does not match format string"
          986379
          HI I GOT A SOME ERROR LIKE 01843. 00000 - "not a valid month"


          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;
          • 18. Re: 01861. 00000 -  "literal does not match format string"
            986379
            hi,

            i got error like PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got DATE
            please chek it help me


            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
            (
            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;
            • 19. Re: 01861. 00000 -  "literal does not match format string"
              Ramin Hashimzadeh
              You are asking a lot of questions and all your questions have an answer in Google. First, learn to work with google
              • 20. Re: 01861. 00000 -  "literal does not match format string"
                986379
                hi,

                this task very difficult for me and first time i am working on utl_files
                last 3 days diacuss with you and you gave lot information
                as per text file loading into database please help me. i am already gave text file and script also please check it

                THANKS FOR MORE HELP
                • 21. Re: 01861. 00000 -  "literal does not match format string"
                  986379
                  HI,

                  please checlk it my query getting a some error like that ORA-01861: literal does not match format string

                  script:
                  -------
                  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_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line, '[^|~]+',1,6),'a~','a'),'0'),'mm-dd-yyyy'),'mm-dd-yyyy')
                  END,
                  CASE
                  WHEN LENGTH(regexp_substr(v_line,'[^|~]+',1,7)) < 10
                  THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line,'[^|~]+',1,7),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                  ELSE TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line, '[^|~]+',1,7),'a~','a'),'0'),'mm-dd-yyyy'),'mm-dd-yyyy')
                  END,
                  CASE
                  WHEN LENGTH(regexp_substr(v_line,'[^|~]+',1,8)) < 10
                  THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line,'[^|~]+',1,8),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                  ELSE TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line, '[^|~]+',1,8),'a~','a'),'0'),'mm-dd-yyyy'),'mm-dd-yyyy')
                  END,
                  CASE
                  WHEN LENGTH(regexp_substr(v_line,'[^|~]+',1,9)) < 10
                  THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line,'[^|~]+',1,9),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                  ELSE TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line, '[^|~]+',1,9),'a~','a'),'0'),'mm-dd-yyyy'),'mm-dd-yyyy')
                  END,
                  CASE
                  WHEN LENGTH(regexp_substr(v_line,'[^|~]+',1,10)) < 10
                  THEN TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line,'[^|~]+',1,10),'a~','a'),'0'),'mm-dd-yyyy'),'mm-yyyy')
                  ELSE TO_CHAR(to_date(ltrim(TRANSLATE(regexp_substr(v_line, '[^|~]+',1,10),'a~','a'),'0'),'mm-dd-yyyy'),'mm-dd-yyyy')
                  END,
                  TRANSLATE(regexp_substr(v_line,'[^|~]+',1,11),'a~','a')
                  );
                  -----commit;
                  END LOOP;
                  utl_file.fclose(v_file);
                  END;
                  • 22. Re: 01861. 00000 -  "literal does not match format string"
                    jeneesh
                    Did you read what I posted?
                    jeneesh wrote:
                    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
                    What is the meaning of 67 in a DATE field?

                    Or if you want to neglect all those values, try
                    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)) < 7 then
                           null
                         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,6)) < 7 then
                           null
                         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,6)) < 7 then
                           null
                         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,6)) < 7 then
                           null
                         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,6)) < 7 then
                           null
                         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;
                    • 23. Re: 01861. 00000 -  "literal does not match format string"
                      986379
                      hi

                      in this script

                      sql query not working please check it

                      SELECT to_date(ltrim(TRANSLATE(regexp_substr('A50.2||Short|Long|Full|01-01-2009|01-01-2009|*67*|01-01-2013|09-18-2012|C|','[^|]+',1,6),'a~','a'),'0'),
                      'mm-yyyy') from dual;
                      • 24. Re: 01861. 00000 -  "literal does not match format string"
                        Ramin Hashimzadeh
                        SELECT to_char(to_date(REGEXP_SUBSTR('A50.2||Short|Long|Full|01-01-2009|01-01-2009|*67*|01-01-2013|09-18-2012|C|',
                                                                     '[^|]+',
                                                                     1,
                                                                     6),'mm-dd-yyyy'),'mm-yyyy')
                          FROM DUAL;
                        • 25. Re: 01861. 00000 -  "literal does not match format string"
                          986379
                          HI

                          please check it my script

                          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)) < 7
                          THEN NULL
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,6)) < 10
                          THEN to_char(to_date(REGEXP_SUBSTR(v_line,'[^|]+',1,6),'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,6)) < 7
                          THEN NULL
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,7)) < 10
                          THEN to_char(to_date(REGEXP_SUBSTR(v_line,'[^|]+',1,7),'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,6)) < 7
                          THEN NULL
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,8)) < 10
                          THEN to_char(to_date(REGEXP_SUBSTR(v_line,'[^|]+',1,8),'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,6)) < 7
                          THEN NULL
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,9)) < 10
                          THEN to_char(to_date(REGEXP_SUBSTR(v_line,'[^|]+',1,9),'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,6)) < 7
                          THEN NULL
                          WHEN LENGTH(regexp_substr(v_line,'[^|]+',1,10)) < 10
                          THEN to_char(to_date(REGEXP_SUBSTR(v_line,'[^|]+',1,10),'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;

                          but gettin error like inconsistent datatypes: expected CHAR got DATE

                          my target table datatype date plaease help me
                          • 26. Re: 01861. 00000 -  "literal does not match format string"
                            Ramin Hashimzadeh
                            offff.. Char and Date is diffrent datatypes.
                                   CASE WHEN LENGTH(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 6)) < 7 
                                             THEN NULL 
                                        WHEN LENGTH(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 8)) < 10 
                                             THEN TO_CHAR(TO_DATE(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 8), 'mm-dd-yyyy'),'mm-yyyy') 
                                             ELSE TO_DATE(TRANSLATE(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 8), 'a~', 'a'),'mm-dd-yyyy') END,
                            ----
                            sorry, I Stopped Watching this Thread

                            Edited by: Ramin Hashimzadeh on Apr 1, 2013 2:52 PM
                            1 2 Previous Next