在 PL/SQL 中存取檔案系統 (Access File System via PL/SQL)

在某些應用中，會希望能夠在 PL/SQL 中就能存取資料庫伺服器上的檔案系統，比方說，拿某個檔案的內容來與資料庫的資料做比對，或是把執行的 Log 寫成檔案，以供日後檢索；但是，該怎麼做呢？


藉由 Oracle 的 Java stored procedure，就可以透過 PL/SQL 處理作業系統中的檔案。
就來看一下實作與 Sample Code 吧！

Step1. 建立 Java Stored Procedure 來處理各種檔案動作:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileHandler" AS
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.Timestamp;

public class FileHandler
{
private static int SUCCESS = 1;
private static int FAILURE = 0;

public static int canRead (String path) {
File myFile = new File (path);
if (myFile.canRead()) return SUCCESS; else return FAILURE;
}

public static int canWrite (String path) {
File myFile = new File (path);
if (myFile.canWrite()) return SUCCESS; else return FAILURE;
}

public static int createNewFile (String path) throws IOException {
File myFile = new File (path);
if (myFile.createNewFile()) return SUCCESS; else return FAILURE;
}

public static int delete (String path) {
File myFile = new File (path);
if (myFile.delete()) return SUCCESS; else return FAILURE;
}

public static int exists (String path) {
File myFile = new File (path);
if (myFile.exists()) return SUCCESS; else return FAILURE;
}

public static int isDirectory (String path) {
File myFile = new File (path);
if (myFile.isDirectory()) return SUCCESS; else return FAILURE;
}

public static int isFile (String path) {
File myFile = new File (path);
if (myFile.isFile()) return SUCCESS; else return FAILURE;
}

public static int isHidden (String path) {
File myFile = new File (path);
if (myFile.isHidden()) return SUCCESS; else return FAILURE;
}

public static Timestamp lastModified (String path) {
File myFile = new File (path);
return new Timestamp(myFile.lastModified());
}

public static long length (String path) {
File myFile = new File (path);
return myFile.length();
}

public static String list (String path) {
String list = "";
File myFile = new File (path);
String[] arrayList = myFile.list();

Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);

for (int i=0; i < arrayList.length; i++) {
// Prevent directory listing expanding if we will blow VARCHAR2 limit.
if ((list.length() + arrayList[i].length() + 1) > 32767)
break;

if (!list.equals(""))
list += "," + arrayList[i];
else
list += arrayList[i];
}
return list;
}

public static int mkdir (String path) {
File myFile = new File (path);
if (myFile.mkdir()) return SUCCESS; else return FAILURE;
}

public static int mkdirs (String path) {
File myFile = new File (path);
if (myFile.mkdirs()) return SUCCESS; else return FAILURE;
}

public static int renameTo (String fromPath, String toPath) {
File myFromFile = new File (fromPath);
File myToFile   = new File (toPath);
if (myFromFile.renameTo(myToFile)) return SUCCESS; else return FAILURE;
}

public static int setReadOnly (String path) {
File myFile = new File (path);
if (myFile.setReadOnly()) return SUCCESS; else return FAILURE;
}

public static int copy (String fromPath, String toPath) {
try {
File myFromFile = new File (fromPath);
File myToFile   = new File (toPath);

InputStream  in  = new FileInputStream(myFromFile);
OutputStream out = new FileOutputStream(myToFile);

byte[] buf = new byte[1024];
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
in.close();
out.close();
return SUCCESS;
} catch (Exception ex) {
return FAILURE;
}
}
};
/
show errors java source "FileHandler"

Step2. 建立一個 Package 把它們「包起來」，以便在PL/SQL中呼叫。

CREATE OR REPLACE PACKAGE file_api AS
FUNCTION canRead (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.canRead (java.lang.String) return java.lang.int';

FUNCTION canWrite (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.canWrite (java.lang.String) return java.lang.int';

FUNCTION createNewFile (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.createNewFile (java.lang.String) return java.lang.int';

FUNCTION delete (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.delete (java.lang.String) return java.lang.int';

FUNCTION exists (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.exists (java.lang.String) return java.lang.int';

FUNCTION isDirectory (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.isDirectory (java.lang.String) return java.lang.int';

FUNCTION isFile (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.isFile (java.lang.String) return java.lang.int';

FUNCTION isHidden (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.isHidden (java.lang.String) return java.lang.int';

FUNCTION lastModified (p_path IN VARCHAR2) RETURN DATE
AS LANGUAGE JAVA NAME 'FileHandler.lastModified (java.lang.String) return java.sql.Timestamp';

FUNCTION length (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.length (java.lang.String) return java.lang.long';

FUNCTION list (p_path IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'FileHandler.list (java.lang.String) return java.lang.String';

FUNCTION mkdir (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.mkdir (java.lang.String) return java.lang.int';

FUNCTION mkdirs (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.mkdirs (java.lang.String) return java.lang.int';

FUNCTION renameTo (p_from_path IN VARCHAR2, p_to_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.renameTo (java.lang.String, java.lang.String) return java.lang.int';

FUNCTION setReadOnly (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA NAME 'FileHandler.setReadOnly (java.lang.String) return java.lang.int';

FUNCTION copy (p_from_path IN VARCHAR2, p_to_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA  NAME 'FileHandler.copy (java.lang.String, java.lang.String) return java.lang.int';

END file_api;
/
SHOW ERRORS

Step3. 權限授予

EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<<all>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
GRANT JAVAUSERPRIV TO SCHEMA-NAME;

這項改變將在下次登入時生效。有些參考文章中提到，你可能需要把想要存取的資料匣加
入到 Init.ora 的 UTL_FILE_DIR 中。像這樣，使用 Java 來存取，則無此必要。


最後，你可以透過以下的 PL/SQL 來測試 FILE_API 的呼叫：

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('canRead      : ' ||  File_API.canRead ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('canWrite     : ' ||  File_API.canWrite ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('createNewFile: ' ||  File_API.createNewFile ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('delete       : ' ||  File_API.delete ('C:\temp\test2.txt'));
DBMS_OUTPUT.PUT_LINE('exists       : ' ||  File_API.exists ('C:\temp\test2.txt'));
DBMS_OUTPUT.PUT_LINE('isDirectory  : ' ||  File_API.isDirectory ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('isFile       : ' ||  File_API.isFile ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('isHidden     : ' ||  File_API.isHidden ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('lastModified : ' ||  TO_CHAR(File_API.lastModified ('C:\temp\test1.txt'), 'DD-MON-YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('length       : ' ||  File_API.length ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('mkdir        : ' ||  File_API.mkdir ('C:\temp\dir1'));
DBMS_OUTPUT.PUT_LINE('mkdirs       : ' ||  File_API.mkdirs ('C:\temp\dir2\dir3'));
DBMS_OUTPUT.PUT_LINE('renameTo     : ' ||  File_API.renameTo ('C:\temp\test1.txt','C:\temp\test2.txt'));
DBMS_OUTPUT.PUT_LINE('setReadOnly  : ' ||  File_API.setReadOnly ('C:\temp\test1.txt'));
DBMS_OUTPUT.PUT_LINE('copy         : ' ||  File_API.copy ('C:\temp\test2.txt','C:\temp\test1.txt'));
END;

/

一路做下來，可以發現，要做到存取檔案系統的動作，有很大的安全性議題。
如果，在資料庫伺服器沒有相對應的權限，那就得想法子繞道去達成需求了！