Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: UTL_FILE problems

Re: UTL_FILE problems

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/15
Message-ID: <8ibdkd$d08$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US