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.
Data Flow
Init.Ora
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)
Or
utl_file_dir = c:\temp
utl_file_dir = c:\temp2
V$parameter
The
following sql statement provides the utl_file directory path.
Select
value
from
v$parameter
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
Example
·
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)
RETURN
file_type
·
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.
UTL_FILE Exceptions
NO_DATA_FOUND
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.
UTL_FILE.INTERNAL_ERROR
An
internal error occurred. The requested operation was not completed.
UTL_FILE.INVALID_FILE_HANDLE
The specified file handle does not
identify a valid, open file. This exception may be raised by calls to FCLOSE
and FFLUSH.
UTL_FILE.INVALID_MODE
The mode supplied to FOPEN is not valid.
Valid modes are: `a', `r', or `w' (upper or lower case is acceptable).
UTL_FILE.INVALID_OPERATION
In FOPEN, this exception is raised when
the file cannot be opened as requested.
UTL_FILE.INVALID_PATH
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.
UTL_FILE.READ_ERROR
An operating system-specific error occurred
when you tried to read from the file. For example, there might be a disk error.
UTL_FILE.WRITE_ERROR
An operating system-specific error
occurred when you tried to write to the file. For example, the disk might be
full.
VALUE_ERROR
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
Advantages
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
Limitations
Maximum
line size is 32767.
Can’t
randomly access lines in a file.
Can’t
change the file security
Practice Examples:
No comments:
Post a Comment