oracle - Remove last blank line from file -


i have code gets data file, file contains last empty/blank line, example if file contains 4 records 5th line blank one, how can remove blank line because when code executes rolled whole transaction due blank line , throws "no data found" exception. below code:

create or replace procedure upload_cms_visa_rr 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   select filename   dir_list2   filename = 'destrnfl.20170726.0401671434.00'; begin get_dir_list2( '/cdb/cshmgmt' ); file_rec in c1 loop   utl_file.fgetattr('cshmgmt', file_rec.filename, ex, flen, bsize);   dbms_output.put_line(file_rec.filename);   if ex    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         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         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         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         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         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         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         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         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         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         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         dbms_output.put_line('other error');         continue;       end;     end loop;     --insert  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 error_logger ('upload_cms_visa_rr', 'cms_visa_test_rr', '', sqlcode, substr(sqlerrm, 1, 500));   when others rollback; error_logger ('upload_cms_visa_rr', 'cms_visa_test_rr', '', sqlcode,    substr(sqlerrm, 1, 500)); end;     end upload_cms_visa_rr;

when looping through file using utl_file, how know you've reached end of file? well, no_data_found execption thrown.

so structure of loop.

begin   loop       utl_file.get_line (file, buffer);        -- evaluate you've got file       -- , proces it, or not.  in case print it.        -- edit: if buffer empty don't proces it.       if buffer not null          dbms_output.put_line(buffer);       end if;   end loop;    exception        --if end of file not action      when no_data_found                null;  end;

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more