UTL_FILE | Oracle DBA Tutorial pdf

UTL_FILE

A sample procedure to unload a table data to a text file using UTL_FILE package is shown below.
Set UTL_FILE_DIR = ‘E:\ORACLE\ORADATA\UNLOAD’ (or path where from you need to read/write) in initialization file.
CREATE OR REPLACE PROCEDURE UNLOAD_TABLE (piTable IN VARCHAR2)
AS
CURSOR cTable IS
SELECT * FROM category;
f utl_file.file_type;
buf VARCHAR2(200);
BEGIN
f:=UTL_FILE.FOPEN('E:\ORACLE\ORADATA\UNLOAD','CATEGORY.TXT','w
');
FOR i IN cTable LOOP
buf:=i.CategoryNo || '|' || i.Description || '|' ||
i.ParentCategoryNo;
UTL_FILE.PUT_LINE(f,buf);
END LOOP;
UTL_FILE.FCLOSE(f);
DBMS_OUTPUT.PUT_LINE('TABLE UNLOADED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
Similarly, GET_LINE is used to read data from file.