web analytics

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 
DECLARE 
    l_output UTL_FILE.file_type; 
    v_buffer VARCHAR2 (255); 

BEGIN 
    – define output directory 
    l_output: = UTL_FILE.fopen (‘IMAGES_DIR’, ‘test.txt’, ‘a’); 
    UTL_FILE.PUT_LINE (l_output, ‘Test text’); 
    UTL_FILE.fflush (l_output); 
    UTL_FILE.fclose (l_output); 

    – 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);
    UTL_FILE.fclose (l_output); 

    DBMS_OUTPUT.PUT_LINE (v_buffer); 
END; 
/

Now it is already possible to perform a test with attachments: 
DECLARE 
    v_ret NUMBER; 
    v_error VARCHAR2 (1024); 

    v_attachs Pkg_Emails.t_attachments; 
    v_replies UTL_SMTP.replies; 

BEGIN 
    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. 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!