ORA-29283 ORA-6512

Posted on 2007-08-06
Last Modified: 2013-12-18
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  /
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

Question by:Greens8301
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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');

    Author Comment


    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

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment

    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

    LVL 76

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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.

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    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.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now