Re: UTL_FILE

From: Ari D Kaplan <akaplan_at_interaccess.com>
Date: Mon, 14 Aug 2000 14:18:52 -0500 (CDT)
Message-Id: <10589.114531@fatcity.com>

Arun,

Here is a some information on UTL_FILE_DIR that I wrote awhile back. Hope it helps you get yours working. There are some good examples on syntax, which is probably what is causing you problems.

-Ari Kaplan
www.arikaplan.com

Many Oracle developers are aware of DBMS_OUTPUT but are unaware that UTL_FILE is usually a better option. It is just about as simple to use as DBMS_OUTPUT. Below are the advantages of using UTL_FILE over DBMS_OUTPUT: 1) Tests show that it is over 30% faster than DBMS_OUTPUT. This will be critical when spooling large amounts of data to flat files - for reports, MVS backfills, and so on.
2) With DBMS_OUTPUT, you cannot see the progress of the PL/SQL code until it has completely finished and the output buffer is flushed. With UTL_FILE you can view the progress of PL/SQL by looking at the files being generated as it is occurring. This helps with debugging and tracking completion time.
3) No need to "SET SERVEROUTPUT ON SIZE xxx" as with DBMS_OUTPUT 4) You can output data to multiple files in one PL/SQL routine. DBMS_OUTPUT can only return information to your screen (or spooled file). 5) DBMS_OUTPUT has a limit to the size of the buffer. Once the limit has been reached, the PL/SQL routine will stop with an error. There are two limitations with UTL_FILE: 1) The files are generated on the server where the database resides. This is where reports and backfills should go, but if you use SQL*Plus on your PC then the file will not get generated on your PC (as it would with DBMS_OUTPUT).
2) You can only work with files that are in the directories defined in the Oracle init.ora file. The directories are specified with "UTL_FILE_DIR=". To use UTL_FILE in PL/SQL, you issue "UTL_FILE.PUT_LINE" the same way you would with "DBMS_OUTPUT.PUT_LINE", except that you provide a pointer to the file that you wish to write to. This way Oracle can write to multiple files within a single PL/SQL routine.
There are many procedures and functions with UTL_FILE (all described at the end of this document). The basic commands of UTL_FILE are: * FOPEN Opens a file. The modes are 'a' append text, 'r' read text, 'w' write text

* PUT_LINE	Writes a line to the file
* FCLOSE	Closes the file

First define a file handle (the path and filename) that you can reference by UTL_FILE later in the PL/SQL routine. An example will be shown below. The following PL/SQL example will query the QUOTE table and use UTL_FILE to put 10,000 records into the "quote.txt" tab-delimited flat file: DECLARE

v_IS_A_SERIES	NUMBER;
v_line			VARCHAR2(2000);
v_CURSOR		QUOTE%ROWTYPE;
utl_file_handle		UTL_FILE.FILE_TYPE;
utl_path		VARCHAR2(255) := '/u01/utldir';
utl_file_name		VARCHAR2(255) := 'quote.txt';
utl_err_status		NUMBER := 0;

Define the cursor for the first 10000 records of the CUST table cursor C_PLSQL is select CUST_CODE, HIRE_DATE, CUST_SYM , DEPT_CODE, SALARY from EMP where ROWNUM < 10001;

BEGIN
-- Use UTL_FILE.FOPEN to open the file in 'A'ppend mode and assign to --"utl_file_handle":
utl_file_handle := UTL_FILE.FOPEN(utl_path, utl_file_name, 'a');

OPEN C_PLSQL;
-- Loop through the records one at a time LOOP
FETCH C_PLSQL INTO v_cursor;

CHR(9) is the ASCII representation of a tab v_line :=v_cursor.CUST_CODE||chr(9)||v_cursor.HIRE_DATE||chr(9)|| v_cursor.CUST_SYM ||chr(9)||v_cursor.DEPT_CODE||chr(9)|| v_cursor.SALARY
Use UTL_FILE.PUT_LINE to append the record (v_line) to the file: UTL_FILE.PUT_LINE(utl_file_handle,v_line);

EXIT WHEN (C_PLSQL%NOTFOUND);
END LOOP;
-- Use UTL_FILE.FCLOSE to close the file: UTL_FILE.FCLOSE(utl_file_handle);
END;
/

On Mon, 14 Aug 2000, ARUN K C wrote:

> Hello everyone,
> Can somebody please let me know what I should do so that users can create
> and use files from the operating systems.
> I added the utl_file_dir=/path/ but this does not seem to be working.
> I even ran the utlfile.sql which creates the package and granted the
> execute permission to the user but still it is not creating the file on the
> operating system.
> the dir which is on the operating system has read write permission.
> Can somebody please let me know where I have gone wrong or what else I
> should do for this to work
> Thanks in Advance
> Arun
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: ARUN K C
> INET: arun_k_c_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Aug 14 2000 - 14:18:52 CDT

Re: UTL_FILE_DIR