Read a Directory using PLSQL


In Oracle 10G there is not UTL function / procedure to read the listing of a directory.

There is a undocumented procedure that can be used

 

CREATE OR REPLACE PROCEDURE list_directory
(directory VARCHAR2)
IS
ns          VARCHAR2(1024);
v_directory VARCHAR2(1024);
BEGIN
v_directory := directory;
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(v_directory, ns);
FOR each_file IN (SELECT fname_krbmsft AS name FROM x$krbmsft) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;

 

This must be compiled as SYS and then EXECUTE permision granted to the relevat user.  Care must be taken on who should have access to this, I would suggest not giving it out to all user including poer users but limit it to perhaps a batch processing user.  This is where most of the benefit I believe will come from.

Grant execute of List_Directory to XXXXXXXX;

 

This then can be wrappered in a PLSL procedure a quick example below

begin
sys.list_directory(‘/IFS_Intergration/DataDump’);
for each_file in (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) loop
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
end;

TO BE CONTINUED, time for home 🙂


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s