HOME

Writing to a file using Oracle PL/SQL : UTL_FILE

Contact Me

For writing to a file using PL/SQL, we use Oracle PL/SQL package called UTL_FILE.
Its important to note that the file will be created in a directory present on the server/system where Oracle software is installed.

We need to provide 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 directory 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' ;

After we have directory object in place, lets write to a file.
Below example shows how to create and write to a file in above directory using UTL_FILE PL/SQL package.
Here we write EMPLOYEES table data to the file via a cursor FOR loop.

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 ;


If this runs successfully, go to server directory and verify the file.



Thanks...

TOP