Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: UTL_FILE problems
In article <jia25.51689$5k2.106919_at_dfw-read.news.verio.net>,
"Scott Buyer" <scottb_at_verio.net> wrote:
> I cannot get the fopen function of this package to run. I added the
> directory I am accessing to the control file, I create the variable
of type
> UTL_FILE.FILE_TYPE;
>
> Here is my code in the proc
>
> CREATE OR REPLACE PROCEDURE XACCT.SUMMARIZE_USAGE_DETAIL_2_DED
(V_STR_DATE
> CHAR)
> IS
> -- UTL file declaration
> d_exception_file1 UTL_FILE.FILE_TYPE;
> d_exception_file2 UTL_FILE.FILE_TYPE;
> d_directory1 varchar2(50) := '/apps/xacct/Reports/';
> d_directory2 varchar2(50) := '/apps/xacct/Reports/';
> v_test varchar2(50) := 'test1.txt';
> v_test2 varchar2(50) := 'test2.txt';
> BEGIN
> --CREATE DATE VARIABLE WHICH HAS TIME A SET TO 00:00:00
> begin
> d_exception_file1 :=
> UTL_FILE.FOPEN('/apps/xacct/Reports/','test1.txt','A');
> exception
> when others then
> dbms_output.put_line('1 errored out with ' || SQLERRM);
> end;
> begin
> d_exception_file2 := UTL_FILE.FOPEN(d_directory2,v_test2,'A');
>
> The open fails.
>
> Any thoughts out there.
>
>
did you restart the instance after setting the init.ora parameter utl_file_dir?
database version? Is it v7.3. There is a bug whereby the append mode does not create the file. The workaround is to code:
begin
file_stat := utl_file.fopen(file_dir,file_name,'a'); exception
It is fixed in version 8.0.3. The above is functionally equivalent to the way it does work in 8.0 and up. For ease of use, you would probably put the above into a small subroutine somewhere and replace it with just
file_stat := utl_file.fopen(file_dir,file_name,'a');
when you goto v8 or up.
Also, try a block like:
declare
l_input utl_file.file_type;
begin
l_input := utl_file.fopen( '/tmp/x', 'foo.dat', 'a' );
utl_file.fclose( l_input );
exception
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.');
end;
/
and use that exception handler to see what the real exception is...
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Thu Jun 15 2000 - 00:00:00 CDT