Re: UTL_FILE_DIR

Ari,

         I believe there is a buffer size limitation with UTL_FILE. I believe it is 1K (or 32K, I'm not sure), at least in Oracle 8.0.5 and lower. Maybe that's changed in 8i.

         Other then that, I agree with Ari that's UTL_FILE is a great tool, compared to whatever else Oracle has provided perviously or is providing currently, for debugging and such.

Just my two cents.
Lyall Barbour

At 12:30 PM 8/14/00 -0800, you wrote:
>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
> > also send the HELP command for other information (like subscribing).
> >
>
>--
>Author: Ari D Kaplan
> INET: akaplan_at_interaccess.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 - 15:03:58 CDT