> On NT oracle.exe runs as "OracleService<SID>" service.
> Hence UTL_FILE will be executed under whatever OS account
> "OracleService<SID>" service runs.
Well, I just tested it, and I can open a file on a local drive, but not on
a network
drive.
This is because the VOS is running as a service, somewhat as you stated.
The Oracle instance itself does not run as a service.
The VOS ( virtual operating system ) does, and this is the code
responsible for writing to disk.
Here's the workaround from Oracle. Maybe there's a step you were missing
in trying
to get the Oracle Service to run as a user other than SYSTEM?
I followed the instructions, and then successfully created a file on the
same network
drive where the attempt failed previously. Here's the code I used:
DECLARE
relink_log UTL_FILE.FILE_TYPE;
log_dir varchar2(80) := 'F:\tmp';
log_fname varchar2(80) := 'utl.txt';
BEGIN
relink_log := UTL_FILE.FOPEN(log_dir, log_fname, 'w');
if utl_file.is_open( relink_log ) then
utl_file.put_line(relink_log, 'The utl file is open');
UTL_FILE.FFLUSH(relink_log);
UTL_FILE.FCLOSE(relink_log);
else
raise_application_error(-20100,'Open failed on ' || log_dir || '/'
|| log_fname );
end if;
END;
/
HTH
Jared
Doc ID:
Note:1034188.6
Subject:
INVALID_OPERATION Exception from UTL_FILE when
Writing to/from Network Drive
Type:
PROBLEM
Status:
PUBLISHED
Content Type:
TEXT/PLAIN
Creation Date:
30-JUL-1997
Last Revision
Date:
29-JUN-2000
Problem Description
-------------------
On the Windows NT platform, the UTL_FILE package raises the
INVALID_OPERATION exception when attempting to write to, or
read from, a network or compressed drive.
If the exception is not handled, then you receive the following
error:
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "SCOTT.MYFILE", line 9
ORA-06512: at line 1
Problem Explanation
-------------------
The process that is attempting to perform the read or write operation
is the Oracle RDBMS Service. The OracleService<sid> service runs as
the SYSTEM account by default. The SYSTEM account, however, has no
access to shares that are set up by the user who is currently logged in.
Therefore, the Oracle RDBMS service can only read and write files on
local hard drives.
Solution Description
--------------------
Start the Oracle service as a user who has the same permissions as
SYSTEM,
and also who has access to the shared directory.
Solution Explanation
--------------------
When the Oracle service is started, it is done so as the user SYSTEM.
SYSTEM is unable to see any shared directories therefore UTL_FILE is
restricted to accessing local drives only. Hence, Oracle must be
started as a user who can see the relevant shares.
This user must have full permissions in order for Oracle to run
successfully. In addition, we recommend that you alter the Oracle
SQL*Net
Listener Service to log on as the same user.
For a full description refer to the following article:
[NOTE:45172.1] Running UTL_FILE on Windows NT
Another approach is to alter the SYSTEM user so that it can see shared
directories, however, Microsoft does not recommend this because it is
a security risk. Refer to the following Microsoft article:
' Q124184:Service Running as System Account Fails Accessing Network '
( http://support.microsoft.com/support/kb/articles/q124/1/84.asp )
Reference
---------
[BUG:493693] UTL_FILE CANNOT WRITE ON NETWORK DRIVE
.
"Igor Neyman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/28/02 11:49 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: moving from unix to NT
Jared,
On NT oracle.exe runs as "OracleService<SID>" service.
Hence UTL_FILE will be executed under whatever OS account
"OracleService<SID>" service runs.
And I was not able to make it running properly under any other than SYSTEM
account.
Remember, on NT Oracle is one big (though multithreaded) process, unlike
it
is on UNIX.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, February 28, 2002 1:03 PM
> I don't have any experience with UTL_FILE on NT. I've used it a little
> on unix. Don't know why it wouldn't work on NT. As stated by others,
> it might be a problem if writing to network drives, as the SYSTEM user
> does not have access to those when run as a service.
>
> This doesn't seem reasonable though, as the instance itself does not
> run as a service. The VOS runs as a service, but not the instance.
>
> Best bet is to try it.
>
> The biggest problem will likely be your code. If you have paths hard
> coded in it, you will have to rewrite. If they are stored in a table,
> just
> change the data.
>
> If you're interested in Perl, I can send you the same email I sent
> Dave Farnsworth. I can do that tonight from home if you like.
>
> Jared
>
>
>
>
>
>
> John Dunn <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 02/28/02 02:08 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> cc:
> Subject: RE: moving from unix to NT
>
>
> Jared
>
> It would be useful if you could point me in the right direction.
>
> I guess the real question for me is will UTL_FILE work properly when I
> move
> to NT, including on Network drives?. There seems to be some doubt
amongst
> the listers as to whether it does.
>
>
>
> John.
>
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> > Sent: 27 February 2002 18:35
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: moving from unix to NT
> >
> > Perl is *much* more flexible than UTL_FILE for flat file operations.
> >
> > There is simply no basis for comparison.
> >
> > The question in your case is this: Can you easily replace the PL/SQL
> > procedures that are using UTL_FILE with a process that runs outside
> > of the database?
> >
> > If so, myself and others on this list can point you in the right
> > direction, as
> > basics in Perl/Oracle/DBI are really not too hard.
> >
> > If your PL/SQL is part of a larger application and not easily removed,
> you
> > may just have to deal with modifying the PL/SQL.
> >
> > Of course, if you had made this stuff data driven ( meta data, if you
> will
> >
> > ),
> > this would be a non-issue. :)
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > John Dunn <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 02/27/02 01:53 AM
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
