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

ORA-29283 ORA-6512

SQL> create directory testdir as '\\servername\x$';
Directory created.
SQL> grant read,write on directory testdir to public;
Grant succeeded.

SQL> declare
  2  file_handler utl_file.file_type;
  3  begin
  4  file_handler := utl_file.fopen('testdir','file.dat','r');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at line 4


I am trying create a direcory in oracle to a network drive. That is this drive is in another windows server.
The drive letteri x:  The server is "servername"

Can anyone tell me is there a way to create a oracle directory like this I have granted permission  for the
user "Everyone" all privilege so that I can just test this method

Thanks
0
Greens8301
Asked:
Greens8301
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Stupid question but:  Are you sure the file 'file.dat' exists on '\\servername\x$'?

I get the ORA-29283 when the file doesn't exist.

I'm also pretty sure the 'DIR' needs to be uppercase:
file_handler := utl_file.fopen('TESTDIR','file.dat','r');
0
 
Greens8301Author Commented:
slightwv,

Actually this is just a test I am doing to find what syntax should I use to get the "create directory"
to work properly. My ultimate goal is to execute expdp (data pump) from server1, extract data from a database on server2 via sqlnet, write the dumpfile to a drive in server1.

expdp works fine from server1, extarct data from database on server1 write to a filesystem on server1
expdp works fine from server1 with loop back sqlnet string also

When I try create directory on database on server2 to point to the directory on server1, for some reason, it does not recognize file location

I am doing this simple test. If this test works, then expdp probably will work

Thanks
0
 
slightwv (䄆 Netminder) Commented:
Even if it's a test, the file has to exist for a 'R' operation.  Try changing it wo a 'W' for the test.

For the real question:  Make sure the Windows user 'SYSTEM' on server1 has rights to the directory on server2.
0
 
Greens8301Author Commented:
For the test, I followed your suggesion, works well for the local disks.

For remote disks, I may have to talk to system administrators
What is the format for assigning permission on to the drive <machine name>/SYSTEM@<domain_name>
Getting a invalid name error


0
 
slightwv (䄆 Netminder) Commented:
Are you talking about the SQL 'grant' permissions?  I was talking about the O/S Security (ACLs) on the directory.

There are hoops you have to jump through to get Oracle to write to network drives in a Windows environment.  A quick google will bring back tons of links.  If you have access to Metalink, there are a few papers out there as well.

To be honest, I tried something similar a few years ago and never was able to get it to work.  Granted, I didn't spend a lot of time messing with it.   I've read the papers where others have gotten it working.

In a nutshell, the Windows service runs as 'SYSTEM' so any file I/O it does is performed as the SYSTEM user.  This user needs access to the drives.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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