Hi TOM,
I am working on writing a package that has a requirement to create hyperlinks for photos if they exists on the server but have encountered an issue. Unsure if its the oracle user unable to access network locations (i.e. files shared on a different server other than Oracle DB Server) or permission issue. Here as an example that shows what i have done and the tests i have carried out.
I have created a directory object called
PHOTOS_DIR that points to a share on my network
'\\FileServer\Photos\' using the statement below:
CREATE OR REPLACE DIRECTORY PHOTOS_DIR AS '\\FileServer\Photos\';
I have also created a function
FileExistsTest which accepts two parameters - Directory Object Name and File Name and checks if the specified file exists in the path specified in the directory object. Here is the code for the function:
CREATE OR REPLACE FUNCTION FileExistsTest(
p_DirName in VARCHAR2, -- schema object name
p_FileName in VARCHAR2
) RETURN NUMBER
IS
l_file_loc BFILE;
BEGIN
l_file_loc := BFILENAME(UPPER(p_DirName), p_FileName);
RETURN DBMS_LOB.FILEEXISTS(l_file_loc); -- 1 exists; 0 - not exists
END;
Now, when i run the following query, i am expecting 1 if files exists or 0 if it does not exists but instead i get an error:
SELECT FileExistsTest('PHOTOS_DIR','P1234.jpg') FROM DUAL;
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEEXISTs failed
The device does not recognize the command.
ORA-06512: at "SYS.DBMS_LOG", line 786
ORA-06512: "GPROD.FILEEXISTSTEST", line 9However, when i change the path for my directory object to
C:\Temp\Photos using the SQL statement below it executes fine and i get a return value of 1.
CREATE OR REPLACE DIRECTORY PHOTOS_DIR AS 'C:\Temp\Photos';
SQL> SELECT FileExistsTest('PHOTOS_DIR','P1234.jpg') FROM DUAL;
FILEEXISTSTEST('PHOTOS_DIR ','P1234.JPG')
-----------------------------------------
1I have also gone a step ahead an mapped
'\\FileServer\Photos\' on the database server on G: Drive and changed the path for my directory object to
G: and then run the SQL statement which also executes fine which means it now can access the file system but cannot get its content which leads us to the second point: OracleHome cannot access those files due to permission issue?
What does you think could be the problem here and how can i resolve it. Do you think Oracle cannot access network locations. Also the OracleHome user is created as a local user on the Database Server and cannot be shared with other machines (i was thinking of giving OracleHome user full access to '\\FileServer\Photos').
I would really appreciate your insight into this and how can i over come my problem.
Kind Regards,
Dhaval Kumar
MOS note 739772.1 looks like it relates to your issue:
GOAL
The purpose of this document is to provide the customers with necessary configuration to create database directory on a remote server . For exmaple:
CREATE OR REPLACE DIRECTORY 'SUBSEADATA_1' AS '<Remote_serverDriveOrNetworkUNC>;
Note: This is not a supported action as per Oracle Documentation :
The operating system directory path named in the Oracle DIRECTORY object should not contain any symbolic links in its components.
Reference: "Rules for Using Directory Objects and BFILEs"
http://docs.oracle.com/database/121/ADLOB/adlob_managing.htm#ADLOB45157
However, if it was proven that same error occurred not only in the UNC Env, but also in the Env. with using local hard disk, We Oracle will support the issue.
SOLUTION
It is recommended to use the Domain Administrator account to ensure that passwords are the same across the various machines. If there is no domain, use the local Administrator account andvensure that the passwords are the same for this account across all of the machines.
1. On the machine where you wish to write the files to, create a shared drive granting the user
'Administrator' FULL Control.
Note: For reference below, this Administrator will use the password 'test', this will be referred
to as the "Destination" machine.
2. On the machine with the DB, verify the Administrator user has the same password of the user that shared the drive on the destination machine. In the example here, the password would be 'test'.
3. Map a network drive on the DB machine to the shared drive on the destination machine. When mapping this drive, use the Administrator user with the password 'test'.
4. On the DB machine, BOTH the OracleTNSListener Service and the OracleService<SID> services must be configured to start using the Administrator/test account.
(As discussed above, Oracle uses the Local System account by default.)
a) Go to the Control Panel and then open up the Services panel.
b) Double click on the appropriate service
(TNSListener or OracleService<SID>).
c) Change the "Log on as" user from the "Local System Account" to
"This Account".
d) Specify the service to log on as the Administrator user.
e) Click on "OK".
5. Shutdown the database and stop and start the services on the DB machine. Restart the database.
6. You should now be able to create directory that references the local map or UNC location and access it.
Special Windows 2003 Update :
As Windows 2003 has a changed access behavior compared to Windows 2000, the solution is a little restrict :
Don't use local drive letters for mapping network shares. Workaround is to use UNC locations directly, e.g. backup to \\B\share Since this is in fact an absolute location this is always the
same for any node in the network. So whether accessed from node A or node B, \\B\share is always the shared location on B.