UTL_File package programs

Below are the UTL_FILE packages units.

Program Name	Either Procedure or Function	Use of it
FCLOSE	Procedure	Closes a file
FCLOSE_ALL	Procedure	Closes all open file handles
FCOPY	Procedure	Copies a contiguous portion of a file to a newly created file
FFLUSH	Procedure	Physically writes all pending output to a file
FGETATTR	Procedure	Reads and returns the attributes of a disk file
FGETPOS	Function	Returns the current relative offset position within a file, in bytes
FOPEN	Function	Opens a file for input or output
FOPEN_NCHAR	Function	Opens a file in Unicode for input or output
FREMOVE	Procedure	Deletes a disk file, assuming that you have sufficient privileges
FRENAME	Procedure	Renames an existing file to a new name, similar to the UNIX mv function
FSEEK	Procedure	Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE	Procedure	Reads text from an open file
GET_LINE_NCHAR	Procedure	Reads text in Unicode from an open file
GET_RAW	Procedure	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN	Function	Determines if a file handle refers to an open file
NEW_LINE	Procedure	Writes one or more operating system-specific line terminators to a file
PUT	Procedure	Writes a string to a file
PUT_LINE	Procedure	Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR	Procedure	Writes a Unicode line to a file
PUT_NCHAR	Procedure	Writes a Unicode string to a file
PUTF	Procedure	A PUT procedure with formatting
PUTF_NCHAR	Procedure	A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW	Procedure	Accepts as input a RAW data value and writes the value to the output buffer

Separating Values from One Column to Multi Columns Using INSTR

Let's say we got requirement as below.

String: 'Venky,Teja,Bangalore' --its one of the column value , now i want it to be shown as 3 different columns.

Col1     Col2     Col3
Venky   Teja      Bangalore


Lets take one eg:

create table trans( t_id integer, txn_desc varchar2(400));

SQL> insert into trans  values (1,'Amount,Piad_by,Cash');

1 row inserted

SQL> insert into trans  values (2,'Amount,Piad_by,Credit card');

1 row inserted

SQL> insert into trans  values (3,'Amount,Piad_by,Debit card');

1 row inserted

SQL> commit;

Commit complete

Output in Required Format

How to create text file with UTL_FILE

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;

We can find "UTL_FILE packages units" here.

I hope you understood basic concept to create text file using UTL_FILE,feel free to ask questions if you have any.Thanks.