Monday, October 23, 2017

UTL_FILE In Oracle

UTL_FILE is a built-in package from Oracle that allows us to read or write data to Operating System Files that are accessible from the Server using PL/SQL code.

UTL_FILE Programs:-



UTL_FILE.FILE_TYPE is actually a PL/SQL record whose fields contain all the information about the file needed by UTL_FILE. However, this information is for use only by the UTL_FILE package.

When you open a file, PL/SQL returns a handle to that file for use within your program. This handle has a datatype of UTL_FILE.FILE_TYPE.
ex:
l_file_handle UTL_FILE.FILE_TYPE;


UTL_FILE.FOPEN:- The FOPEN function opens the specified file and returns a file handle that you can then use to manipulate the file. We can't open the file in all the places, we need permission.

-- Open file.
  l_file_handle := UTL_FILE.fopen(l_location--->Location
                          ,l_filename--->File Name
                          ,'r'       --->open_mode
                          ,32767     --->max_linesize
                          );


We can use below query to find valid directories which are accessible through UTL_FILE through below query.

SELECT *
    FROM v$parameter
  WHERE name LIKE '%utl_file%';

You can open the file in one of three modes:

R :-    Open the file read-only. If you use this mode, use UTL_FILE's GET_LINE procedure to read from the file.
W :-    Open the file to read and write in replace mode. When you open in replace mode, all existing lines in the file are removed.
A :-    Open the file to read and write in append mode. When you open in append mode, all existing lines in the file are kept intact. New lines will be appended after the last line in the file.        

UTL_FILE.PUT_LINE:-    Puts a string into a file(Transfer the data into file), followed by a platform-specific line termination character.

UTL_FILE. FCLOSE :-    Use FCLOSE to close an open file.

Example:-
DECLARE
 l_file_handle utl_file.file_type;
BEGIN
    l_file_handle:= utl_file.fopen('c:/tmp','hellow.txt','w');
    utl_file.put_line(l_file_handle,'Welcome to Oracle Applications' );
    utl_file.put_line(l_file_handle,'Hello World..' );
    utl_file.put_line(l_file_handle,'Bye..Bye..' );
    utl_file.fclose(l_file_handle);
END;
/

UTL_FILE Exceptions:-
 ------------------------------
INVALID_PATH:-    The file location or the filename is invalid. Perhaps the directory is not listed as a utl_file_dir parameter in the INIT.ORA file, or you are trying to read a file and it does not exist.

INVALID_MODE:-    The value you provided for the open_mode parameter in UTL_FILE.FOPEN was invalid. It must be "A," "R," or "W."

INVALID_FILEHANDLE:-    The file handle you passed to a UTL_FILE program was invalid. You must call UTL_FILE.FOPEN to obtain a valid file handle.

INVALID_OPERATION:-    UTL_FILE could not open or operate on the file as requested. For example, if you try to write to a read-only file, you will raise this exception.

READ_ERROR:-    The operating system returned an error when you tried to read from the file.

WRITE_ERROR:-    The operating system returned an error when you tried to write to the file.

INTERNAL_ERROR:-    Unspecified Error when Something went wrong.

NO_DATA_FOUND:-    Raised when end of the file is reached.

VALUE_ERROR:-    Raised when you try to read or write lines in the file which are too long. UTL_FILE limits the size of a line read by UTL_FILE.GET_LINE to 1022 bytes.

INVALID_MAXLINESIZE:-    Raised when you try to open a file with a maximum linesize outside of the valid range (between 1 through 32767).

Limitations:-
  • We can't delete files, 
  • change privileges, 
  • copy a file, 
  • obtain the contents of a directory 
  • set a path

No comments:

Post a Comment

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)