Script to create an Oracle directory entry in dba_directories table

Any file in any directory accessible to the user can be attached (not just the directories listed for the utl_file_dir parameter in the init.ora file).

HOWEVER, if we are using this to send e-mail's with attached files (or reading the message text or message html from a file) from a trigger or from some other SQL that you can't or don't want to have a commit done, you will first need to create an Oracle directory entry for the directory containing the attached files and grant read access to it to public, such as:


DECLARE
l_directory_name VARCHAR2 (2000);
l_directory_path VARCHAR2 (2000);
BEGIN
l_directory_name := 'CESDIR072917';
l_directory_path :=
'/dba/u01/app/applmgr/common/xxx/admin/out/xxx';

EXECUTE IMMEDIATE 'create or replace directory '
|| l_directory_name
|| ' as '''
|| l_directory_path
|| '''';

EXECUTE IMMEDIATE 'grant read on directory '
|| l_directory_name
|| ' to public';

COMMIT;
END;
/


In this script the local variable l_directory_name and l_directory_path are used to define the directory name and the directory path from which you want to access the file respectively.