Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 28 May 2016

UTL File


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’

Directories
  •  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.

Writing Data into file

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:                          




Share this article :

0 comments:

Post a Comment