• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4761
  • Last Modified:

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.

0
hiseetha
Asked:
hiseetha
  • 4
  • 3
  • 2
3 Solutions
 
seazodiacCommented:
UTL_FILE package setup varies by different oracle database version.
but at a glance, I have found out that the error message (ORA-29283: invalid file operation) arised from
the fact that you only assign "READ" privs to the user, ie. you are missing out "WRITE" priv.

therefore, in step 3:
Step 3) Grant read , write on the directory

SQL> grant read, write on directory UTL_DIR to public;


and in step 4: change the line:
 v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'a');
to :
v_output_file1 := utl_file.fopen('UTL_DIR', 'NEW.txt', 'W');



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', 'W');
utl_file .put_line(v_output_file1, 'NATURE and Beauty');
utl_file.fclose_all;
return 1;
end;
/


Hope this solves your problem
0
 
schwertnerCommented:
Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA).

Specify the accessible directories for the UTL_FILE
functions in the initialization file
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>
like in
UTL_FILE_DIR = C:\DATA\ORACLE\DATAFILES
0
 
SDuttaCommented:
Hello Hiseetha,

Whatever you did as part of the scripts are exactly correct if your database is release 9.2, you do not need to make any changes to that.

However I am thinking that you missed out on the settings you must do on Windows systems before you use UTL_FILE package.

When an oracle instance is created the services that support it are setup to "Log On As" the SYSTEM (or operating system) account, this account has very few privileges and no access to NT Domains. To access another NT machine the OracleServiceXXXX must be setup to logon to the appropriate NT Domain as a user who has access to the required location for UTL_FILE.
To change the default logon for the Oracle services, go to :
-> Control Panel
-> Services
-> OracleServiceXXXX (where XXXX is the instance name)
-> Startup
-> Log On As
Choose the "This Account" radio button, then complete the appropriate domain login information. Repeat this procedure for the TNS Listener :
-> Control Panel
-> Services
-> OracleXXXXTNSListener (where XXXX is the Oracle Home Name)
-> Startup
-> Log On As
If the listener is not configured to start as the same user as the Oracle Service user will fail to connect with "ORA-12500 TNS:listener failed to start a dedicated server process". Once the services have been setup as a user with the appropriate privileges you can use UTL_FILE package :

In Oracle 9.2 you do not need to set the UTL_FILE_DIR parameter in the Init.ora or spfile. Also you should NOT grant write on directory UTL_DIR to public.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
seazodiacCommented:
SDutta:
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"
0
 
SDuttaCommented:
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.

0
 
seazodiacCommented:
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"
0
 
SDuttaCommented:
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.
0
 
schwertnerCommented:
UTL_FILE_DIR init.ora parameter still exists in Oracle 9.2.0.4 and works well.
0
 
SDuttaCommented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now