Directory File List in PL/SQL (Oracle 10g)
UTL_FILE, unfortunately, does not allow you to query the contents of a directory.
If you’re on Oracle 11g you can use the method described here: listing files with the external table preprocessor in 11g.
If you’re on Oracle 10g another option is to create a java routine. Note: this is not my code – a colleague sent the basics to me and I just modified it somewhat to suit my purpose at the time.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirectoryLister" AS import java.io.File; import java.util.Arrays; public class DirectoryLister { public static String getFileList(String idir, String sep) { File aDirectory = new File(idir); File[] filesInDir = aDirectory.listFiles(); String result = ""; for ( int i=0; i<filesInDir.length; i++ ) { if ( filesInDir[i].isFile() && !filesInDir[i].isHidden() ) { result = result + sep + filesInDir[i].getName(); } } return result; } }; / CREATE OR REPLACE FUNCTION dirlist_csv (p_dir IN VARCHAR2, p_sep IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'DirectoryLister.getFileList (java.lang.String, java.lang.String) return String'; / BEGIN DBMS_JAVA.grant_permission('USER', 'java.io.FilePermission', '<>', 'read'); DBMS_JAVA.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', ''); DBMS_JAVA.grant_permission('USER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', ''); END; /
(replace “USER
” in the above commands with the user name)
CREATE OR REPLACE FUNCTION get_path (dir IN VARCHAR2) RETURN VARCHAR2 IS o_path ALL_DIRECTORIES.directory_path%TYPE; BEGIN SELECT directory_path INTO o_path FROM ALL_DIRECTORIES d WHERE d.directory_name = dir; RETURN o_path; END get_path;
Sample script:
DECLARE csv VARCHAR2(32767); filename VARCHAR2(1000); BEGIN csv := dirlist_csv(get_path('MYDIR'), ','); LOOP EXIT WHEN csv IS NULL; filename := SUBSTR(csv, INSTR(csv, ',', -1) + 1); dbms_output.put_line(filename); csv := SUBSTR(csv, 1, INSTR(csv, ',', -1) - 1); END LOOP; END; /
Note: If the number of files is expected to be large, it would be better to get the java program to insert the file names into a global temporary table, then query that, instead of returning the list as a CSV string.
John Flack
10 August 2010 - 8:31 pm
I’ve got a similar Java Stored Procedure based on one from Tim Hall of Oracle-Base. Mine returns the list as an array of String, then the PL/SQL shell package can return the array via a pipelined table function. That means that I can SELECT * FROM TABLE(my_pipeline_function).
Connor McDonald
23 May 2013 - 1:05 pm
You might like this – no java 🙂
http://www.oracle-developer.net/display.php?id=513
Jeffrey Kemp
23 May 2013 - 1:11 pm
Thanks Connor, good to mention it for future visitors, if there are any 🙂
I was aware of that one. This post was about a 10g database though (I probably should have mentioned that).
If/when they upgrade to 11g I’d probably look at removing this entire thing anyway, because the only reason we want a directory listing is to drive a home-grown “File Watcher” – and 11g has a built-in File Watcher feature in dbms_scheduler.
Mohammad Forhad
28 July 2019 - 1:35 am
Love U Jeff… My Stack Overflow…