In Oracle PL/SQL how do I create a directory on the file system?

Question

Hi

how do you create a physical directory on the OS from within PL/SQL? I looked at the "CREATE OR REPLACE DIRECTORY" command but that doesn't do it. Neither does UTL_FILE appear to do it. This would be in Oracle 10g and upwards.

thanks

Answer 1

I believe the only way to do this is to use an external procedure (C or Java) and call it through PL/SQL. PL/SQL itself does not have the means to create the physical OS directory.

PL/SQL Tips provides a good example of how to create a C external procedure that executes shell commands. Note that I would not consider it best practice to allow this for security reasons.

It you can create the directory first, then you can use the

create or replace directory myDir as '<path-to-dir>/myDir';

Note that you will need to have the CREATE ANY DIRECTORY privilege assigned to the user executing the command. After the directory is created with the command above, be sure to assign any needed privileges on the directory to other users.

grant read, write on directory myDir to testUsers;

Answer 2

UTL_FILE still lacks this capability - probably a holdover from the pre- DIRECTORY object days where you had to explicitly define the OS file directories you could access in a startup parameter, so there was no need to create directories dynamically anyway.

I think the easiest way to do this is with an Oracle Java stored procedure that uses:

 File f = new File(dirname);
 return (f.mkdir()) ? 1 : 0;

If you go this route make sure that you use dbms_java.grant_permission to grant 'java.io.FilePermission' to the user that owns the executing code.

Answer 3

I just checked the new docs for database version 11.2, and there's still no routine I can find to create a directory. So, like the other respondents, I recommend using a Java or C routine.

Answer 4

Thanks for the replies - in the end I did find an easier solution. Use select os_command.exec('mkdir /home/oracle/mydir') from dual;

or simply x := os_command.exec('mkdir /home/oracle/mydir');

Works well!