Re: Question on utl_file

Clifton got it right. The problem was the trailing delimiter.
I was mislead by Steven Feuerstein's
"Oracle PL/SQL" book in which he stated (p. 543):

"When you pass the location in the call to FOPEN, you must include a final delimiter in the location specification as shown below:
o In DOS:

     file_id := UTL_FILE_FOPEN('k:\common\debug\', 'trace.lis', 'R');"

Maybe the DOS part messed me up as I'm working under NT? Or maybe books have bugs in them too.

What works is this:

  file_handle UTL_FILE.FILE_TYPE;
  line_out VARCHAR2(100);
  loc VARCHAR2(30) := 'C:\TEMP'; -- This is what's different
  BEGIN
     file_handle := UTL_FILE.FOPEN(loc,p_file,'r');

The init.ora file parameter is:

utl_file_dir=C:\TEMP

Clifton L. McLellan wrote:

> ---------- Forwarded message ----------
> Date: Tue, 20 May 1997 09:17:18 -0700
> From: Clifton L. McLellan
> To: 'Peter Adams'
> Subject: RE: Question on utl_file
>
> My INIT file contains
>
> UTL_FILE_DIR = d:\dir1\dir2\dir3\Queries
>
> And my open statement is
>
> fd := UTL_FILE.FOPEN( 'd:\dir1\dir2\dir3\Queries','newfile', 'w' );
>
> I do note that you are terminating your directory with a backslash. I just modified mine to match yours and got the following:
>
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 82
> ORA-06512: at "SYS.UTL_FILE", line 120
> ORA-06512: at "USERNAME.FUNCTIONNAME", line 54
> ORA-06512: at line 3
>
> Does that look familiar?
>
> Thanks,
> Clifton
>
> Clifton L. McLellan
> Reaction Design
> McLellan_at_ReactionDesign.com
>
> I am currently using the following:
>
> fd := UTL_FILE.FOPEN( 'd:\dir1\dir2\dir3\Queries',
> 'newfile', 'w' );
>
> where the directory name is cut and pasted to the instance
> init.ora file.
> Note that 'a' does not seem to work. I discovered that
> the very hard way.
> --
>
> Hope this helps,
> Clifton L. McLellan
>
> Peter Adams <padams_at_sul.stanford.edu> wrote in article
> <3380E55F.6E94A8F_at_sul.stanford.edu>...
> > Are there any tricks to get
 utl_file.fopen(dir,file,mode)
> > to work on NT from a stored procedure? I keep getting an
 

> > invalid path exception. I modified the initorcl.ora file
 

> > with the utl_file_dir parameter to read c:\temp As
 arguments
> > to fopen I am passing the directory with the additional
> > delimiter (c:\temp\), plus the file name (x.txt) and the
 'R'
> > argument for read only. This is all happening within my
 NT
> > box.
> >
> > TIA,
> >
> > Pete
> >
> > --
> > Peter Adams
> > Sr Systems Developer
> > Stanford University Libraries
> > vc: 415-725-7909
> > fx: 415-725-4902
> > em: padams_at_sulmail.stanford.edu
> > http://www-sul.stanford.edu
> >
> >

--
Peter Adams
Sr Systems Developer
Stanford University Libraries
vc: 415-725-7909
fx: 415-725-4902
em: padams_at_sulmail.stanford.edu
http://www-sul.stanford.edu