| create directory problem [message #157320] |
Wed, 01 February 2006 10:03  |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
I want to make a directory object but i want the files to be written to a different server then on the database server itself
so i tried the following
create directory test as '\\server2\d$\test';
But when i try to write a file to it with an utl procedure it doesn't work.
when i change it to create directory test as 'c:\test';
it does work
is it possible to even do this with otacle???
thanks
|
|
|
|
|
|
|
|
|
|
| Re: create directory problem [message #157419 is a reply to message #157414] |
Thu, 02 February 2006 06:12  |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Thanks the document exactly said the problem and how to fix it. This is the part in the document i used.
Windows shares and mapped drives
Oracle does not understand about mapped drives. When running as a service on a Windows server (or even using Personal Oracle on your PC), the directory or UTL_FILE_DIR cannot be a windows mapped drive. The services run as the LOCAL SYSTEM user, and has no access to any mapped drives you may have set up.
The following example shows how this doesn't work, and in addition, shows how a directory can be remapped, without causing too much hassle to users who have existing procedures which write or read from the directory - unlike if a specific UTL_FILE_DIR is used. (But, remember, the procedures will go invalid when the directory is dropped - causing a recompile on next execute!)
SQL> connect system
Enter password: ******
Connected.
SQL> drop directory utl_file_dir;
Directory dropped.
SQL> create directory utl_file_dir as 's:\temp\norman\utl_file';
Directory created.
SQL> grant read,write on directory utl_file_dir to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> execute empflatfile
BEGIN empflatfile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "SCOTT.EMPFLATFILE", line 19
ORA-06512: at line 1
If I now change the directory to use the full UNC name of the share mapped as drive S:\ then the following shows what happens :
SQL> connect system
Enter password: ******
Connected.
SQL> drop directory utl_file_dir;
Directory dropped.
SQL> create directory utl_file_dir as '\\fp\shared\temp\norman\utl_file';
Directory created.
SQL> grant read,write on directory utl_file_dir to scott;
Grant succeeded.
SQL> connect scott/tiger
Connected.
SQL> execute empflatfile
BEGIN empflatfile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "SCOTT.EMPFLATFILE", line 19
ORA-06512: at line 1
So that didn't work - why not ?
In control panel, services (NT) or control panel, administrative tools, services - stop the OracleServiceXXXX service. Click on the login tab, and note that it is using a local system logon. That user has no rights to any shares (at least on our NT system,) and so is unable to access the shared drives by their mapped name or full UNC name.
If I change the service to logon as my own username and restart it, I can try again :
SQL> connect scott/tiger
Connected.
SQL> execute empflatfile
PL/SQL procedure successfully completed.
I could also go back and change the directory to use a mapped drive name (S:\temp\norman\utl_file) but if our sysadmins for NT decided to change the share name, or the mapped drive, the procedure would be broken again. It is advisable to use the full UNC name for the drive you are trying to access.
And finally, watch out for the times when you change your Windows password. When you do this, remember to amend your OracleServiceXXXX service again - otherwise the database won't startup.
Thank you very much
|
|
|
|
| Re: create directory problem [message #172341 is a reply to message #157419] |
Tue, 16 May 2006 02:19 |
denis.flotat
Messages: 13
Registered: September 2005
|
Junior Member |
|
|
Could you precise the oracle version you have used.
I have recently heard that this problem of UNC was working until 9i but since 10g, it is necessary to use DBMS_FILE_TRANSFER package in order to bypass that problem.
Anyone has tested those operations on 10g ?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
