Oracle create directory command
Now that Oracle run outside the database, OS file directories needs
to be created and managed. The Oracle docs note the syntax for the Oracle
create directory statement as follows:
CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name. Database-unique directory
name.
path_name.
Operating system directory path
(note that if the OS is case-sensitive, the name must be exact)
When using the Oracle create directory statement, a
directory is created inside a single namespace. The directory created
with the Oracle create directory statement is not owned by a schema.
Therefore, the directory names used when initializing the Oracle
create directory statement must be unique across the entire database. You grant
access to the BFILEs or external files in a specific directory by
granting READ access to the users or roles that require access.
Oracle create directory file permission
When the
Oracle create directory statement is used to create a directory, the
user that issued the Oracle create directory statement automatically
receives the READ grant with the admin option so it can be subsequently
granted to others.
If you initialized the Oracle create directory statement, you will
automatically have read and write privileges on that directory. Upon
issuing the Oracle create directory statement, the issuer can grant these privileges to other users and
roles. The DBA also has the ability to grant read and write privileges
to users.
The following is an example of the Oracle CREATE DIRECTORY
statement:
The Oracle create directory statement below will
associate the directory internal Oracle alias g_vid_lib with the
directory '/video/library/g_rated'.
CREATE
OR REPLACE DIRECTORY g_vid_lib AS '/video/library/g_rated';
The path name used in the Oracle create
directory statement must be a full path and not use any special
characters. The existence of the directory created by the Oracle
create directory statement is not validated until the directory alias is
referenced by Oracle.
There is also a create_directory package. Here is a
working example of creating a directory in Oracle:
--CREATES A DIRECTORY IN
A SPECIFIC OS LOCATION AND GRANTS PRIVS
CREATE OR REPLACE PACKAGE CREATE_DIRECTORY AS
PROCEDURE
createdirectory(directory_name IN VARCHAR2, directory_path
IN VARCHAR2);
END create_directory;
/
CREATE OR REPLACE PACKAGE BODY CREATE_DIRECTORY as
PROCEDURE
createdirectory(directory_name IN VARCHAR2, directory_path
IN VARCHAR2) IS
l_exec_string VARCHAR2(1024):=
'CREATE OR REPLACE DIRECTORY ';
l_directory_name_stripped VARCHAR2(1024);
l_directory_name_dstripped
VARCHAR2(1024);
l_directory_name_validated VARCHAR2(1024);
l_directory_validated VARCHAR2(1024);
BEGIN
l_directory_name_stripped :=
REPLACE(directory_name,'''','');
l_directory_name_dstripped := REPLACE(l_directory_name_stripped,'"','');
l_directory_name_validated := DBMS_ASSERT.simple_sql_name(l_directory_name_dstripped);
l_directory_validated
:= REPLACE(directory_path,'.','');
IF instr(l_directory_validated,'/u01/thisismypath')
= 1
THEN
l_exec_string := l_exec_string||l_directory_name_validated
||' AS
'||''''||l_directory_validated||''''
;
EXECUTE IMMEDIATE (l_exec_string);
l_exec_string := 'GRANT
READ, WRITE ON DIRECTORY
'||l_directory_name_validated ||' TO '||user;
EXECUTE IMMEDIATE (l_exec_string);
END IF;
END createdirectory;
END create_directory;
/
EXEC CREATE_DIRECTORY.createdirectory('PAULSDIR2','/u01/thisismypath');
|