Utl File Package wv1t

UTL_FILE Package: ================ UTL_FILE is PL/SQL Package that can be used to write the data from tables to files and  files to tables. Generally we will use UTL_FILE package for writing data from tables to files. Important queries to check the details about UTL_FILE Package. SELECT   owner, object_type, status   FROM   all_objects  WHERE   object_name = 'UTL_FILE';

Note: If the package status is invalid, recompile the package.      If the package is not listed, run {ORACLE_HOME}/rdbms//utlfile.sql to create it.  SELECT   grantee, privilege   FROM   all_tab_privs  WHERE   table_name = 'UTL_FILE';

Note: Always grantee should be  logged in or public, If this privilege is missing, then log in as an   and  Run GRANT EXECUTE on UTL_FILE

UTL_FILE_DIR: =========== To know Available Directory Path Details: Select * from v$parameter where name = 'utl_file_dir';

For Creation: CREATE OR REPLACE DIRECTORY TEST_DIR AS ' /usr/tmp';

Grant a access to the directory: GRANT read, write ON DIRECTORY TEST_DIR TO APPS;

To know Directory Path Details: SELECT   directory_name, directory_path   FROM   all_directories  WHERE   DIRECTORY_NAME = 'TEST_DIR';

To know Directory Path Priviliges: SELECT   grantee, privilege   FROM   all_tab_privs  WHERE   table_name = 'TEST_DIR'; Note:  UTL_FILE uses Oracle directories, not OS directories like ­ D:\ Instead create oracle directory with reference to an OS Directory like 

Create or replace directory utl_file_dir as 'D:\’; We will use following three functions to generate the file.

1) Utl_File.fopen = to open (or) Create the file   (We can open maximum 50 files at one  session)

2) Utl_File.Put_line = To Transfer the data into the File. 3) Utl_File.fclose = to close the File after Data transfer.

1. Utl_File.fopen: Opens a file for input or output with the default line size. FOPEN returns a file handle, which must be used in all subsequent I/O operations on the  file.

Syntax: UTL_FILE.FOPEN  (     Location IN VARCHAR2,     Filename IN VARCHAR2,     Open_mode IN VARCHAR2     Max_linesize IN BINARY_INTEGER )  RETURN UTL_FILE.FILE_TYPE;

Parameters: Location ­­­ > Directory Path or Oracle Directory Name. Note:  The file location must be an accessible directory, as defined in the instance's  initialization parameter UTL_FILE_DIR. Filename ­­­ > Name of the file with extension (File_type). Open_mode ­­ > Specifies how file is to be opened. (Not a Case sensitive)                ‘R’ ­­ > Reads Text (get_line)                ‘W’ ­­ > Writes Text (put, put_line, putf, fflush)                ‘A’ ­­ > Append Text (put, put_line, putf, fflush, new_line)                ‘RB’ ­­ > Read Binary                ‘WB’ ­­ > Write Binary                ‘AB’  Append Binary     

Note:  If you open a file using open_mode=’A’ but the file not existed then it will open  (Created) in ‘w’ automatically. Max_linesize ­­ > minimum value 1, maximum value 32767 (We can  null value also)

Exceptions: INVALID_PATH INVALID_MODE INVALID_OPERATION INVALID_MAXLINESIZE

: : : :

File location or name was invalid. The open_mode string was invalid. File could not be opened as requested. Specified max_linesize is too large or too small.

2 Utl_File.Put_line: It is used to write the data stored in buffer parameter to the open file identified by the file handler. The file must be open for write operations. PUT_LINE terminates the line with the platform-specific line terminator.

Syntax: UTL_FILE.PUT_LINE ( File IN FILE_TYPE, Buffer IN VARCHAR2 );

Parameters: File ­­ > Active file Handle returned by an UTL_FILE.FOPEN  Buffer ­­ > Text Buffer that contains the lines to be write to the file. Exceptions: INVALID_FILEHANDLE INVALID_OPERATION

: File could not be opened as requested. : File handle was invalid.

WRITE_ERROR

: Operating system error occurred during the write operation.

3 Utl_File.fclose: It closes an open file identified by a file handle. Syntax: UTL_FILE.FCLOSE ( File IN OUT FILE_TYPE );

Parameters: File ­­ > Active file Handle returned by an UTL_FILE.FOPEN

Exceptions: WRITE_ERROR INVALID_FILEHANDLE

: Operating system error occurred during the write operation. : File could not be opened as requested.

Note: If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

UTL_FILE.IS_OPEN Syntax: UTL_FILE.IS_OPEN ( File IN FILE_TYPE ) RETURN BOOLEAN;

Parameters: File ­­ > Active file Handle returned by an UTL_FILE.FOPEN. Returns: True or False.

UTL_FILE.FCLOSE_ALL It closes all open file handles for the session.

Syntax: UTL_FILE.FCLOSE_ALL;

Exceptions: WRITE_ERROR

Note:

: Operating system error occurred during the write operation.

FCLOSE_ALL does not alter the state of the open file handles held by the . This means that an IS_OPEN test on a file handle after an FCLOSE_ALL call still returns TRUE, even though the file has been closed. No further read or write operations can be performed on a file that was open before an FCLOSE_ALL.

UTL_FILE.GET_LINE Syntax: UTL_FILE.GET_LINE (

File Buffer

IN FILE_TYPE, OUT VARCHAR2

);

Parameters: File ­­ > Active file Handle returned by an UTL_FILE.FOPEN  Buffer ­­ > Text Buffer to receive the lines read from the file. Note: File Must be opened in open_mode=’R’

Exceptions: INVALID_OPERATION INVALID_FILEHANDLE READ_ERROR NO_DATA_FOUND VALUE_ERROR

: File could not be opened as requested. : File could not be opened as requested. : Operating system error occurred during the read operation.

Other Functions: UTL_FILE.PUT

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. No line terminator is appended by PUT; use NEW_LINE to terminate the line or Use PUT_LINE to write a complete line with a line terminator. PUT

Syntax: UTL_FILE.PUT ( File IN FILE_TYPE, Buffer IN VARCHAR2 );

Exceptions: INVALID_FILEHANDLE INVALID_OPERATION

: File could not be opened as requested. : File handle was invalid.

WRITE_ERROR

: Operating system error occurred during the write operation.

UTL_FILE.NEW_LINE Syntax: UTL_FILE.NEW_LINE ( File IN FILE_TYPE, Lines IN NATURAL: = 1 );

Parameters:

File ­­ > Active file Handle returned by an UTL_FILE.FOPEN  Lines ­­ > Number of line terminators to be written to the file. Exceptions: INVALID_FILEHANDLE INVALID_OPERATION

: File could not be opened as requested. : File handle was invalid.

WRITE_ERROR

: Operating system error occurred during the write operation.

****

UTL_FILE.PUT (v_empfile_tgt, v_empline); UTL_FILE.NEW_LINE (v_empfile_tgt, 2);