Not sure why one has too open first without and then with a file size but it does work. Not a scenario I attempted. Tom, Thanks

Hi Tom:

following is a procedure, that I tried to use your free.sql script to monitor our pro. db tablespace usage, when I ran the procedure, I got errors:

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "ANL.TABLESPACE_USAGE", line 7
ORA-06512: at line 1


CREATE OR REPLACE PROCEDURE tablespace_usage
IS

file_handle utl_file.file_type;

BEGIN
file_handle :=
utl_file.fopen('/tmp','tabsusage.dat', 'W');
utl_file.fclose( file_handle);
file_handle := utl_file.fopen ('/tmp', 'tabsusage.dat', 'W', 30000);
utl_file.put_line ( file_handle, rpad( '*', 1024));

INSERT INTO space_usage
SELECT *
FROM ( SELECT TRUNC(sysdate),
NVL(b.tablespace_name,
NVL(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
NVL(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
NVL(largest,0) largest,
NVL(kbytes_max,kbytes_alloc) Max_Size,
DECODE( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
FROM ( SELECT SUM(bytes)/1024 Kbytes_free,
MAX(bytes)/1024 largest,
tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name ) a,
( SELECT SUM(bytes)/1024 Kbytes_alloc,
SUM(maxbytes)/1024 Kbytes_max,
tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
UNION all
SELECT SUM(bytes)/1024 Kbytes_alloc,
SUM(maxbytes)/1024 Kbytes_max,
tablespace_name
FROM sys.dba_temp_files
GROUP BY tablespace_name )b
WHERE a.tablespace_name (+) = b.tablespace_name);

FOR x IN (SELECT *
FROM space_usage
WHERE used > 80 )
LOOP
UTL_FILE.PUTF(file_handle, 'statement: was successful');
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
/

my space_usage table is :
desc space_usage
Name Null? Type
----------------------------------------- -------- ----------------------------
CRTDATE DATE
TABLESPACE_NAME VARCHAR2(18)
KBYTES NUMBER
USED NUMBER
FREETABS NUMBER
PERCENTUSED NUMBER
LARGEST NUMBER
MAX_SIZE NUMBER
PERCENTMAXUSED NUMBER

thank you as always,
an

Thank you, Tom.
here is parameter utl_file_dir set in our environment, but what means '*' here?

show parameter utl_file

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
utl_file_dir string *

an

>> exec utl_file.fclose_all

Tom, Can one just use "utl_file.fclose_all" right out and
no bother with closing the individual handles ?
What are the considerations ?

Thanks

I just remembered something on UTIL_FILE exception in your book which is not around me right now...
Re exception handling you mentioned that those exceptions are "user-defined" in the pkg and that you convert that to "your" exception in your code...and that you listed out those UTIL_FILE exceptions (in a file or something) and just
"....read it into a file...." ( hope I quoted you right)
--- when I read it, I was really curious...what exactly do you mean and how do you do that... I still am.
Thanks

>> exec utl_file.fclose_all
Followup:
>>>Imagine how irritated the other developers might get if >>>you closed their files!

Tom re your response above, you mean fclose_all will close
all file handles in OTHER sessions ? wow..that's bad.
The Package Reference certainly did not say this...
"This procedure closes all open file handles for the session. This should be used as an emergency cleanup procedure...".

It says nothing about WHY this is an emergency bug-out.

hello,

I would like to know if it is possible to check the existence of a file on the file system using UTL_FILE.

I have tried the following:
============================================================
$ sqlplus /@dev

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Aug 1 16:17:28 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> create or replace function exists_file( p_dir in varchar2, p_filename  in varchar2 ) return boolean
is
        l_input         utl_file.file_type;
begin
     l_input := utl_file.fopen( p_dir, p_filename, 'r', 4000 );
     exception
        when no_data_found then return FALSE;
     utl_file.fclose( l_input );
     return TRUE;
end;
/
 
Function created.

SQL> begin
  if exists_file('/home/yogeeraj/','.profile') then
   dbms_output.put_line('hello');
  end if;
end;
/
  2    3    4    5    6  begin
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 192
ORA-06512: at "YD.EXISTS_FILE", line 5
ORA-06512: at line 2

SQL> show parameter utl

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
utl_file_dir                         string  *
SQL> !ll /home/yogeeraj/.profile
-r--r--r--   1 yogeeraj   it            1719 Jan 16  2003 /home/yogeeraj/.profile

SQL>
============================================================

Please help.
thank you in advance for you reply.

best regards 

your exception handling is all wrong.

the only time you close a file is when utl_file throws "no data found" on the fopen call --- which it never ever would.

if the file existed -- you would have returned -- NOTHING

if no data found was thrown -- you would have then returned true (but probably not, since the close would have failed)



You need to read up on coding with exceptions and how they work a bit.  That and check out the docs to see what exceptions utl_file ACTUALLY throws.

these are the exceptions you could expect:

  /*
  ** Exceptions
  */
  file_open            EXCEPTION;
  charsetmismatch      EXCEPTION;
  invalid_path         EXCEPTION;
  invalid_mode         EXCEPTION;
  invalid_filehandle   EXCEPTION;
  invalid_operation    EXCEPTION;
  read_error           EXCEPTION;
  write_error          EXCEPTION;
  internal_error       EXCEPTION;
  invalid_maxlinesize  EXCEPTION;
  invalid_filename     EXCEPTION;
  access_denied        EXCEPTION;
  invalid_offset       EXCEPTION;
  delete_failed        EXCEPTION;
  rename_failed        EXCEPTION;

something like:

ops$tkyte@ORA920> create or replace function file_exists( p_path in varchar2, p_fname in varchar2 ) return varchar2
  2  as
  3          l_temp utl_file.file_type;
  4  begin
  5          l_temp := utl_file.fopen( p_path, p_fname, 'r' );
  6          utl_file.fclose( l_temp );
  7          return 'Y';
  8  exception
  9          when utl_file.invalid_path then return 'N';
 10          when utl_file.invalid_filename then return 'N';
 11          when utl_file.invalid_operation then return 'N';
 12  end;
 13  /

Function created.

ops$tkyte@ORA920> column e format a10
ops$tkyte@ORA920> select file_exists( '/tmp/', 'x.txt' ) e from dual;

E
----------
Y

ops$tkyte@ORA920> select file_exists( '/tmp/', 'y.txt' ) e from dual;

E
----------
N

ops$tkyte@ORA920>

would be more like it. 

hello,

Thanks a million.

I went through the Docs for Oracle 8i - Supplied PL/SQL Packages Reference and found the following exceptions:

Exception Name Description
=================== ==========================================================
INVALID_PATH File location or filename was invalid.
INVALID_MODE The open_mode parameter in FOPEN was invalid.
INVALID_FILEHANDLE File handle was invalid.
INVALID_OPERATION File could not be opened or operated on as requested.
READ_ERROR Operating system error occurred during the read operation.
WRITE_ERROR Operating system error occurred during the write operation.
INTERNAL_ERROR Unspecified PL/SQL error.

unlike in 9i which has more features:
Exception Name Description
=================== ==========================================================
INVALID_PATH File location is invalid.
INVALID_MODE The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE File handle is invalid.
INVALID_OPERATION File could not be opened or operated on as requested.
READ_ERROR Operating system error occurred during the read operation.
WRITE_ERROR Operating system error occurred during the write operation.
INTERNAL_ERROR Unspecified PL/SQL error
CHARSETMISMATCH A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN The requested operation failed because the file is open.
INVALID_MAXLINESIZE The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME The filename parameter is invalid.
ACCESS_DENIED Permission to access to the file location is denied.
INVALID_OFFSET The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; it should be greater than 0 and less than the total number of bytes in the file.
DELETE_FAILED The requested file delete operation failed.
RENAME_FAILED The requested file rename operation failed.

and modified "your" procedure accordingly.

It works fine now.

yd@MYDB.MU> column e format a10
yd@MYDB.MU> select file_exists( '/tmp/', 'x.txt' ) e from dual;

E
__________
N

Elapsed: 00:00:00.02
yd@MYDB.MU> yd@DEV.CMT.MU> !touch /tmp/x.txt

yd@MYDB.MU> /

E
__________
Y

Elapsed: 00:00:00.01
yd@MYDB.MU>

Thank you again for your helping hand..

best regards
Yogeeraj

Hi Tom,

How do you solve and exception of 'UTL_FILE.INVALID_FILEHANDLE'?

thanks

Hi Tom,

I have written down a procedure but needs to know where to catch the exceptions for the UTL_FILE package and trap them in the outputfile named XException.txt.

when utl_file.invalid_path then
raise_application_error(-20001,'INVALID_PATH: File location or filename was invalid.');
when utl_file.invalid_mode then
raise_application_error(-20002,'INVALID_MODE: The open_mode parameter in FOPEN was invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,'INVALID_OPERATION: The file could not be opened or operated on as requested.');
when utl_file.read_error then
raise_application_error(-20004,'READ_ERROR: An operating system error occurred during the read operation.');
when utl_file.write_error then
raise_application_error(-20005,'WRITE_ERROR: An operating system error occurred during the write operation.');
when utl_file.internal_error then
raise_application_error(-20006,'INTERNAL_ERROR: An unspecified error in PL/SQL.');

Please help me as I am new to UTL_FILE package!

CREATE OR REPLACE PROCEDURE PULL_DATA AS
begin
declare
Path VARCHAR2(100) ;
Filename VARCHAR2(100) := 'XException.txt';
err_num NUMBER;
err_msg VARCHAR2(500);
Output_file utl_file.file_type;
begin
select value into path from v$parameter where name = 'utl_file_dir';
output_file := utl_file.fopen (path,filename, 'a');

Some logic......
execute immediate String_merge;
end;
end;
/

Thanks

Hi Tom,

Yes, I know unless you have that fpoen working, you just cannot dump these exceptions in the file.

We are recording the exceptions in the error file, and based on any new line additions will get an e-mail to take corrective action. All these exceptions will get trapped which will be based on Oracle but just thinking how to catch utl_file exceptions itself.

Is there any other way you can suggest!