Make Directory Part Dynamic In Call To BFILENAME f

Question

I am trying to open an existing physical file from file system in a procedure in Oracle 10g. The bfilename function requires a directory and path. I want to pass on the directory such as "c:\abc" directly to the procedure, i.e. I don't want to pass a directory object. The reason is, the directory can change, and I don't know how to create or replace directory inside a procedure (the command always returns error saying that variable is not allowed). I also am not sure about how it works in a multi-user environment, because a new directory is not local to the procedure.

When I run the command:

bfilename('c:\abc', 'myfile.txt');

it returns error that directory do not exists. I have checked by ending directory with "\" i.e. make it "c:\work\". I have also checked by capitalizing the directory name inside the procedure. If I make a directory object say DOCUMENTS and pass it to the bfilename, then its working.

bfilename('DOCUMENTS', 'myfile.txt');

Is there some way to make the directory part dynamic?

Update: I have tried to create directory from inside the procedure because this msdn article says that directory-object is must. The code is as follows:

EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY WORKDIR AS ''c:\work''';

The procedure compiled successfully but when run gives following error:

Insufficient privileges.

I have only one user in my test database, this user has sysdba role. I have physical access to file through file system. The database-user can create directory through sqlplus.

Answer 1

Try granting the 'CREATE DIRECTORY' privilege directly to the user (i.e., not through a role).

Something like:

grant create directory to the_user;

The reason for that is that roles are disabled inside of stored procedures. So, your user needs a direct grant explicitly granted.

Hope that helps.

Answer 2

Mark's answer solves your specific problem (you cannot use privileges gained through the SYSDBA role in procedures) but I want to discuss the underlying issues.

The relevant privilege is CREATE ANY DIRECTORY. All directory objects are owned by SYS, which is why this is a privilege which should be granted with caution.

Bear in mind that the second time you run your stored procedure it will fail. This is because the directory already exists. There is no ALTER DIRECTORY syntax. This means you need to drop and re-create the directory every time you want to change its path. Consequently you will also need to grant DROP ANY DIRECTORY to the user. Also, in a production environment you would need to re-issue the privileges granted on that directory to any other users who need it.

---

Why do Oracle make it so hard to work dynamically with directory objects? Because we shouldn't need to do it.

The OS directory structure is full of potential dangers for the database. Access to OS files from inside the database should be strictly controlled. That means we should specify particular directories for known purposes (DataPump, writing dumps and logfiles, importing CSV files, etc) and stick to them. Allowing procedures to change the paths of directories on the fly is a red flag for bad business process.

But, sometimes directory objects are a real pain. For instance, I once worked on a system which generated millions of files. In order to spread them across the operating system without blowing the unix inode limit we had a tree structure based on the last two digits of the header ID and the penultimate two digits of the header ID. Some like this:

$OUT_FILES/whatever/00/00
$OUT_FILES/whatever/00/01
...
$OUT_FILES/whatever/99/98
$OUT_FILES/whatever/99/99

That would have been ten thousand directory objects per feed. Which is a lot. So we used the (deprecated) UTL_FILE_DIR parameter. This parameter is deprecated for at least three reasons:

1. It is set in the INIT.ORA file and changing it requires a database re-start.
2. There is no granularity. Every database user has privileges on any OS directory in the list
3. The security is laxer because it allows wildcards.

Also, it means we have to specify the full OS path whenever we need to read or write to a directory, which is fragile and error-prone.

However, if you're working on a toy database this parameter might solve your headache. Find out more.

Just to be clear, I am not recommending that anybody use this parameter in a proper database unless they really understand the limitations. In most situations directory objects are safer and more convenient.