Thursday, January 11, 2018

UTL_FILE another Query

declare
             fileptr    utl_file.file_type;
             buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=buffer;
                     dbms_output.put_line(string_var);
                    
             end loop;
     exception
             when no_data_found then
                     utl_file.fclose(fileptr);
                     dbms_output.put_line('Number of lines parsed ='||line_num);
             when value_error then
                     dbms_output.put_line('value error');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_path  then
                     dbms_output.put_line('invalid path');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_mode  then
                     dbms_output.put_line('invalid_mode');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_filehandle  then
                     dbms_output.put_line('invalid_filehandle');
                     raise_application_error(-20100,'file error');
             when utl_file.invalid_operation  then
                     dbms_output.put_line('invalid_operation');
                     raise_application_error(-20100,'file error');
             when utl_file.read_error  then
                     dbms_output.put_line('read_error');
                     raise_application_error(-20100,'file error');
             when utl_file.write_error  then
                     dbms_output.put_line('write_error');
                     raise_application_error(-20100,'file error');
             when utl_file.internal_error  then
                     dbms_output.put_line('internal_error');
                     raise_application_error(-20100,'file error');
             when others then
                     dbms_output.put_line('un-handled');
                     raise_application_error(-20100,'file error');
     end;


-------------------------------------------------------------------

DECLARE
file1   UTL_FILE.file_type;
p_line VARCHAR2(4000);
BEGIN
file1 := UTL_FILE.fopen ('G_USER_DIR', 'sp_details.txt', 'w');
p_line := '19320418'||','||'18649335'||','||'18016235'||','||'14032419'||','||'10383346'||','||'13194228'||','||'18882026'||','||'11073020'||','||'19539731'||','||'16712789'||','||'10808330'||','||'14903288'||','||'17118027'||','||'15928693'||','||'11532136'||','||'12627057'||','||'19961984'||','||'19138862'||','||'11398161'||','||'16115467'||','||'14890139'||','||'11560049'||','||'14105816'||','||'17121334'||','||'19542285'||','||'11384858'||','||'17418279'||','||'13293065'||','||'14692220'||','||'11362967'||','||'10436234'||','||'19341379'||','||'16134493'||','||'15493206'||','||'15745525'||','||'15135785'||','||'16573746'||','||'13439175'||','||'16272382'||','||'11229845'||','||'12836326'||','||'11402844'||','||'13419723'||','||'19343730'||','||'11310078'||','||'18766875'||','||'10249762'||','||'10849323'||','||'16495481'||','||'15412637';
UTL_FILE.put_line (file1, p_line);
UTL_FILE.fclose (file1);
END;

No comments:

Post a Comment