Remove last blank line from file

Question

I have a code that gets data from a file, file contains last empty/blank line, for example if a file contains 4 records then the 5th line is blank one, how can I remove that blank line because when the code executes it rolled back the whole transaction due to that blank line and throws "no data found" exception. Below is my code:

create or replace PROCEDURE UPLOAD_CMS_VISA_RR
AS
BEGIN
  DECLARE
   f utl_file.file_type;
s                  VARCHAR2(4000);
BRANCH_CODE        VARCHAR2(500);
TRANS_DATE         VARCHAR2(500);
TRANS_TIME         VARCHAR2(500);
SEQ_NO             VARCHAR2(500);
MSG_TYPE           VARCHAR2(500);
REMOTE_ACCOUNT     VARCHAR2(500);
FRG_CURR_AMT       VARCHAR2(500);
LOC_CURR_AMT       VARCHAR2(500);
CHEQUE_NO          VARCHAR2(500);
IBCA_NO            VARCHAR2(500);
CR_DR_CODE         VARCHAR2(500);
DESC_DETAIL        VARCHAR2(500);
DOC_NO             VARCHAR2(500);
Y_LIVECMGT_ID      VARCHAR2(500);
FILLER_ONE         VARCHAR2(500);
T24_ACC_NO         VARCHAR2(500);
FILLER_TWO         VARCHAR2(500);
INST_CODE          VARCHAR2(500);
FILLER             VARCHAR2(500);
LAST_DATE_MODIFIED VARCHAR2(500);
LEAD_COMPANY       VARCHAR2(500);
LEAD_COUNTRY       VARCHAR2(500);
V_TABLE_RECORD_COUNT NUMBER :=0;
V_FILE_RECORD_COUNT NUMBER :=0;
-- start_pos NUMBER;
ex    BOOLEAN;
flen  NUMBER;
bsize NUMBER;
CURSOR C1
IS
  SELECT FILENAME
  FROM DIR_LIST2
  WHERE FILENAME = 'DESTRNFL.20170726.0401671434.00';
BEGIN
GET_DIR_LIST2( '/cdb/CSHMGMT' );
FOR FILE_REC IN C1
LOOP
  UTL_FILE.FGETATTR('CSHMGMT', FILE_REC.FILENAME, EX, FLEN, BSIZE);
  DBMS_OUTPUT.PUT_LINE(FILE_REC.FILENAME);
  IF EX THEN

  V_TABLE_RECORD_COUNT :=0;
  V_FILE_RECORD_COUNT:= 0;

    dbms_output.put_line('File Exist');
    F := UTL_FILE.FOPEN('CSHMGMT', FILE_REC.FILENAME, 'R');
    LOOP
      BEGIN
      V_FILE_RECORD_COUNT := V_FILE_RECORD_COUNT + 1;

        UTL_FILE.GET_LINE(F,S);
        BRANCH_CODE        := (SUBSTR(s, 1, 4));
        TRANS_DATE         := (SUBSTR(S, 5,8));
        TRANS_TIME         := (SUBSTR(S, 13,6));
        SEQ_NO             := (SUBSTR(S,19,10));
        MSG_TYPE           := (SUBSTR(S,29,4));
        REMOTE_ACCOUNT     := (SUBSTR(S,33,10));
        FRG_CURR_AMT       := (SUBSTR(S,43,14));
        LOC_CURR_AMT       := (SUBSTR(S,57,14));
        CHEQUE_NO          := (SUBSTR(S,71,8));
        IBCA_NO            := (SUBSTR(S,79,8));
        CR_DR_CODE         := (SUBSTR(S,87,1));
        DESC_DETAIL        := (SUBSTR(s,88,21));
        DOC_NO             := (SUBSTR(S,109,6));
        Y_LIVECMGT_ID      := (SUBSTR(s,115,16));
        FILLER_ONE         := (SUBSTR(S,131,65));
        T24_ACC_NO         := (SUBSTR(s,196,16));
        FILLER_TWO         := (SUBSTR(s,212,199));
        INST_CODE          := (SUBSTR(S,411,6));
        FILLER             := (SUBSTR(s,417));
        LAST_DATE_MODIFIED := NULL;
        LEAD_COMPANY       := 001;
        LEAD_COUNTRY       := 'PK';
        INSERT
        INTO CMS_VISA_TEST_RR
          (
            BRANCH_CODE,
            TRANS_DATE,
            TRANS_TIME,
            SEQ_NO,
            MSG_TYPE,
            REMOTE_ACCOUNT,
            FRG_CURR_AMT,
            LOC_CURR_AMT,
            CHEQUE_NO,
            IBCA_NO,
            CR_DR_CODE,
            DESC_DETAIL,
            DOC_NO,
            Y_LIVECMGT_ID,
            FILLER_ONE,
            T24_ACC_NO,
            FILLER_TWO,
            INST_CODE,
            FILLER,
            LAST_DATE_MODIFIED,
            LEAD_COMPANY,
            LEAD_COUNTRY,
            SYSTEM_DATE
          )
          VALUES
          (
            TRIM(BRANCH_CODE),
            CONVERT_TO_DATE(TRANS_DATE),
            TRIM(TRANS_TIME),
            TRIM(SEQ_NO),
            TRIM(MSG_TYPE),
            TRIM(REMOTE_ACCOUNT),
            TRIM(FRG_CURR_AMT),
            TRIM(LOC_CURR_AMT),
            TRIM(CHEQUE_NO),
            TRIM(IBCA_NO),
            TRIM(CR_DR_CODE),
            TRIM(DESC_DETAIL),
            TRIM(DOC_NO),
            TRIM(Y_LIVECMGT_ID),
            TRIM(FILLER_ONE),
            TRIM(T24_ACC_NO),
            TRIM(FILLER_TWO),
            TRIM(INST_CODE),
            TRIM(FILLER),
            CONVERT_TO_DATE(LAST_DATE_MODIFIED),
            LEAD_COMPANY,
            LEAD_COUNTRY,
            SYSDATE
          );
        COMMIT;

        V_TABLE_RECORD_COUNT := V_TABLE_RECORD_COUNT +1;
        --END IF;
             EXCEPTION
      WHEN utl_file.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Path');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Invalid Pat','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN utl_file.INVALID_MODE THEN
        dbms_output.put_line('Invalid Mode');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Invalid Mod','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN UTL_FILE.INVALID_FILEHANDLE THEN
        dbms_output.put_line('Invalid File Handler');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Invalid File Handler','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN utl_file.INVALID_OPERATION THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Operation');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Invalid Operation','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN utl_file.READ_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Read Error');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Read Error','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN utl_file.WRITE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Write Error');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Write Error','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN utl_file.INTERNAL_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Internal Error');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Internal Error','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Value error');
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'Value error','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
      WHEN NO_DATA_FOUND THEN
        ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'NO_DATA_FOUND','FILE_NAME='|| FILE_REC.FILENAME||' TRANS_TIME='|| TRIM(TRANS_TIME)||' LOC_CURR_AMT='||TRIM(LOC_CURR_AMT)||' TT NO='||Y_LIVECMGT_ID, SQLCODE, SUBSTR(SQLERRM, 1, 500));
        --    commit;
        --              utl_file.fcopy('CDB_UPLOAD', 'export.log', 'CDB_UPLOAD', 'export.log.processed');
        --              utl_file.frename('CSHMGMT', FILE_REC.FILENAME, 'CMS_DHA', FILE_REC.FILENAME|| '_' || SYSDATE);
        --              utl_file.frename('PHXEXPORT', 'export.log', 'PHXEXPORT', 'export.log.processed' || SYSDATE);
        EXIT;
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Other Error');
        CONTINUE;
      END;
    END LOOP;
    --INSERT  INTO CMS_CONTROL_RR ( FILENAME,TABLE_RECORD_COUNT,FILE_RECORD_COUNT, SYSTEM_DATE ) VALUES ( FILE_REC.FILENAME,V_TABLE_RECORD_COUNT,V_FILE_RECORD_COUNT,  SYSDATE  );
    --COMMIT;
    --UTL_FILE.FREMOVE ('CSHMGMT', FILE_REC.FILENAME);
    utl_file.fclose(f);
  END IF;
END LOOP;
COMMIT;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'CMS_VISA_TEST_RR', '', SQLCODE, SUBSTR(SQLERRM, 1, 500));
  WHEN OTHERS THEN
ROLLBACK;
ERROR_LOGGER ('UPLOAD_CMS_VISA_RR', 'CMS_VISA_TEST_RR', '', SQLCODE,    SUBSTR(SQLERRM, 1, 500));
END;
    END UPLOAD_CMS_VISA_RR;

Rene · Accepted Answer · 2017-07-28T10:37:45.320

0

When looping through a file using utl_file, how do you know that you've reached the end of the file? Well, a no_data_found execption is thrown.

So this would be the structure of your loop.

BEGIN
  LOOP
      UTL_FILE.GET_LINE (file, buffer);

      -- evaluate what you've got from the file
      -- and proces it, or not.  In this case we just print it.

      -- Edit: if buffer is empty don't proces it.
      if buffer is not null then
         DBMS_OUTPUT.PUT_LINE(buffer);
      end if;
  END LOOP;

  EXCEPTION  
     --if end of file not action
     WHEN NO_DATA_FOUND 
     THEN 
        NULL;

END;

edited Jul 28 '17 at 10:37

answered Jul 28 '17 at 07:02

Rene

10,391
5
33
46

Means, should I remove the no data found exception? and consider it as EOF? Because at the end of each file blank line appears. – KeenLearner Jul 28 '17 at 10:32
No, the no_data_found is the way to find out the end of the file. Within the loop you can determine what to do with each line you've read from the file. If it's an empty line don't proces it.(see edit). – Rene Jul 28 '17 at 10:37

Remove last blank line from file

1 Answers1