I am running into a problem when I want to access files from PL/SQL that are located on a network share.
The problem occurred first when I wanted to attach a file to e email message.
I've now reduced the problem to this piece of PL/SQL:
SET SERVEROUTPUT ON
DECLARE
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
BEGIN
UTL_FILE.fgetattr ('EXAMPLE', 'testfile.txt', v_exists, v_length, v_blocksize);
IF v_exists
THEN
DBMS_OUTPUT.put_line ('File found');
ELSE
DBMS_OUTPUT.put_line ('File not found');
END IF;
END;
This script checks if a file exists, so if this script fails, attaching the file to a email won't work either.
Before running the script, I've created a directory:
create or replace directory EXAMPLE as '\\servername\dirname\';
grant read, write on directory EXAMPLE to public;
The problem is that the script tels me it can't find the file when I want to access a file located on another computer.
I've tried it using the UNC path, and tried it using a network mapping.
When I use the script to access a local file (on the c: or d: disks) it works fine.
When I searched the net for answers, all I could find was that it could have something to do with rights. Both computers (the one with oracle, and the one with the file on it) run on windows 2003.
On the PC with the file on it, I've changed the rights for the share, and gave 'full control' to 'everyone'. On the pc with oracle on it, I've done the same for the shared drive, but it didn't help.
I've noticed that the Oracle service is running under the "local system account", but I can't change this (I don't have the rights for it).
To see if oracle was attempting to open the file on the remote computer, I've enabled windows auditing for the directory. By doing that, I got the following log:
Event Type: Success Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 540
Date: 17-1-2007
Time: 13:35:40
User: NT AUTHORITY\ANONYMOUS LOGON
Computer: fileserver
Description:
Successful Network Logon:
User Name:
Domain:
Logon ID: (0x0,0xD4FA9E17)
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: database server
Logon GUID: -
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: 10.92.39.14
Source Port: 0
The log shows that the user "ANONYMOUS LOGON" from the database server tried to access the folder on the fileserver, and apparently did a "Successful Network Logon".
I get exactly the same log if I try to access that folder from my workstation (only with a different workstation name of course)
So It seems like the folder is accessible, even for the account oracle is running at, still I can't access the files from PL/SQL.
Any idea what goes wrong?