Re: Why is UTL_FILE.FOPEN failing?

[Quoted] On Apr 12, 9:16 am, PeteOlcott <peteolc..._at_gmail.com> wrote:
> INPUT:
> create or replace directory filesdir as 'c:\';
> grant read on directory filesdir to public;
>
> declare
>  namesfile UTL_FILE.FILE_TYPE;
> begin
>  --  Syntax : FOPEN ( directory alias, filename, open mode)
>  namesfile  := UTL_FILE.FOPEN('FILESDIR','CASELIST.TXT','r');
> end;
>
> OUTPUT:
>
> SQL> create or replace directory filesdir as 'c:\';
>
> Directory created.
>
> SQL> grant read on directory filesdir to public;
>
> Grant succeeded.
>
> SQL>
>   1  declare
>   2   namesfile UTL_FILE.FILE_TYPE;
>   3  begin
>   4   --  Syntax : FOPEN ( directory alias, filename, open mode)
>   5   namesfile  := UTL_FILE.FOPEN('FILESDIR','CASELIST.TXT','r');
>   6* end;
>   7  /
> declare
> *
> ERROR at line 1:
> ORA-29283: invalid file operation
> ORA-06512: at "SYS.UTL_FILE", line 488
> ORA-29283: invalid file operation
> ORA-06512: at line 5

I'll ask the obvious question: Does c:\CASELIST.TXT exist? I doubt that it does:

SQL> $ls c:\caselist.txt
ls: c:\caselist.txt: No such file or directory

SQL>
SQL> declare
  2 namesfile UTL_FILE.FILE_TYPE;
  3 begin
  4 -- Syntax : FOPEN ( directory alias, filename, open mode)
  5 namesfile := UTL_FILE.FOPEN('FILESDIR','CASELIST.TXT','r');   6 end;
  7 /
declare
*
ERROR at line 1:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5

SQL>
SQL> $touch c:\caselist.txt

SQL>
SQL> declare
  2 namesfile UTL_FILE.FILE_TYPE;
  3 begin
  4 -- Syntax : FOPEN ( directory alias, filename, open mode)
  5 namesfile := UTL_FILE.FOPEN('FILESDIR','CASELIST.TXT','r');   6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> David Fitzjarrell Received on Fri Apr 13 2012 - 20:10:13 CEST