1 Reply Latest reply: Apr 4, 2013 1:30 AM by sybrand_b RSS

    loading file

    986379
      hi

      i have a file and script following below please check it but all records load into error table
      please help me

      text file
      -----------

      HZ87ZZZ||MED MGMT SUBSTANCE ABS TX BUPROPION|MEDICATION MANAGEMENT SUBSTANCE ABS TX BUPROPION|Medication Management for Substance Abuse Treatment, Bupropion|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ88||MEDICATION MGMT/PSYC MEDICATION|MEDICATION MANAGEMENT / PSYCHIATRIC MEDICATION|Medication Management / Psychiatric Medication|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ88Z||MED MGMT/PSYC MEDICATION/NONE|MEDICATION MGMT/PSYCHIATRIC MEDICATION/NONE|Medication Management / Psychiatric Medication / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ88ZZ||MED MGMT/PSYC MEDICATION/NONE/NONE|MEDICATION MGMT/PSYCHIATRIC MEDICATION/NONE/NONE|Medication Management / Psychiatric Medication / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ88ZZZ||MED MGMT SUBSTANCE ABS TX PSYC MED|MEDICATION MGMT SBSTNC ABS TX PSYCHIATRIC MED|Medication Management for Substance Abuse Treatment, Psychiatric Medication|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ89||MED MGMT/OTH REPLACEMENT MEDICATION|MEDICATION MANAGEMENT/OTH REPLACEMENT MEDICATION|Medication Management / Other Replacement Medication|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ89Z||MED MGMT/OTH REPLACEMENT MED/NONE|MEDICATION MGMT/OTH REPLACEMENT MEDICATION/NONE|Medication Management / Other Replacement Medication / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ89ZZ||MED MGMT/OTH REPLACE MED/NONE/NONE|MED MGMT/OTH REPLACEMENT MEDICATION/NONE/NONE|Medication Management / Other Replacement Medication / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ89ZZZ||MED MGMT SBSTNC ABS TX OTH REPL MED|MED MGMT SUBSTANCE ABS TX OTH REPLACEMENT MED|Medication Management for Substance Abuse Treatment, Other Replacement Medication|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ9|C|PHARMACOTHERAPY / NONE|PHARMACOTHERAPY / NONE|Pharmacotherapy / None|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ90||PHARMACOTHERAPY/NICOTINE REPLACE|PHARMACOTHERAPY / NICOTINE REPLACEMENT|Pharmacotherapy / Nicotine Replacement|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ90Z||PHARMACOTHERAPY/NICOTINE REPL/NONE|PHARMACOTHERAPY / NICOTINE REPLACEMENT / NONE|Pharmacotherapy / Nicotine Replacement / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ90ZZ||PT/NICOTINE REPLACEMENT/NONE/NONE|PHARMACOTHERAPY/NICOTINE REPLACEMENT/NONE/NONE|Pharmacotherapy / Nicotine Replacement / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ90ZZZ||PT SBSTNC ABS TX NICOTINE REPLACE|PHARMACOTHERAPY SBSTNC ABS TX NICOTINE REPLACE|Pharmacotherapy for Substance Abuse Treatment, Nicotine Replacement|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ91||PHARMACOTHERAPY/METHADONE MAINT|PHARMACOTHERAPY / METHADONE MAINTENANCE|Pharmacotherapy / Methadone Maintenance|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ91Z||PHARMACOTHERAPY/METHADONE MNT/NONE|PHARMACOTHERAPY / METHADONE MAINTENANCE / NONE|Pharmacotherapy / Methadone Maintenance / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ91ZZ||PT/METHADONE MAINT/NONE/NONE|PHARMACOTHERAPY/METHADONE MAINTENANCE/NONE/NONE|Pharmacotherapy / Methadone Maintenance / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ91ZZZ||PT SUBSTANCE ABS TX METHADONE MAINT|PHARMACOTHERAPY SUBSTANCE ABS TX METHADONE MAINT|Pharmacotherapy for Substance Abuse Treatment, Methadone Maintenance|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ92||PT/LEVO-ALPHA-ACETYL-METHADOL LAAM|PHARMACOTHERAPY / LEVO-ALPHA-ACETYL-METHADOL|Pharmacotherapy / Levo-alpha-acetyl-methadol (LAAM)|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ92Z||PT/LAAM/NONE|PT/LEVO-ALPHA-ACETYL-METHADOL LAAM/NONE|Pharmacotherapy / Levo-alpha-acetyl-methadol (LAAM) / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ92ZZ||PT/LAAM/NONE/NONE|PT/LEVO-ALPHA-ACETYL-METHADOL LAAM/NONE/NONE|Pharmacotherapy / Levo-alpha-acetyl-methadol (LAAM) / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ92ZZZ||PT SUBS ABS TX LAAM|PHARMACOTHERAPY SUBSTANCE ABUSE TREATMENT LAAM|Pharmacotherapy for Substance Abuse Treatment, Levo-alpha-acetyl-methadol (LAAM)|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ93||PHARMACOTHERAPY / ANTABUSE|PHARMACOTHERAPY / ANTABUSE|Pharmacotherapy / Antabuse|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ93Z||PHARMACOTHERAPY / ANTABUSE / NONE|PHARMACOTHERAPY / ANTABUSE / NONE|Pharmacotherapy / Antabuse / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ93ZZ||PHARMACOTHERAPY/ANTABUSE/NONE/NONE|PHARMACOTHERAPY / ANTABUSE / NONE / NONE|Pharmacotherapy / Antabuse / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ93ZZZ||PT SUBSTANCE ABS TX ANTABUSE|PHARMACOTHERAPY FOR SUBSTANCE ABUSE TX ANTABUSE|Pharmacotherapy for Substance Abuse Treatment, Antabuse|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ94||PHARMACOTHERAPY / NALTREXONE|PHARMACOTHERAPY / NALTREXONE|Pharmacotherapy / Naltrexone|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ94Z||PHARMACOTHERAPY / NALTREXONE / NONE|PHARMACOTHERAPY / NALTREXONE / NONE|Pharmacotherapy / Naltrexone / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ94ZZ||PT/NALTREXONE/NONE/NONE|PHARMACOTHERAPY / NALTREXONE / NONE / NONE|Pharmacotherapy / Naltrexone / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ94ZZZ||PT SUBSTANCE ABS TX NALTREXONE|PHARMACOTHERAPY FOR SUBSTANCE ABS TX NALTREXONE|Pharmacotherapy for Substance Abuse Treatment, Naltrexone|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ95||PHARMACOTHERAPY / NALOXONE|PHARMACOTHERAPY / NALOXONE|Pharmacotherapy / Naloxone|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ95Z||PHARMACOTHERAPY / NALOXONE / NONE|PHARMACOTHERAPY / NALOXONE / NONE|Pharmacotherapy / Naloxone / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ95ZZ||PHARMACOTHERAPY/NALOXONE/NONE/NONE|PHARMACOTHERAPY / NALOXONE / NONE / NONE|Pharmacotherapy / Naloxone / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ95ZZZ||PT SUBSTANCE ABS TX NALOXONE|PHARMACOTHERAPY FOR SUBSTANCE ABUSE TX NALOXONE|Pharmacotherapy for Substance Abuse Treatment, Naloxone|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ96||PHARMACOTHERAPY / CLONIDINE|PHARMACOTHERAPY / CLONIDINE|Pharmacotherapy / Clonidine|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ96Z||PHARMACOTHERAPY / CLONIDINE / NONE|PHARMACOTHERAPY / CLONIDINE / NONE|Pharmacotherapy / Clonidine / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ96ZZ||PHARMACOTHERAPY/CLONIDINE/NONE/NONE|PHARMACOTHERAPY / CLONIDINE / NONE / NONE|Pharmacotherapy / Clonidine / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ96ZZZ||PT SUBSTANCE ABS TX CLONIDINE|PHARMACOTHERAPY FOR SUBSTANCE ABUSE TX CLONIDINE|Pharmacotherapy for Substance Abuse Treatment, Clonidine|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ97||PHARMACOTHERAPY / BUPROPION|PHARMACOTHERAPY / BUPROPION|Pharmacotherapy / Bupropion|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ97Z||PHARMACOTHERAPY / BUPROPION / NONE|PHARMACOTHERAPY / BUPROPION / NONE|Pharmacotherapy / Bupropion / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ97ZZ||PHARMACOTHERAPY/BUPROPION/NONE/NONE|PHARMACOTHERAPY / BUPROPION / NONE / NONE|Pharmacotherapy / Bupropion / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ97ZZZ||PT SUBSTANCE ABS TX BUPROPION|PHARMACOTHERAPY FOR SUBSTANCE ABUSE TX BUPROPION|Pharmacotherapy for Substance Abuse Treatment, Bupropion|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ98||PHARMACOTHERAPY/PSYC MEDICATION|PHARMACOTHERAPY / PSYCHIATRIC MEDICATION|Pharmacotherapy / Psychiatric Medication|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ98Z||PHARMACOTHERAPY/PSYC MED/NONE|PHARMACOTHERAPY / PSYCHIATRIC MEDICATION / NONE|Pharmacotherapy / Psychiatric Medication / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ98ZZ||PHARMACOTHERAPY/PSYC MED/NONE/NONE|PHARMACOTHERAPY/PSYCHIATRIC MEDICATION/NONE/NONE|Pharmacotherapy / Psychiatric Medication / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ98ZZZ||PT SUBSTANCE ABS TX PSYC MEDICATION|PHARMACOTHERAPY SBSTNC ABS TX PSYCHIATRIC MED|Pharmacotherapy for Substance Abuse Treatment, Psychiatric Medication|01-01-2008|01-01-2009||01-01-2013|09-18-2012|C|
      HZ99||PHARMACOTHERAPY/OTH REPLACEMENT MED|PHARMACOTHERAPY / OTHER REPLACEMENT MEDICATION|Pharmacotherapy / Other Replacement Medication|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
      HZ99Z||PHARMACOTHERAPY/OTH REPL MED/NONE|PHARMACOTHERAPY/OTH REPLACEMENT MEDICATION/NONE|Pharmacotherapy / Other Replacement Medication / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|
      HZ99ZZ||PT/OTH REPLACEMENT MED/NONE/NONE|PT/OTH REPLACEMENT MEDICATION/NONE/NONE|Pharmacotherapy / Other Replacement Medication / None / None|01-01-2012|01-01-2012||01-01-2013|09-18-2012|I|

      script
      ----------
      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,' ','||');
      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. Re: loading file
          sybrand_b
          Again:

          The worst method to load files is what you are doing here, not using the power of Oracle at all.

          Use external tables

          And please STOP posting the same question over and over and over and over again.


          --------------
          Sybrand Bakker
          Senior Oracle DBA