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');

 

---