For writing to a file using PL/SQL, we use Oracle PL/SQL package called UTL_FILE. The file is created in a directory present on the server/system where Oracle software is installed.
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 ;