utl_file_dir

[Brian_274]

I'm trying to set up another directory for read/write. The only way I can get it to work is if I put and '*' for the value. I don't want to do that. I can currently write to the existing ones but no new ones. I've read the documentation and set the utl_file_dir with the new parameter on a new line, I've tried it on the same line in the init.ora file. Oracle doesn't own the directory but is a member of the group. I thought I had it working earlier today but now it's not working again. Any suggestions?

[Brian Crabtree]

1. Is it showing up in the utl_file_dir when you query for it in the v$parameter view?

2. Can oracle write to the directory (cd dir; touch hi)?

3. Are you getting an Oracle error when you attempt to write to a file or create a file in that directory?

You are aware that you can't write into sub-directories under the directory specified, correct?

The following wouldn't be valid:

utl_file.fopen('/directory','subdirect/file.txt','W');

Thanks,

Brian

[Indira Aramandla]

Hi Brian,

Having the value "utl_file_dir = *", would make ALL operating system directories accessible to UTL_FILE. This is not recommended since operating system permissions will most likely be circumvented - VERY RISKY.

You can define them in two lines or a single line as you already did.

For example:

utl_file_dir = /tmp
utl_file_dir = /home/nostradamus

or

utl_file_dir = /tmp, / home/nostradamus

If you create an spfile from a pfile with these settings, you will see that it ends up in the spfile like this:

*.utl_file_dir='/tmp','/home/nostradamus'

In Oracle 9i Release 2 you can create a directory within oracle and do not have to specify the util parms in your init.ora.

But as Brian C mentioned make sure Oracle user can write to that directory by touch a text file.

Indira A

[Brian_274]

utl_file_dir=utl_file_dir=/thpdata/thpoutput,/thpdata/weboutput,/thpdata/weboutput/report,/thpdbata/ediinput,/shdata/discfiles/iofiles

I've tried both one line and each entry on a separate line. I haven't tried entering them in single qoutes yet.

When I query v$parameter it comes up the following:

/thpdata/weboutput, /thpdata/thpoutput, /thpdata/weboutput/report, /thpdbata/ediinput, /shdata/discfiles/iofiles

So that appears fine. I created a basic procedure:
***
CREATE OR REPLACE PROCEDURE test_utl_file_dir
as
file_handle UTL_FILE.FILE_TYPE;
file_name VARCHAR2(50);
file_text VARCHAR2(50);
BEGIN
file_text := 'test3';
file_name := 'test_utl.txt';
file_handle := UTL_FILE.FOPEN('/thpdata/thpoutput', file_name ,'A');

UTL_FILE.PUTF(file_handle,file_text);
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20104,'Write Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
END;

***
I run this under my account and it works fine. I create a new user called test with only connect, unlimited tablespace and create procedure privileges and it works. I have another user called 'ODS' and it doesn't work for that one. The permissions for 'ODS' are the same and more. It's the same code. It also doesn't work from any GUI tools. Do I have to be connected to the os in order to run it? Here's the error msg.

SQL> begin
2 test_utl_file_dir;
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20102: Invalid Operation
ORA-06512: at "ODS.TEST_UTL_FILE_DIR", line 30
ORA-06512: at line 2
****
Here's it working for 'test'

SQL> connect test/test
Connected.
SQL> l
1 begin
2 test_utl_file_dir;
3* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> show user;
USER is "TEST"
***

Any suggestions?

[Brian_274]

And just to mention this is Oracle 8.1.7.4 not 9i. I never had a problem with this in 9i.

[Jean-Luc Oudart]

Hi try
create the procedure as user test and grant execute to ODS user.
then as user ODS
sqlplus
execute test.test_utl_file_dir

Regards,
Jean-Luc

[Indira Aramandla]

Hi Brian,

You received an unhandled UTL_FILE.INVALID_OPERATION exception. The file cannot be created may be because of permissions in given directory or when you are trying to write to someone else's file without having proper permissions.

Verify permissions to the directories. The file location must be an accessible directory, defined in the instance's initialization parameter UTL_FILE_DIR.

In normal cases, the file that is created by the FOPEN is owned by ORACLE. Files created using the FOPEN are always writable and readable using the UTL_FILE routines. User may need to get the correct privileges in order to have access to the files.

Or may be check whether you have public synonym on UTL_FILE package and execution privileges has been granted to either public or specific user from which you execute the code.

I hope this will help.

Indira A

[Brian Crabtree]

Brian,

In your error message, it is pointing to line "30" in the TEST_UTL_FILE_DIR procedure. I can't see this line. I would verify that you are running the correct code, and not an earlier version.

To check, run the following:

select * from dba_source where name = 'TEST_UTL_FILE_DIR' and line = '30';

I don't get a response after loading your code for it.

Thanks,

Brian

[Yogeeraj_1]

hi,

I went through metalink note 45172.1 and section quoted below:


2) Directories with space characters
------------------------------------

For this to work the directory must be delimited by double quotes in the
init.ora parameter file.

e.g. UTL_FILE_DIR="E:\LOG DIR".


can you please confirm that the utl_file_dir value is double-quoted in your init.ora.

thanks
yogeeraj

[Brian_274]

Ok here's the latest.
1. I've tried defining utl_file_dir on one line and multiple lines, no luck.
2. I've tried defining it with no quotes, single quotes and double quotes with no difference in the v$parameter view.
3. I connect to the os as oracle, then to sqlplus as ods, test and any other user and the procedure works.
4. I connect to the OS as the 'ods' user and it doesn't work. Even granting execute permissions doesn't work. I play around with the .profile it works. I change it back to the original .profile and it still works. I'm reloading the profile after each change using '. .profile'. So at the moment everything is working. If it was something in the profile then why does it work now after I've restored the original .profile?
5. And yes both oracle and ods user_id's have the correct permissions for the directory. i can manually create a file on the os.
6. I have the correct permissions on the package because I can run it just fine when I change the directory.
7. Line 30 is the error handler catching the error.
8. And I don't have any spaces in the directory names.