UTL_FILE is a Pl/sql package used to read and write the text files from server.
Text Files – UTL_FILE can only read and create clear text files. Specifically, it cannot be used to read or create binary files. Special characters contained within arbitrary binary data will cause UTL_FILE to do the wrong thing.
File System of the Server – UTL_FILE can only read and write to the file system of the database server. It cannot read or write to the file system the client is exe cuting on if that client is not logged onto the server itself.
UTL_FILE implements this security by limiting access to files that reside in one of the directories specified in the init.ora file (parameter initialization file) for the database instance on which UTL_FILE is running.
The UTL_FILE_DIR init.ora parameter takes one of two forms.
utl_file_dir = (c:\temp,c:\temp2)
utl_file_dir = c:\temp
utl_file_dir = c:\temp2
The following sql statement provides the utl_file directory path.
where name = ‘utl_file_dir’
- From oracle 9i Using directories, it is possible to grant directories to some users according to their needs. Thus, the security risk is smaller.
- Each directory to be accessed by UTL_FILE can be specified via the CREATE DIRECTORY command. And specified via this level of indirection in UTL_FILE.FOPEN.
- By default UTL_FILE will have no file access because it will have no directory access, until granted access by CREATE DIRECTORY by SYS or SYSTEM or a user with DBA privileges. Since access privileges are granted on a per directory basis, the DBA can control directory access by either.
- Creating separate directories for users with differing access requirements
- use operating system utilities and features for controling read and write access by users
· Connect to sys schema
create or replace directory ee_dir as '/tmp';
· Provide permission from sys to apps
Grant read, write on directory ee_dir to apps;
UTL_FILE Sub Programs
- fileUTL_FILE.FOPEN ( location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
· This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously
- UTL_FILE.FCLOSE ( file IN OUT FILE_TYPE) This procedure close an open
- UTL_FILE.PUT( file IN FILE_TYPE, buffer IN VARCHAR2)
· writes the text string stored in the buffer parameter to the open file identified by the file handle
- UTL_FILE.PUT_LINE ( file IN FILE_TYPE,
o buffer IN VARCHAR2,
o autoflush IN BOOLEAN DEFAULT FALSE)
· This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations.
- UTL_FILE.GET_LINE ( file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN PLS_INTEGER DEFAULT NULL
· This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter.
- UTL_FILE.IS_OPEN ( file IN FILE_TYPE) RETURN BOOLEAN
· This function tests a file handle to see if it identifies an open file. IS_OPEN reports only whether a file handle represents a file that has been opened, but not yet closed.
The GET_LINE procedure tried to read past the end of the file. Remember that this same exception is also raised by implicit cursors and references to PL/SQL tables.
An internal error occurred. The requested operation was not completed.
The specified file handle does not identify a valid, open file. This exception may be raised by calls to FCLOSE and FFLUSH.
The mode supplied to FOPEN is not valid. Valid modes are: `a', `r', or `w' (upper or lower case is acceptable).
In FOPEN, this exception is raised when the file cannot be opened as requested.
The path name supplied in a call to FOPEN is not valid. This error occurs when the location is not accessible or the path name is improperly constructed.
An operating system-specific error occurred when you tried to read from the file. For example, there might be a disk error.
An operating system-specific error occurred when you tried to write to the file. For example, the disk might be full.
The text read by GET_LINE is too long to fit in the specified buffer.
Declare a file handle.
Open the file with a call to FOPEN, which returns a file handle to the file. You can open a file to read, replace, or append text.
Write data to the file using the PUT or PUT_LINE procedures.
Close the file with a call to FCLOSE. This releases resources associated with the file.
Reading data from file
Declare a file handle.
Declare a VARCHAR2 string buffer that will receive the line of data from the file. Open the file using FOPEN in read mode.
Use the GET_LINE procedure to read data from the file and into the buffer. To read all the lines from a file, you would execute GET_LINE in a loop.
Close the file with a call to FCLOSE
High integration with pl/sql
Can read and write data from file
Can read multiple files in a single load session.
Can perform conditional translation
Maximum line size is 32767.
Can’t randomly access lines in a file.Can’t change the file security