Create .csv or .txt file on usr/tmp/ path by UTL_FILE package

--UTL_FILE Example Using Cursor
--Create .csv or .txt file on usr/tmp/ path by UTL_FILE package




--select * from v$parameter where name like 'utl%'  --Get utl directory path from



CREATE OR REPLACE PROCEDURE xx_utl_txt_file_crt_p
(           p_errbuf    OUT   VARCHAR2,
      p_retcode   OUT   NUMBER ) IS
v_file_handle    UTL_FILE.FILE_TYPE;

CURSOR c_persons IS

select papf.person_id,papf.EMPLOYEE_NUMBER,papf.FULL_NAME from per_all_people_f papf
where rownum <100
ORDER BY 1;
   
BEGIN

v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_PERSONS.txt', 'W');--txt
--v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_PERSONS.csv', 'W');--CSV
UTL_FILE.PUT_LINE(v_file_handle,'PersonID'||'|'||
                                'EMP_NO'||'|'||
                            'EMP_Full_NAME' --column names
                            );
FOR c_rec IN c_persons LOOP
  UTL_FILE.PUT_LINE(v_file_handle,c_rec.person_id||'|'||
                                  c_rec.EMPLOYEE_NUMBER||'|'||
                              c_rec.FULL_NAME
                                  );
END LOOP;
UTL_FILE.PUT_LINE(v_file_handle,'END OF Extraction');
UTL_FILE.FCLOSE(v_file_handle);
COMMIT;
/*  Handle UTL_FILE exceptions Starts */
        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');
/*  Handle UTL_FILE exceptions Ends */
END xx_utl_txt_file_crt_p;


--- **The file will create on database tire.