How to save Oracle Table data into a text file using UTL_FILE – for the new oracle developer or dba

you are a new oracle dba and you would like to know about a way to write oracle table data into text files.

In this Document
Goal
Solution

PL/SQL – Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.

How to save table data into a user-readable text file ( .txt extension)

CREATE or replace DIRECTORY test_dir1 AS 'C:\Home';

set serverout on;

CREATE OR REPLACE PROCEDURE WRITEFILE as
fHandle UTL_FILE.FILE_TYPE;
Vename VARCHAR2(100); 
VempNo VARCHAR2(100);
error VARCHAR2(100);
CURSOR C1 IS
SELECT ENAME, EMPNO
FROM EMP;
BEGIN
fHandle := UTL_FILE.FOPEN('TEST_DIR1','test.txt', 'W');
OPEN C1;
LOOP
FETCH C1 INTO Vename, VempNo;
EXIT WHEN C1%NOTFOUND;
UTL_FILE.PUTF(fHandle,Vename);
UTL_FILE.PUTF(fHandle,' ');
UTL_FILE.PUTF(fHandle,VempNo);
UTL_FILE.PUTF(fHandle,'\n');
DBMS_OUTPUT.PUT_LINE(Vename);
DBMS_OUTPUT.PUT_LINE(Vempno);
END LOOP;
UTL_FILE.FCLOSE(fHandle);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
WHEN OTHERS THEN
error:=SQLCODE;
DBMS_OUTPUT.PUT_LINE('other stuff'||error);
END;
/