Oracle supplied packages -UTL_FILE Package
UTL_FILE package is used to write data to operating system files such as text files, word files, excel files, etc.
The data from the oracle table is transferred to the o.s. files. So we can generate a report of Oracle data in a non-oracle format.Members of UTL_FILE Package are as follows –
FOPEN function
This function opens a file for input or output. The file location must be an accessible directory, as defined in the instance's initialization parameter UTL_FILE_DIR. The complete directory path must already exist; it is not created by FOPEN.
It takes 3 parameters
1. Directory name à The path
2. File name (.txt, .doc,.xls,)
3. Open_Mode à
a. ‘r’ – Read Text
b. ‘w’-- Write Text
c. ‘a’ – Append Text
PUTF procedure
This procedure is a formatted PUT procedure. It works like a limited printf(). The format string can contain any text, but the character sequences '%s' and '\n' have special meaning.
%s
Substitute this sequence with the string value of the next argument in the argument list.
\n
Substitute with the appropriate platform-specific line terminator.
FCLOSE procedure
This procedure closes an open file identified by a file handle.
NEW_LINE procedure (Can specify how many blank lines are required. Default is 1)
This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.
Syntax
UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN NATURAL := 3);
Example of generating a text file of salary status from the emp table.
create or replace procedure sal_status
(p_filedir in varchar2, p_filename in varchar2)
is
v_filehandle UTL_FILE.FILE_TYPE;
Cursor emp_info is
select ename,sal,deptno
from emp
order by sal desc;
Begin
v_filehandle := UTL_FILE.FOPEN(p_filedir,p_filename,'w');
UTL_FILE.PUTF(v_filehandle,'SALARY REPORT : GENERATED ON %s\n',SYSDATE);
UTL_FILE.NEW_LINE(v_filehandle);
for v_emp_rec in emp_info
LOOP
UTL_FILE.PUTF(v_filehandle,'DEPARTMENT: %s \n', v_emp_rec.deptno);
UTL_FILE.PUTF(v_filehandle,' Employee:%s earns: %s\n', v_emp_rec.ename,v_emp_rec.sal);
END LOOP;
UTL_FILE.PUTF(v_filehandle, '*** END OF REPORT ***');
UTL_FILE.FCLOSE(v_filehandle);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
raise_application_error(-20001,'Invalid File');
WHEN UTL_FILE.WRITE_ERROR THEN
raise_application_error(-20002,'Unable to write file');
End;
Steps for executing the procedure are as follows –
1. In the init.ora file of the database, the file location has to be defined as a value of UTL_FILE_DIR
Open the init.ora file and add the property as follows-
UTL_FILE_DIR = C:\ABC
(Step 1 is only required for early version. If directory object is not created then UTL_FILE_DIR path is required)
2. Create a folder ABC in C drive.
3. Create a directory object in database as –
create directory D1 as 'C:\ABC'
Directory is a nonschema database object that provides a way for administering the o.s. files through Oracle Server 9i.
A Directory specifies an alias for the actual o.s. path .
4. Execute the procedure
Exec Sal_Status('D1','a1.txt'); -- a1.txt is the new text file getting generated.
Query the following dictionary views to get information on directories in the database
ALL_DIRECTORIES - all directories accessible to the database user