7 Replies Latest reply: Apr 1, 2013 6:30 AM by Ashu_Neo RSS

    read text file insert into table using utl_file

    986379
      Hi

      i have script for read and insert into table but i want error records load into error table so i sent you my script and please fix the error log table
      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_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;

      text file
      ----------
      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|
      A5.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.2|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.3|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      D642|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.5|D|Short|Long|Full|01-01-2009|01-01-2009|01-10-2013|01-01-2013|09-18-2012|C|
      A5.6|D|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.7|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A001|C|Short Updated|Long Updated|Full Updated|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A009|C|Short Updated|Long Updated|Full Updated|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A5.10|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A0109|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.0|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.1|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.2|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.3|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.4|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.5|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.6|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      F10.7|N|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A30|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A316|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|
      A317|C|Short|Long|Full|01-01-2009|01-01-2009||01-01-2013|09-18-2012|C|

      ----clearly read text file insert into table and error records load into error table
      please help me