This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Apr 1, 2013 2:45 AM by 986379 RSS

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

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

Legend

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