[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

automatic email alert

hi experts,

i have two tables. in table1 i have two columns (store,email),in table2 i have two columns (store,logs). In table1 ,for one particular store we have only one email id associated. but in table2 with one particular store we will have multiple logs. What i need to do is i need to send an email to that email id with which the store is attached in table1 and in the body of that email i will have the names of the logs associated with that store in table2. I need to use plsql query for this. please help me with this issue. i have attached the screenshorts of demo tables.

thanks.
table2.PNG
table1.PNG
0
sam shah
Asked:
sam shah
1 Solution
 
sdstuberCommented:
Do you need to send the file names or the contents of those files?

If the contents, where are the files?
And, should the contents be written into the body of the email, or sent as attachments?


If you only need to send the file names, then something like this should work...

DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    FOR e IN (SELECT e.store, e.email
                FROM store_emails e
               WHERE e.store IN (SELECT l.store
                                   FROM store_logs))
    LOOP
        v_connection := UTL_SMTP.open_connection('your.email.server.com');
        UTL_SMTP.helo(v_connection, 'your.domain.com');
        UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
        UTL_SMTP.rcpt(v_connection, e.email);
        UTL_SMTP.open_data(v_connection);

        UTL_SMTP.write_data(v_connection, 'From: Your Name' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'To: ' || e.email || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, 'Subject: Store Logs' || UTL_TCP.crlf);
        UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

        -- This is easy and likely sufficient for short emails
        -- but, if your email body becomes large
        -- you may want to construct it as a clob before connecting to the email server
        -- then write the clob into the email body so you don't get timeouts
        FOR l IN (SELECT logs
                    FROM store_logs l
                   WHERE l.store = e.store)
        LOOP
            UTL_SMTP.write_data(v_connection, l.logs || UTL_TCP.crlf);
        END LOOP;

        UTL_SMTP.close_data(v_connection);
        UTL_SMTP.quit(v_connection);
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now