Help - Search - Members - Calendar
Full Version: How to create directory in trigger
Oracle DBA Forums > Oracle > Oracle Forum
Shahzad
Is there any way available to create oracle directory alias (Create or Replace Directory ...) runtime like in trigger. As when I am doing this oracle does not permits.

Waiting for kind suggestions.
dave
yes, yes you can.

But if you dont post yout trigger or error how do you expect any help?
HAL9000
You should be able to embed the "create directory aleas" command inside dynamic PL/SQL:

http://www.dba-oracle.com/t_bfile_external_lob.htm

"The BFILE datatype is used to act as a pointer or locator for the actual external data files. The BFILE datatype is a two part locator, the first part is the file name and the second part is the internal alias for the full path directory where the files reside.

You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors."


To execute the "create directory" from inside PL/SQL trigger, use the dynamic SQL "execute immediate" syntax:

http://www.dba-oracle.com/t_execute_dynami..._procedures.htm

PROCEDURE create_dir IS
BEGIN
MY_DIR:='/u01/app/oracle/mydir';
EXECUTE IMMEDIATE
'CREATE DIRECTORY MY_DIR '||
' AS '''||my_dir||'''';
END create_dir;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.