Writing to a file using Oracle PL/SQL : UTL_FILE

We can choose the file name, directory path and file contents. Before writing to target server directory, make sure that directory exists on server and oracle user has write permission it. In case of invalid directory path or some permission issue, error like below will occur:

"ORA-29283: invalid file operation 
ORA-06512: at "SYS.UTL_FILE", line.... "

First create a directory object in which the file will be created. This object will just serve as a reference to actual directory path on server.
But make sure that directory exist on the server with appropriate permission.

CREATE DIRECTORY MY_DIR AS '/home/user12/data' ;

So we have directory now, lets write to a file. Below example shows how to create and write to a file in this directory using UTL_FILE PL/SQL package:

DECLARE
--create a file pointer type variable
v_os_touch_file		UTL_FILE.FILE_TYPE;
in_file			VARCHAR2(50) := 'My_file_Super.txt';

CURSOR get_data IS
  SELECT empno,ename from EMPLOYEES ORDER BY empno;
  
BEGIN
--associate file pointer variable to a file and open it for write operation
v_os_touch_file	:= UTL_FILE.FOPEN('MY_DIR', in_file, 'w');

--write to the file
FOR i IN get_data LOOP
	UTL_FILE.PUT_LINE(v_os_touch_file, 'Emp id: '||i.empno|| 
					', Emp name: '||i.ename);
END LOOP;

--close the file pointer
UTL_FILE.FCLOSE(v_os_touch_file);
	
dbms_output.put_line('S U C C E S S ! !   Check file content now.');

EXCEPTION 
WHEN others THEN
	DBMS_OUTPUT.PUT_LINE('Its error. Try again :)) ');
	DBMS_OUTPUT.PUT_LINE('ERROR :' || SUBSTR(SQLERRM,1,250));
	DBMS_OUTPUT.PUT_LINE('ERROR :' || SQLCODE);
END ;