UTL File

declare
             fileptr    utl_file.file_type;
             v_buffer    varchar2(2048);
             line_num number(10):=0;
             loc_no  integer;
             string_var varchar2(4000);
              CURSOR process_unix_ssn_c(unix_ssn VARCHAR2)
              IS
              SELECT sp ,
              sp s_id3
              FROM sa.sp
              WHERE sp_id        =unix_ssn
              AND sp_change_ind IS NULL;
              v_process_unix_ssn process_unix_ssn_c%rowtype ;
             
            
            
     begin
             fileptr:=utl_file.fopen('G_USER_DIR','sp_details.txt','R');
             loop
                     line_num:=line_num+1;
                     utl_file.get_line(fileptr,v_buffer);
                     --dbms_output.put_line(line_num||' '||buffer);
                     string_var:=v_buffer;
                     dbms_output.put_line(string_var);
                    
                 
                  OPEN process_unix_ssn_c(v_buffer);
                  LOOP
                  FETCH process_unix_ssn_c INTO v_process_unix_ssn;
                  EXIT
                  WHEN process_unix_ssn_c%notfound;
                 
                  dbms_output.put_line('s_pidm3: '||v_process_unix_ssn.s_pidm3||' s_id3 : '||v_process_unix_ssn.s_id3);
                  END LOOP;
                  CLOSE process_unix_ssn_c;
                    
             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 ('GWU_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';
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;