12 Replies Latest reply: Apr 4, 2013 4:37 AM by Manik RSS

    utl_file

    983124
      DECLARE
      v_fileID UTL_FILE.FILE_TYPE;
      v_dir VARCHAR2(50) := 'MID5010_DOC1TP';
      v_filename VARCHAR2(50) := 'OPTUM_ICD10PCS_BASE.TXT';
      v_buffer VARCHAR2(32000);
      v_diag_code VARCHAR2 (8);
      V_STATUS VARCHAR2 (1);
      V_SHORT_DESC VARCHAR2 (50);
      V_LONG_DESC VARCHAR2 (50);
      V_FULL_DESC VARCHAR2 (2000);
      V_CODE_EFT_DATE DATE;
      V_CHAN_EFT_DATE DATE;
      V_TERMI_DATE DATE;
      V_RLSE_DATE DATE;
      V_FIL_DATE DATE;
      V_VALID VARCHAR2(1);
      V_FF_STG_SID NUMBER(10):=1001;
      V_PROCESSED VARCHAR2(1):='Y';
      BEGIN
      Dbms_Output.Enable(100000);
      v_fileID := UTL_FILE.FOPEN ( v_dir , v_filename, 'r', 32000);
      LOOP
      BEGIN
      Utl_File.Get_Line(v_fileID, v_buffer);
      v_buffer := Replace(v_buffer,'||','| |');
      v_diag_code :=Replace(Regexp_Substr (v_buffer, '[^|]+', 1, 1),'.');
      V_STATUS := Regexp_Substr (v_buffer, '[^|]+', 1, 2);
      V_SHORT_DESC := Regexp_Substr (v_buffer, '[^|]+', 1, 3);
      V_LONG_DESC := Regexp_Substr (v_buffer, '[^|]+', 1, 4);
      V_FULL_DESC := Regexp_Substr (v_buffer, '[^|]+', 1, 5);
      V_CODE_EFT_DATE := To_Date(Regexp_Substr (v_buffer, '[^|]+', 1, 6) ,'MM-DD-YYYY');
      V_CHAN_EFT_DATE :=
      CASE
      WHEN LENGTH(Regexp_Substr (v_buffer, '[^|]+', 1, 7)) < 10 THEN
      To_Date(Regexp_Substr (v_buffer, '[^|]+', 1, 7) ,'MM-YYYY')
      Else
      To_Date(Regexp_Substr (v_buffer, '[^|]+', 1, 7) ,'MM-DD-YYYY')
      END;
      V_TERMI_DATE := To_Date(Regexp_Substr (v_buffer,'[^|]+', 1, 8) ,'MM-DD-YYYY');
      V_RLSE_DATE := To_Date(Regexp_Substr (v_buffer,'[^|]+', 1, 9) ,'MM-DD-YYYY');
      V_FIL_DATE := To_Date(Regexp_Substr (v_buffer, '[^|]+', 1, 10) ,'MM-DD-YYYY');
      V_VALID := Regexp_Substr (v_buffer, '[^|]+', 1,11);
      INSERT INTO IST_1002_02A_CM_BASE_E (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(
      v_diag_code,
      V_STATUS,
      V_SHORT_DESC,
      V_LONG_DESC,
      V_FULL_DESC,
      V_CODE_EFT_DATE,
      V_CHAN_EFT_DATE,
      V_TERMI_DATE,
      V_RLSE_DATE,
      V_FIL_DATE,
      V_VALID,
      V_FF_STG_SID,
      V_PROCESSED);
      EXCEPTION
      WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('invalid path');
      WHEN NO_DATA_FOUND THEN
      EXIT;
      WHEN OTHERS THEN
      INSERT INTO OPT_ERR_LOG_E VALUES(v_buffer);
      END;
      END LOOP;
      END;
        • 1. Re: utl_file
          BluShadow
          So what's the problem?

          {message:id=9360002}
          • 2. Re: utl_file
            jeneesh
            Is this someway related to this - error_recods(utl_file)
            • 3. Re: utl_file
              983124
              my requirement is to read the pipe(|) separated data from text file and insert into PCS_BASE table.my script inserts all the records to error log table only.please send me the script.
              my script is:
              DECLARE
              F Utl_File.File_Type;
              S VARCHAR2(32000);
              S1 VARCHAR2(32000);
              S2 VARCHAR2(32000);
              F1 VARCHAR2(10);
              F2 VARCHAR2(100);
              F3 VARCHAR2(100);
              F4 VARCHAR2(100);
              F5 VARCHAR2(100);
              F6 DATE;
              F7 DATE;
              F8 DATE;
              F9 DATE;
              F10 DATE;
              F11 VARCHAR2(1);
              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,'||','| |');
              S1 := REPLACE(S,'/','-');
              F1 :=REPLACE(Regexp_Substr (S1,'[^|]+', 1, 1),'.');
              F2 := Regexp_Substr (S1,'[^|]+', 1, 2);
              F3 := Regexp_Substr (S1,'[^|]+', 1, 3);
              F4 := Regexp_Substr (S1,'[^|]+', 1, 4);
              F5 := Regexp_Substr (S1,'[^|]+', 1, 5);
              F6 := To_Date(Regexp_Substr (S1,'[^|]+',1,6) ,'DD-MM-YYYY');
              F7 := To_Date(Regexp_Substr (S1,'[^|]+',1,7) ,'DD-MM-YYYY');
              F8 := To_Date(Regexp_Substr (S1,'[^|]+',1,8) ,'DD-MM-YYYY');
              F9 := To_Date(Regexp_Substr (S1,'[^|]+',1,9) ,'DD-MM-YYYY');
              F10 := To_Date(Regexp_Substr (S1, '[^|]+',1,10),'DD-MM-YYYY');
              F11 := Regexp_Substr (S1,'[^|]+', 1,11);
              INSERT INTO IST_1002_02A_PCS_BASE_E (CODE,
              STATUS,
              SHORT_DESCRIPTION,
              LONG_DESCRIPTION,
              FULL_DESCRIPTION,
              CODE_EFFECTIVE_DATE,
              CHANGE_EFFECTIVE_DATE,
              TERMINATION_DATE,
              RELEASE_DATE,
              File_DATE,
              VALIDITY)
              VALUES(
              F1,
              F2,
              F3,
              F4,
              F5,
              F6,
              F7,
              F8,
              F9,
              F10,
              F11);
              EXCEPTION
              WHEN No_Data_Found THEN
              EXIT;
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE (SQLCODE || ' - ' || SQLERRM);
              INSERT INTO Err_Opts VALUES(S1);
              END;
              End Loop;
              END;
              • 4. Re: utl_file
                BluShadow
                Use an External Table, it's much easier.
                • 5. Re: utl_file
                  983124
                  ok.but they want only script.
                  my text file like:
                  001|C|BYPASS / CENTRAL NERVOUS SYSTEM|BYPASS / CENTRAL NERVOUS SYSTEM|Bypass / Central Nervous System|01-01-2009|01-01-2009||01-01-2013|09-18-2012|I|
                  • 6. Re: utl_file
                    983124
                    please send me the script .
                    • 7. Re: utl_file
                      BluShadow
                      7malai wrote:
                      please send me the script .
                      You have to learn patience.
                      Volunteers on this forum have their own work to do and are not just sitting around here waiting to do your work for you.
                      It is considered rude and poor forum etiquette to keep bumping your thread and making demands of the volunteers.
                      • 8. Re: utl_file
                        jeneesh
                        BluShadow wrote:
                        You have to learn patience.
                        @OP: And you have to try to learn also..
                        • 9. Re: utl_file
                          John Stegeman
                          ok.but they want only script.
                          Who are "they," and why do they want you to do things in the slowest possible way?
                          • 10. Re: utl_file
                            sushaant
                            Try to implement each reg_Ex command in single sql statement by querying dual. The error is occurring during date conversion of null or non date entries. Please correct it and use NVL function to handle null values. I hope it should resolve

                            Cheers
                            Sush
                            • 11. Re: utl_file
                              Manik
                              I guess you are incorrectly doing a date format there

                              Check this date:
                              09-18-2012
                              You are trying to convert it as dd-MM-yyyy (no calander on this universe has 18th month for an year :) so it fails.. correct it and try )

                              Cheers,
                              Manik.
                              • 12. Re: utl_file
                                983124
                                yeah i solved.thanks for your supporting.