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

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Fri, 16 May 2003 13:46:10 +0400
Message-ID: <ba2c3e$apo$1@babylon.agtel.net>

>
> 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.
>

Maybe I am missing something, but why do you think you need to know which actual file system directory the DIRECTORY object maps to?

I am pretty sure that

>
> --**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;
> /
>

would do. You'll pass in the name of the DIRECTORY object and the file name, BFILENAME() will automagically resolve the directoryname to the actual directory it maps and load the file from there (provided that Oracle owner has access to that directory - it is usually LocalSystem on Windows and thus has access to the whole file system.) Here's the actual run on my 9.2.0.3/Win2k test db:

SQL> CREATE OR REPLACE DIRECTORY DIR1 AS 'c:\test1';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY DIR2 AS 'c:\test2';

Directory created.

SQL> CREATE OR REPLACE FUNCTION
  2 LOADTHEFILE( P_DIR IN VARCHAR2,
  3 P_FN IN VARCHAR2 )
  4 RETURN CLOB
  5 AS
  6 L_BFILE BFILE;
  7 RV CLOB;
  8 BEGIN
  9 DBMS_LOB.CREATETEMPORARY(RV, TRUE); 10 DBMS_LOB.OPEN(RV, DBMS_LOB.LOB_READWRITE); 11 L_BFILE := BFILENAME(P_DIR, P_FN);

 12    DBMS_LOB.OPEN(L_BFILE, DBMS_LOB.LOB_READONLY);
 13    DBMS_LOB.LOADFROMFILE(RV, L_BFILE, DBMS_LOB.GETLENGTH(L_BFILE));
 14    DBMS_LOB.CLOSE(L_BFILE);

15 RETURN RV;
16 END;
17 /

Function created.

SQL> sho err
No errors.

SQL> 
SQL> VAR C CLOB;
SQL> SET LONG 1000
SQL> EXEC :C := LOADTHEFILE('DIR1','TEXT.TXT');

PL/SQL procedure successfully completed.

SQL> PRINT C C

This is text from DIR1 (c:\test1)

SQL> EXEC :C := LOADTHEFILE('DIR2','TEXT.TXT'); PL/SQL procedure successfully completed.

SQL> PRINT C C

This is text from DIR2 (c:\test2)

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.

Received on Fri May 16 2003 - 04:46:10 CDT