hiseetha
asked on
utl_file problem
Step 1) Connect as sys
Step 2) Create a directory:
CREATE DIRECTORY UTL_DIR AS 'C:\oracle\UTL_DIR';
---DROP DIRECTORY utl_dir ;
Step 3) Grant read on the directory
SQL> grant read on directory UTL_DIR to public;
Grant succeeded.
Step 4) Create function
create or replace function f1 return number is
v_output_file1 utl_file.file_type;
begin
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
utl_file .put_line(v_output_file1, 'NATURE and Beauty');
utl_file.fclose_all;
return 1;
end;
/
Step 6) Create a bind variable and Execute the function
var x number
exec :x:=f1;
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 439
ORA-29283: invalid file operation
ORA-06512: at "SYSTEM.F1", line 4
ORA-06512: at line 1
i got this coding from experts exchange.
while executing above procedure i am getting this error,
i have to add utl_dir parameter in parameter file.
how to mention it and how to change it in parameter file.
Step 2) Create a directory:
CREATE DIRECTORY UTL_DIR AS 'C:\oracle\UTL_DIR';
---DROP DIRECTORY utl_dir ;
Step 3) Grant read on the directory
SQL> grant read on directory UTL_DIR to public;
Grant succeeded.
Step 4) Create function
create or replace function f1 return number is
v_output_file1 utl_file.file_type;
begin
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
utl_file .put_line(v_output_file1, 'NATURE and Beauty');
utl_file.fclose_all;
return 1;
end;
/
Step 6) Create a bind variable and Execute the function
var x number
exec :x:=f1;
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 439
ORA-29283: invalid file operation
ORA-06512: at "SYSTEM.F1", line 4
ORA-06512: at line 1
i got this coding from experts exchange.
while executing above procedure i am getting this error,
i have to add utl_dir parameter in parameter file.
how to mention it and how to change it in parameter file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Seazodiac :
As per the new 9.2 utl_file specifications, the user compiling the utl_file package just needs read access to the DIRECTORY object. It does not matter if the user executing the package actually has any rights to it or not. So whether the user wants to read or write to any location, they just need READ access to the directory object which should always be owned by SYS. Of course the user running the service OracleServicexxxx must have the appropriate privilege read/write/both to the OS directory.
As per the new 9.2 utl_file specifications, the user compiling the utl_file package just needs read access to the DIRECTORY object. It does not matter if the user executing the package actually has any rights to it or not. So whether the user wants to read or write to any location, they just need READ access to the directory object which should always be owned by SYS. Of course the user running the service OracleServicexxxx must have the appropriate privilege read/write/both to the OS directory.
you are right about that, I got that part too.
I was talking abt different thing though here.., dig again...
the asker's original code containes the following line :
#Oracle will throw an error, because there is NO 'a' mode.
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
And it should be changed to:
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'w');
that parameter specifies the mode of the file opened, it could be "R" or "W"
I was talking abt different thing though here.., dig again...
the asker's original code containes the following line :
#Oracle will throw an error, because there is NO 'a' mode.
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
And it should be changed to:
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'w');
that parameter specifies the mode of the file opened, it could be "R" or "W"
Oh that ...
the 'a' setting is to open a file in "append" mode.
If the file does not exist, it creates a new file, if it exists, the record is appended to the end of the file.
the 'a' setting is to open a file in "append" mode.
If the file does not exist, it creates a new file, if it exists, the record is appended to the end of the file.
UTL_FILE_DIR init.ora parameter still exists in Oracle 9.2.0.4 and works well.
Schwertner :
The old method of using utl_file_dir parameter can still be used in 9.2 but is not recommended. This is mainly due to two reasons. First the database has to be recycled every time you make a change to the parameter file. Second the utl_file_dir parameter has a maximum length of 255 characters (total) and you cannot add any more directories once you have reached that length.
Please refer to Metalink notes 196939.1 and 206272.1 for more details.
The old method of using utl_file_dir parameter can still be used in 9.2 but is not recommended. This is mainly due to two reasons. First the database has to be recycled every time you make a change to the parameter file. Second the utl_file_dir parameter has a maximum length of 255 characters (total) and you cannot add any more directories once you have reached that length.
Please refer to Metalink notes 196939.1 and 206272.1 for more details.
maybe I missed out sth here, but the asker's original code containes the following line :
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
I think here is the problem, it should be changed to:
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'w');
that parameter specifies the mode of the file opened, it could be "R" or "W"