CODEWITHSUNDEEP
oracle-databaseplsqlutl-file
mr anto
mr anto

Reputation: 25

Oracle UTL_FILE. how to create OS directory

I should store some files from DB table into server directories. the repository tree should be structured as follow

\\server\REPOSITORY\[FOLDER]\[list of files...]

where [FOLDER] name and list of files are retrieved from DB. so, I used the UTL_FILE tool to create my files

F_OUT := UTL_FILE.FOPEN('\\SERVER\REPOSITORY\FOLDER1', 'FILE1.JPG', 'WB',32760);

but this works only if \\SERVER\REPOSITORY\FOLDER1 already exists on server. how can I create directory if it does not exist? (note that \\SERVER\REPOSITORY\ are a simple path, not a oracle directory)

Upvotes: 0

Views: 436

Answers (1)

Paul W
Paul W

Reputation: 11603

Yes, you can do this but it'll require some work and may not be worth it.

Approach #1:

  1. Create a C library (.so or .dll) containing a function that will do the directory creation work, or that wraps an OS call to a shell script to do so. Register this as a library in Oracle and create an external procedure for the function. You'll need to look at docs and examples of "external procedures" or "extprocs" for specifics, too much to put here.
  2. A regular stored procedure can be called to (a) call the extproc to create the directory on the host, and then (b) use EXECUTE IMMEDIATE to create a directory object for it in Oracle and grant the needed permissions.

Approach #2

  1. Create an external table and use the PREPROCESSOR attribute to call a shell script on the database host. That shell script can do the directory creation work and pass back a dummy value to make the external table happy. Lookup docs and examples on "external tables" for more info. This feels like a hack but it's simpler than writing C code and messing with extprocs.

  2. Have a stored procedure dummy query this external table, which will execute the shell script. Then have it use EXECUTE IMMEDIATE to create a directory object for it in Oracle and grant the needed permissions.

There are other options too, but they get even more complicated. It's awkward for a database process to reach outside the database to the OS layer. Consider rearchitecting completely and driving your process from the OS instead of from within the database. That'll be a lot easier to work with. Plus it'll save you from getting into trouble with RAC down the road, or having to mount shared storage to overcome the multiple-host nature of RAC (where your files, library and/or scripts have to be available on any host, as you'll be on whichever host the listener or your job scheduler connected you too, not the same one every time). This is a major reason why these methods aren't in common use, aside from security concerns that usually requires a specialized listener and separate unprivileged OS account to mitigate. I'm speaking about Unix here, I don't know how it would work on Windows.

But you asked, and this is a workable solution if you must drive this from within PL/SQL.

Upvotes: 3

Related Questions