Iterating over files in PLSQL

Question

Using a PLSQL script, is it possible to iterate over binary files in a folder and insert them as a BLOB in Oracle 10g? Any examples would be appreciated.

Answer 1

Converting a file's contents to a blob is covered here: http://stackoverflow.com/questions/122909/using-pl-sql-how-do-you-i-get-a-files-contents-in-to-a-blob

However this doesn't help you iterate over multiple files in a directory.

Two possible solutions for this:

Solution 1: SYS.DBMS_BACKUP_RESTORE.searchFiles

You can pass this procedure your search criteria and it populates a global in-memory table with the list of results over which you can then iterate like so:

DECLARE

pattern VARCHAR2(1024) := '/u01/oracle/admin/SID/udump';
ns VARCHAR2(1024);

BEGIN
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);

FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT WHERE FNAME_KRBMSFT LIKE '%.trc') LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;

END;
/

See a full example complete with an excellent explanation here.

Solution 2: Java

The great Tom "AskTom" Kyte shows how to iterate over files using a Java procedure here.