Solved

utl_file problem

Posted on 2003-11-29
11
4,727 Views
Last Modified: 2007-12-19
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
Comment
Question by:hiseetha
  • 4
  • 3
  • 2
11 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 100 total points
Comment Utility
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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
Comment Utility
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
 
LVL 10

Assisted Solution

by:SDutta
SDutta earned 100 total points
Comment Utility
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
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:SDutta
Comment Utility
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
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
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
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
UTL_FILE_DIR init.ora parameter still exists in Oracle 9.2.0.4 and works well.
0
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now