How to create text file with UTL_FILE

1 min read

To create text file,as a developer we use “UTL_FILE” concept in oracle.

Lets get complete details on this topic.

What’s UTL_FILE:- Its a Built in package ,which is used to create text file in server or read data from text file at OS level.

To create/read text file at OS level , User should have privileges to access that particular directory.

So for our eg: let’s create Directory to create text file in that location.

“`
SQl>CREATE OR REPLACE DIRECTORY UTL_DIR AS ‘/tmp/Utl_Dir’ ;
“`

If you don’t have permissions to create directory then ask your DBA to create and provide full permissions(READ,WRITE,EXECUTE) on it.

Now let’s say you may wanted to write emp table data to text file as below:

Note:
File format will be plain text with pipe delimiter between each field. There will not be any fixed length for fields in text file.

And say if you get requirement for filename as “EMP_REP_DD-MM-YYYY_SEQ-NUMBER(4 digits).

“`
SQL> CREATE SEQUENCE EMP_SEQ START WITH 1 MAXVALUE 100 NOCACHE;
“`
We create above sequence to get sequential values for file name as per client requirement.

Create one procedure as below to create file.

“`

CREATE OR REPLACE PROCEDURE Emp_Utl_File IS
FILE_HANDLE UTL_FILE.file_type;
V_DIR ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := ‘UTL_DIR’;
V_DIRECTORY_PATH ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
V_filename VARCHAR2(100);
V_SQL VARCHAR2(32767);
BEGIN
SELECT DIRECTORY_PATH
INTO V_DIRECTORY_PATH
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = V_DIR;

DBMS_OUTPUT.PUT_LINE(‘Dir path ‘ || V_DIRECTORY_PATH);

V_filename := ‘EMP_REP’ || ‘_’ || TO_CHAR(SYSDATE, ‘DD-MM-YYYY’) || ‘_’ ||
LPAD(EMP_SEQ.NEXTVAL, 4, 0);

DBMS_OUTPUT.PUT_LINE(‘File name is ‘ || V_filename );
–Create/Open file in READ mode
FILE_HANDLE := UTL_FILE.fopen(location => V_DIRECTORY_PATH,
filename => V_filename,
open_mode => ‘W’,
max_linesize => 32767);
— max_linesize => 32767–Don’t ignore this parameter if you need more amount of data for each line,Because Default value is 1024 (i.e) it means You are telling oracle that I am not expecting more than 1024 characters in a line So use it for maximum size allowed ie. 32767.

FOR K IN (SELECT * FROM EMP) LOOP
–Using loop,writing one by one line to text file
V_SQL := K.EMPNO || ‘|’ || K.ENAME || ‘|’ || K.SAL || ‘|’ || K.DEPTNO;

UTL_FILE.put_line(file => FILE_HANDLE, buffer => V_SQL);

END LOOP;

IF UTL_FILE.is_open(file => FILE_HANDLE) THEN
UTL_FILE.fclose(FILE_HANDLE);
END IF;

EXCEPTION
–Handling Multiple exceptions which are related to UTL_FILE
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20051, ‘Invalid Path’);

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20052, ‘Invalid Mode’);

WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20053, ‘Internal Error’);

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20054, ‘Invalid Operation’);

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20055, ‘Invalid Operation’);

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE_ALL;
RAISE_APPLICATION_ERROR(-20056, ‘Invalid Operation’);
END;

Execute:

BEGIN
Emp_Utl_File;
END;

“`

Oracle Password Expired

dadin
17 sec read

Generate Insert Script

dadin
2 min read

Job not Running

dadin
18 sec read