- Integrated Systems
- About Us
- Integrated Systems
- About Us
02-20-2004 04:55 AM
02-20-2004 08:45 AM
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:
02-22-2004 12:07 PM
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.
utl_file_dir = /tmp
utl_file_dir = /home/nostradamus
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:
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.
02-24-2004 02:13 AM
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
file_text := 'test3';
file_name := 'test_utl.txt';
file_handle := UTL_FILE.FOPEN('/thpdata/thpoutput', file_name ,'A');
WHEN utl_file.invalid_path THEN
WHEN utl_file.invalid_mode THEN
WHEN utl_file.invalid_operation THEN
WHEN utl_file.invalid_filehandle THEN
WHEN utl_file.write_error THEN
WHEN utl_file.internal_error THEN
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.
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
PL/SQL procedure successfully completed.
SQL> show user;
USER is "TEST"
02-24-2004 02:53 AM
create the procedure as user test and grant execute to ODS user.
then as user ODS
02-24-2004 12:23 PM
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.
02-24-2004 01:03 PM
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.
02-24-2004 04:19 PM
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.
02-25-2004 03:46 AM
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.
02-25-2004 12:07 PM
When you say you played with the .profile did you change the PATH variable. What changes did you do. But then again you said you put back the original .profile and it works. Did you change any directory permissions in the mean while.
03-12-2004 08:17 AM
03-12-2004 09:50 AM
1. Check the permissions on the the $OH/bin/oracle executable. Should be suid for oracle.
2. Check what user is running the listener. You might want to restart this, as it should be writing as oracle when connecting through SQLNet.