How to check existence and create a physical file system directory from db?
Hi all,
I was hoping somebody would have some alternative suggestions for the following problem under an Oracle 9.2 Windows database please?
I have a requirement to check if physical file-system directories (not Oracle Directory objects) exist via PL/SQL, and if not creating the physical directory, then moving a file to that directory. The physical directories can exist both on the Oracle server or remotely via a drive letter mapping or a UNC path (with the assumption that appropriate privileges are available to read/view the remote directories)
Oracle Directory objects are not_ appropriate in this case as they only work for a static directory. In turn as extension on this point, utl_file isn't appropriate as it doesn't have the ability to make directories or copy files under 9.2 at least.
My initial solution was to install the Java package java.io.File into the database, create a PL/SQL wrapper, then call it's methods. But I've hit a number of potential showstoppers with the java.io.File.exists() method. Firstly java.io.File.exists() fails on remote drive letter mapped file systems. Secondly as a workaround using a UNC path for a remote directory path, java.io.File.exists() fails on some directories without an obvious reason. The UNC path approach implies an operating system permissions error, yet we can't see any difference on the directory permissions. There are logged bugs against java.io.File so it may be something we can't get around.
To ensure I don't get stuck, I'm trying to think up other solutions on our 9.2 database to achieve the same thing.
Under 10g there is dbms_scheduler which allows you to execute operating system commands, but not available to us under 9.2.
I guess another solution would be an external C routine to create the same routines, but I'd rather go with an internal db routine because the external C routine will be hard to maintain with most programmers locally having PL/SQL skills only (though this may be the only other valid option).
Potentially another solution is to use utl_tcp to use ftp calls to do the same thing. But unfortunately our remote server does not have a FTP server (it's a minimal windows file server).
I'm scratching my head to think of other viable solutions. Does anybody else have any suggestions that I haven't though of please?
Thanks for your help in advance.
Regards,
Chris Muir.