CREATE OR REPLACE PROCEDURE xxaj_utl_file
v_file_handle
UTL_FILE.FILE_TYPE;
CURSOR c_supplier IS
select
a.vendor_name,
a.segment1
Vendror#,
site.vendor_site_code,
site.address_line1,
site.ADDRESS_LINE2,
CITY,
STATE,
country,
zip
FROM ap_suppliers a,
ap_supplier_sites_all site
WHERE a.vendor_id = site.vendor_id
AND site.org_id =204
ORDER BY 1,3;
BEGIN
v_file_handle :=
UTL_FILE.FOPEN('/usr/tmp','MY_UTL_SUPP.csv', 'W');
UTL_FILE.PUT_LINE(v_file_handle,'VendorNmae'||'|'||
'Vendor#'||'|'||
'vendor_site_code'||'|'||
'Address1'||'|'||
'Address2'||'|'||
'City'||'|'||
'State'||'|'||
'County'||'|'||
'ZIP'
);
FOR c_rec IN c_supplier LOOP
UTL_FILE.PUT_LINE(v_file_handle,c_rec.vendor_name||'|'||
c_rec.Vendror#||'|'||
c_rec.vendor_site_code||'|'||
c_rec.address_line1||'|'||
c_rec.address_line2||'|'||
c_rec.CITY||'|'||
c_rec.STATE||'|'||
c_rec.country||'|'||
c_rec.zip
);
END LOOP;
UTL_FILE.PUT_LINE(v_file_handle,'END OF
Extraction');
UTL_FILE.FCLOSE(v_file_handle);
COMMIT;
/* Handle
UTL_FILE exceptions */
EXCEPTION
WHEN
UTL_FILE.INVALID_OPERATION THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid operation');
WHEN
UTL_FILE.INVALID_FILEHANDLE THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid filehandle');
WHEN UTL_FILE.INVALID_PATH THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid path');
WHEN
UTL_FILE.INTERNAL_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' internal error');
WHEN
UTL_FILE.INVALID_MODE THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid mode');
WHEN
UTL_FILE.WRITE_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' write error');
WHEN
UTL_FILE.READ_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' read error');
WHEN
OTHERS THEN
utl_file.fclose_all;
dbms_output.put_line('other exception');
END xxaj_utl_file;
Ur's
AmarAlam
0 comments:
Post a Comment