| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CREATE OR REPLACE DIRECTORY "testdir1" AS 'c:/test';
Is this what you are looking for?
SQL> create or replace directory testdir as 'd:\temp';
Directory created.
SQL> select * from all_directories where DIRECTORY_NAME='TESTDIR';
OWNER DIRECTORY_NAME ------------------------------ ------------------------------DIRECTORY_PATH
SYS TESTDIR
There is no USER_DIRECTORIES. Looks like all directory objects are owned by SYS.
mmontreaux_at_hotmail.com (Monty) wrote in message news:<6284dd3.0305160003.6cc8c5af_at_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 - 10:24:24 CDT
 |
 |