ok what am I doing wrong 1

[CIMTEET]

1 create or replace directory temp as '/home/marting/test';
2 Declare
3 l_file utl_file.file_type;
4 strbuffer VARCHAR2(32767);
5 mynum number;
6
7 Begin
8 l_file:=utl_file.fopen('temp','Cimteet.txt','R');
9 utl_file.get_line(l_file,strbuffer);
10 END;
11 /
12 quit

I keep getting the error ORA 29280 invaled directory and two errors that show the sys.utl_file line 18 (1) and line 424(2).
Is the 'temp' correct? I took this straight out of an O'reilly example book. (and yes the directory exists)

 

[SantaMufasa]

Greg,

Strangely, and interestingly, your problem borders on being an Oracle BUG (but, perhaps, Oracle could argue that it is not a bug.): Oracle stores, in its data dictionary, the name of the DIRECTORY you create in UPPERCASE. Your quoted, literal reference within the "utl_file.fopen" function is in lower case. Therefore, Oracle thinks that it cannot find a match to the directory. If, however, you uppercase the reference, you should be fine.

Here is a proof of my assertion:

create or replace directory temp as '<some existing path>';

Directory created.

set serveroutput on
Declare
l_file utl_file.file_type;
strbuffer VARCHAR2(32767);
mynum number;
Begin
l_file:=utl_file.fopen('temp','Cimteet.txt','R');
utl_file.get_line(l_file,strbuffer);
dbms_output.put_line(strbuffer);
END;
/
Declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 6

(Corrected code)

set serveroutput on
Declare
l_file utl_file.file_type;
strbuffer VARCHAR2(32767);
mynum number;
Begin
l_file:=utl_file.fopen('TEMP','Cimteet.txt','R');
utl_file.get_line(l_file,strbuffer);
dbms_output.put_line(strbuffer);
END;
/
This is a text string in file Cimteet.txt

Let us know if this resolves your issue.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

It worked...at first. And then I changed the address to something that was not included in the utl_file_dir and i got an ora 29283 invalid operation. Sounds like the privileges wall has been hit again. What do you think?

Greg

 

[SantaMufasa]

Greg, as you recall from my 13 Apr 07 18:13 reply in thread759-1356109, my utl_file_dir value was NULL, and I had no problem accessing any directory. This does not rule out some other type of *nix path-permission/file-permission problem.

So that we can help you more effectively, please post the following:

1) Your "CREATE DIRECTORY..." command code
2) An "ls -l" that confirms the ownership and permissions of both your target *nix path and any pertinent files.
3) Your relevant PL/SQL code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

The code is the same on this thread. The 2 directories are:
home/oracle/tmp/Cimteet.txt marting staff -rwxrwxrwx
home/oracle/tmp oracle ointall drwxrwxrwx
home/oracle oracle ointall drwxr-xr-x
home/marting/test/Cimteet.txt marting staff-rwxrwxrwx
home/marting/test marting staff drwxrwxrwx
home/marting marting staff drwxrwxrwx

The file is flat_file_read.sql. This has both the create directory and the utl_file code. I do remember your utl_file_dir having nothing in it. Does your oracle session have a dba role? Mine doesn't. All I know is I haven't been able to get it to work.

Greg

 

[SantaMufasa]

Greg,

What was the result when you issued the "CREATE DIRECTORY..." command -- was it successful or did it fail?

When you receive error-message output, could you please copy and paste it here, along with the code that generated the error?

If the directory exists following another user's successful "CREATE DIRECTORY..." execution, until you (as a non-DBA user) receive privileges to access that directory, you will receive this type of error:

Declare
*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at line 6

To resolve that problem, either the directory creator or a DBA can issue this command:

grant read, write on directory <dir name> to <user>;

or 

grant read, write on directory <dir name> to PUBLIC;

Once the accessing user receives the above privileges, then

1) that <dir name> appears when that user queries ALL_DIRECTORIES, and
2) that user can read and write to that directory

You can/should query ALL_DIRECTORIES as your user to confirm what directories you can access presently.

Let us know your findings.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

I got everything the same as your errors except the first ORA error was 29283 invalid file operation. I found this online which states that access was not granted by the OPERATING system.

ORA-29283: invalid file operation
Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system.
Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.

YOUR ERROR MEANT THIS.
ORA-29289: directory access denied
Cause: A directory object was specified for which no access is granted.
Action: Grant access to the directory object using the command GRANT READ ON DIRECTORY [object] TO [username];.

according to this it wasn't oracle that stopped it. It was unix. Why? Is it a fluke. If that is true than I am missing something in the attribs of files and folders. This doesn't change the fact that all I have been able to write to are files that are on the utl_file_dir list.
unix
oracle

 

[CIMTEET]

Well actually, unix and oracle have been good to me.

 

[SantaMufasa]

So, Greg, all told, if you tally up all of the hours that you and I have spent on trying to get all of this going for you (on Unix), and compare that total with the seconds/minutes it would take your "over-worked" Oracle DBA to assign appropriate values to utl_file_dir and bounce the instance, where do you see the best cost-benefit ratios? <grin>

<off the record>

If your DBA cannot do her job (i.e, keep data secure while providing information resources to developers and users) then I say the firm should either get her help or let her go.

</off the record>

If you have a proven scenario/workaround that does what you want, I suggest you pursue that angle, obtaining the resources you need from your DBA.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

I hesitate to get involved in this thread, since I don't know how to resolve your problem. However, it is hardly unreasonable for your dba to enforce a standard which reflects Oracle's own recommendation, as stated in Metalink note 206272.1

UTL_FILE_DIR access is not recommended in 9iR2. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR.

I can reproduce the ORA-29283 error by removing execute privileges on one of the Unix directories in the path to the file that utl_file is trying to read. However, it appears that this is not the problem, since you have documented the permissions on most of the directories in the path.

If I were you, I would log into the database server as the oracle user. (If you can't do this yourself, ask the dba for help.) Get DIRECTORY_PATH from all_directories for the temp directory and have the oracle user cd to this directory. Then cat the 'Cimteet.txt' file to make sure that the oracle user can actually read it. If there really is a Unix permission problem, one of these steps should fail. Otherwise your error is probably caused by something other than Unix permissions.