pondelok, 26 august 2013 11:26 Written by 2448 times
Rate this item
(3 votes)

PL/SQL - Send email

Mnohí programátori v PL/SQL prišli už do štádia, keď potrebovali zo svojich aplikacii posielať mail či už v podobe alertu alebo iba infa uživateľom priamo z PL/SQL kódu. Napíšeme si veľmi jednoduchú "storovanu funkciu", ktorou bude možné posielať jednoduchý mail.

Podmienky funkčnosti kódu:
- funkciu som testoval na Oracle serveri: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
- databázovy server musi mať sprístupneny poštový server a port, cez ktorý posiela maily
- Uživatel prihlásený do databázy musí mať nagarantovanú "package" UTL_SMTP
- troška komplikácie: existencia tabuľky USERS v schéme, v ktorej píšeme kód. Tabuľka USERS musí mať stlpček USER_MAIL, v ktorom je adresa príjemcu, ktorému má byť odoslaný mail. Samozrejme, že celá tato vetva podmienky s kódom sa dá vynechať a v tom prípade musíte zadávat adresu príjemcu v parametri v_Recipient_par  
Poďme na to.

Tu je zdrojový kód funkcie na odosielanie mailov:

CREATE OR REPLACE 
FUNCTION Send_email(v_Subject_par in varchar2, 
                    v_Msg_par in varchar2, 
                    v_Error in out varchar2,
                    v_Recipient_par in varchar2 := '') RETURN BOOLEAN IS
v_Mail_From CONSTANT VARCHAR2(50) := 'programator(zavinac)firma.sk';        
    v_Mail_Host CONSTANT VARCHAR2(20) := '10.1.2.3';
    v_Mail_Port CONSTANT NUMBER := 25;
    crlf CONSTANT VARCHAR2(2) := chr(13) || chr(10);
    v_Mail_Conn UTL_SMTP.CONNECTION;
BEGIN
    v_Error := NULL;
    IF v_Recipient_par IS NULL THEN
        FOR USER_REC IN (SELECT USER_EMAIL FROM USERS)
        LOOP
            BEGIN
                v_Mail_Conn := UTL_SMTP.OPEN_CONNECTION(v_Mail_Host, v_Mail_Port);
            EXCEPTION
                WHEN OTHERS THEN
                    v_Error := 'Function Send_email:' || SQLERRM;
                    RETURN FALSE;
            END;                    
            UTL_SMTP.HELO(v_Mail_Conn, v_Mail_Host);
            UTL_SMTP.MAIL(v_Mail_Conn, v_Mail_From);
            UTL_SMTP.RCPT(v_Mail_Conn, USER_REC.USER_EMAIL);
            UTL_SMTP.DATA(v_Mail_Conn,
                          'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
                          'From: '   || v_Mail_From || crlf ||
                          'Subject: '|| v_Subject_par || crlf ||
                          'To: '     || USER_REC.USER_EMAIL || crlf || crlf ||
                          v_Msg_par 
                        );
            UTL_SMTP.QUIT(v_mail_conn);          
        END LOOP;
    ELSE
        BEGIN        
            v_Mail_Conn := UTL_SMTP.OPEN_CONNECTION(v_Mail_Host, v_Mail_Port);
        EXCEPTION
            WHEN OTHERS THEN
                v_Error := 'Function Send_email: ' || SQLERRM;
                RETURN FALSE;
        END;                
        UTL_SMTP.HELO(v_Mail_Conn, v_Mail_Host);
        UTL_SMTP.MAIL(v_Mail_Conn, v_Mail_From);
        UTL_SMTP.RCPT(v_Mail_Conn, v_Recipient_par);
        UTL_SMTP.DATA(v_Mail_Conn, 'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
                                   'From: '   || v_Mail_From || crlf ||
                                   'Subject: '|| v_Subject_par || crlf ||
                                   'To: '     || v_Recipient_par || crlf || crlf ||
                                   v_Msg_par --|| crlf || crlf || ' from ' || v_Mail_Host
                     );
        UTL_SMTP.QUIT(v_mail_conn);        
    END IF;    
        
    RETURN TRUE;
EXCEPTION
    WHEN UTL_SMTP.INVALID_OPERATION THEN
        v_Error := 'Invalid Operation in Mail attempt using UTL_SMTP. (Function: Send_email)';
        RETURN FALSE;
    WHEN UTL_SMTP.TRANSIENT_ERROR THEN
        UTL_SMTP.QUIT(v_mail_conn);
        v_Error := 'Temporary e-mail issue - try again. (Function: Send_email)'; 
        RETURN FALSE;
    WHEN UTL_SMTP.PERMANENT_ERROR THEN
        UTL_SMTP.QUIT(v_mail_conn);
        v_Error := 'Permanent error has encountered. (Function: Send_email)';
        RETURN FALSE;
    WHEN OTHERS THEN
        v_Error := 'Function Send_email:' || SQLERRM;
        RETURN FALSE;   
END

 Túto plsql storovanú funkciu treba v danej schéme preložiť a zvalidniť. Funkcia ma tieto vstupné parametre:
- v_Subject_par in varchar2: subject alebo predmet mailu
- v_Msg_par in varchar2: text správy,  
- v_Error in out varchar2: vystupný parameter, v ktorom je uložená hláška v prípade, že funkcia z akehokoľvek dôvodu zlyhá
- v_Recipient_par in varchar2: vstupný parameter - mailova adresa konkrétneho príjemcu, ktorému má byť mail odoslaný. V prípade, že je prázdny, prečítajú sa všetci príjemcovia (stlpec "USER_EMAIL") z tabuľky USERS 
 
Návratová hodnota je typu BOOLEAN, ak je "true", mail bol úspešne odoslaný, v opačnom prípade je "false" a vo výstupnom parametri "v_Error" je text chybovej hlášky.
 
V tele funkcie sa nachádzajú konštanty, ktoré si musíte podľa Vašich nastavení zmeniť:
    "v_Mail_From" CONSTANT VARCHAR2(50) := Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebujete mať nainštalovaný JavaScript.'; // odkial je mail posielaný        
    "v_Mail_Host" CONSTANT VARCHAR2(20) := '10.2.1.3';       // IP mailoveho servera   
    "v_Mail_Port" CONSTANT NUMBER := 25;                           // port, cez ktorý komunikuje DB server s mailovym serverom
 
Teraz si napíšeme jednoduchý kód na odoslanie mailu.
Pošleme jednoduchý mail (pozdrav) príjemcovi "Táto e-mailová adresa je chránená pred spamovacími robotmi. Na jej zobrazenie potrebujete mať nainštalovaný JavaScript.":
 

SET SERVEROUTPUT ON
DECLARE
  v_Error VARCHAR2(1000);
  v_Result BOOLEAN;
BEGIN
    v_Result := Send_email('Pozdrav z www.projectik.eu','Pozdravujeme Vas!',v_Error,'ferko.mrkvicka(zavinac)server.com');  
    IF v_Result THEN
        DBMS_OUTPUT.PUT_LINE('Mail bol uspesne odoslany.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Mail sa nepodarilo odoslat: '||v_Error);
    END IF;
END

Hotovo. Predpokladám, že programátor má aspoň základné databázove znalosti administrácie ORACLE db a v programovaní v ORACLE SQL a PL/SQL. S pozdravom AB:)

Last modified on streda, 17 september 2014 11:03
Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 20 rokov.



  • prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal):  2 roky
  • na VŠ - jazyky Turbo pascal a Turbo C: 1rok
  • programovanie databázového ekonomického širokoškálneho software v Delphi a C++ Borland (databázy Paradox): 8 rokov v Codex s.r.o. Nitra
  • programovanie webových aplikácií na databázach MySQL a MS SQL server: 1 roky na živnosť (firmy: Hermes Nitra, Schindler v Bratislave)
  • pracoval ako DBA v Homecredit Brno a vo VÚB Bratislava ako vyvojár + optimalizácie v SQL a PL/SQL na Oracle 11g (firma Accenture)
  • programoval programy pre súkromné firmy pre HW na skenovanie povrchov nádrží pre skladovanie tekutých palív (pristroj Leica)
  • momentálne pracuje ako DBA pod Oracle 11g a ako vyvojár vnútropodnikových databázových aplikacií v PHP, SQL, C++, C# a interface v PL/SQL: 8 rokov
  • ďalej programuje s HTML, CSS, Ajax, Javascript, VBA, MS visual C++ a C# malé podporné aplikácie
  • certifikát SQL expert for Oracle 11g   
  • spolupracuje a aktívne učí pre počítačové firmy: Lapis, IVIT v Nitre