Attach files with email PL/SQL
1) If we want to attach a file inside a directory on the server, it is mandatory to use an Oracle directory previously created. We can not reference a server directory directly.
For example: if we have to read files that are in the path / images , we will have to create the directory previously in this way:
CREATE OR REPLACE DIRECTORY images_dir AS ‘/ images’
To read the files from our programs, it is necessary to grant the permission to the user (being user the user will read the attached):
GRANT READ ON DIRECTORY images_dir TO user
You must also have the CREATE ANY DIRECTORY system privilege to create an Oracle directory .
2) On the other hand, the directory to read (/ images in this case) must exist in the database server and have at least the corresponding read permission, as well as the files it contains. If not, the procedure of sending emails will not be able to read them. How this is done depends on the operating system of the server.
3) If initially you do not have direct access to the server but you want to make tests, it is possible to create with PLSQL a file with trivial information to use it from your programs. Something like:
– Add write permission to the Oracle directory, if not
GRANT WRITE ON DIRECTORY images_dir TO user
– Create a test file with the text Test text
v_buffer VARCHAR2 (255);
– define output directory
l_output: = UTL_FILE.fopen (‘IMAGES_DIR’, ‘test.txt’, ‘a’);
UTL_FILE.PUT_LINE (l_output, ‘Test text’);
– With this I make sure that the file already has it
l_output: = UTL_FILE.fopen (‘IMAGES_DIR’, ‘test.txt’, ‘r’);
UTL_FILE.GET_LINE (l_output, v_buffer);
Now it is already possible to perform a test with attachments:
v_error VARCHAR2 (1024);
v_attachs (0) .mimetype: = ‘text / plain’;
v_attachs (0) .filename: = ‘test.txt’;
v_attachs (0) .DIRECTORY: = ‘IMAGES_DIR’;
v_ret: = Pkg_Emails.send_mail_with_attachments (
pe_sender => ‘MyEmail @ MyDomain’,
pe_recipients => ‘SenderEmail @ YourDomain’,
pe_subject => ‘
pe_message => ‘Text of the test with attachment’,
pe_attachments => v_attachs,
ps_replies => v_replies); – There has been some error
IF v_replies.COUNT> 0 THEN DBMS_OUTPUT.PUT_LINE (v_replies (0) .text); END IF; END; /
A detail that may seem trivial but can give us many headaches if we do not do well: if we have created the Oracle directory using an identifier without using double quotes, it is mandatory to refer to it, within our programs, in uppercase.