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.