我正在尝试让Oracle 11g数据库在文件系统级别(Windows OS)上创建文件夹。
这是我在架构ABC_DEF上执行的代码:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED createFolder AS
public class FolderManagement {
public static java.lang.String createFolder(java.lang.String folderPath) {
java.lang.String folderCreated = “false”;
try {
java.io.File folder = new java.io.File(folderPath);
if(folder.exists() == true){
folderCreated = “true”; // folder already exists and is usable
}
else {
folder.mkdirs(); // If the parent folder exists,create all necessary subdirectories
if(folder.exists() == true){
folderCreated = “true”;
}
}
}
catch(Exception e){
folderCreated = “false. Error message [” + e.getMessage() + “]”;
}
return folderCreated;
}
};
/
CREATE OR REPLACE FUNCTION createFolder(folderPath IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME ‘FolderManagement.createFolder(java.lang.String) return java.lang.String’;
/
然后,我有一个如下所示的Oracle软件包:
CREATE OR REPLACE PACKAGE ABC_DEF.FILE_UTILITIES AS
parentFolder CONSTANT VARCHAR2(4000) := ‘H:\Oracle logs’;
FUNCTION createDirectory(fullPathToFolder IN VARCHAR2) RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY ABC_DEF.FILE_UTILITIES AS
FUNCTION createDirectory(fullPathToFolder IN VARCHAR2) RETURN BOOLEAN IS
folderCreated BOOLEAN := FALSE;
returnedValue VARCHAR2(4000);
BEGIN
SELECT createFolder(fullPathToFolder)
INTO returnedValue
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(‘Returned value from folder creation is [’ || returnedValue || ‘]’);
IF UPPER(returnedValue) = ‘TRUE’ THEN
folderCreated := TRUE;
END IF;
RETURN folderCreated;
EXCEPTION
WHEN OTHERS THEN
folderCreated := FALSE;
RETURN folderCreated;
END;
END;
/
现在,在我的SYS模式(具有SYSDBA权限)上,我执行了以下命令,该命令应授予ABC_DEF模式权限,以任何递归级别对文件夹“ H:\”进行读/写/删除文件和文件夹。 Oracle日志”。
BEGIN
DBMS_JAVA.GRANT_PERMSSION(
grantee => ‘ABC_DEF’,permission_type => ‘SYS:java.io.FilePermission’,permission_name => ABC_DEF.FILE_UTILITIES.parentFolder || ‘\-’,—- Should grant recursive permissions to any subfolder and any file located in any subfolder
permission_action => ‘read,write,delete’
);
END;
/
回到我的ABC_DEF模式,当我尝试执行以下代码时,我从Java代码中得到一个异常:
DeclARE
subfolderToCreate VARCHAR2(4000) := ‘\2019\November’;
folderWasCreated BOOLEAN := FALSE;
fullFolderPath VARCHAR2(4000);
BEGIN
fullFolderPath := FILE_UTILITIES.parentFolder || subfolderToCreate;
folderWasCreated := FILE_UTILITIES.createDirectory(fullFolderPath);
IF folderWasCreated = TRUE THEN
DBMS_OUTPUT.PUT_LINE(‘Folder was created.’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Folder was not created.’);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Folder wasn’’t created. Exception message [’ || SQLERRM || ‘]’);
END;
我收到以下错误:
Permission (java.io.FilePermission H:\Oracle logs\2019\November read) has not been granted to ABC_DEF. The PL/SQL to grant this is dbms_java.grant_permission(‘ABC_DEF’,‘SYS:java.io.FilePermission’,‘H:\Oracle logs\2019\November’,‘read’)
如果我使用父文件夹(在文件系统级别已经存在),则会出现相同的错误。
关于为什么以及如何解决它的任何想法?
编辑:根据请求添加信息
查询
SELECT *
FROM DBA_JAVA_POLICY
WHERE GRANTEE = ‘ABC_DEF’AND TYPE_NAME LIKE ‘%File%’;
对于“读,写,删除”操作,“名称”列下的文件夹“ H:\ Oracle日志\-”显示一行。
关于H:驱动器,它是物理驱动器,而不是网络驱动器。
关于Windows级别的文件夹设置,“ Oracle日志”文件夹已经在Windows级别存在,并且对“所有人”的访问是完全控制的。