CREATE OR REPLACE DIRECTORY "testdir1" AS 'c:/test';

From: Monty <mmontreaux_at_hotmail.com>
Date: 16 May 2003 01:03:44 -0700
Message-ID: <6284dd3.0305160003.6cc8c5af@posting.google.com>

Hi, according to the Oracle docs A96540-1, "Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server's file system where external binary file LOBs (BFILEs) and external table data are located. You can use directory names when referring to BFILEs in you PL/SQL code and OCI calls, rather than hard coding the operating system path name, thereby providing greater file management flexibility."

I have followed this advice and successfully <b>created</b> and <b>used</b> a DIRECTORY object for loading LOBs into a table, with something like the following. It all works.

CREATE OR REPLACE DIRECTORY "testdir1" AS 'c:/test';

CREATE OR REPLACE PROCEDURE loadfileintodatabase(filename VARCHAR2) AS   l_bfile BFILE;
  BEGIN
   l_bfile := BFILENAME('testdir1',filename);
--other stuff inc. DBMS_LOB.LOADFROMFILE(...)
  END;
END;
/

EXEC loadfileintodatabase('test.dat');

But my problem is that I will be loading files into the database from <b>multiple</b> DIRECTORY names, not just "testdir1".

So I need to change or overload the procedure to something like and pass in the DIRECTORY object or its name.

--**A**
CREATE OR REPLACE PROCEDURE loadfileintodatabase(directoryname DIRECTORY,filename VARCHAR2) AS
 BEGIN
--put the stuff above in here and somehow extract "testdir1"
--(and "testdir2" and "testdir3") from the different
--directoryname objects passed to the procedure.
 END;
/

or to

--**B**
CREATE OR REPLACE PROCEDURE loadfileintodatabase(directoryname VARCHAR2,filename VARCHAR2) AS
 BEGIN
--put the stuff above in here and change the line
--l_bfile := BFILENAME(directoryname,filename)
 END;
/

So, at last, my question is HOW can I get the directory name from a DIRECTORY object I have created, in otherwords how do I get 'c:/test' given "testdir1". The following is a contrived SQL session showing what I want. Don't think it works, it doesn't, it just represents what I want.

SQL> 
SQL> 
SQL> CREATE OR REPLACE DIRECTORY "testdir1" AS 'c:/test';

Directory created.

SQL> SELECT "testdir1".name FROM DUAL;

c:/test

SQL>
SQL> Here is the Oracle version etc, but an implementation for any Oracle version, O/S, or even hints will do.
Thank you
Monty

SQL> 
SQL> 
SQL> SELECT * FROM V$VERSION;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production

SQL> Received on Fri May 16 2003 - 03:03:44 CDT