Home » SQL & PL/SQL » SQL & PL/SQL » using utl_SMPT.VRFY (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
using utl_SMPT.VRFY [message #333391] Fri, 11 July 2008 09:01 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have a table which is as follows:

desc ADM.WORKBOOK_RECP_LIST;

NAME                            Null?     Type
------------------------------- --------- -----
EMAIL_ID                        NOT NULL  VARCHAR2(70)
RECP_TYPE                                 VARCHAR2(2)
TIME_STAMP                                DATE

SAMPLE data looks like this: (some email ids are valid and some are invalid, we dont know which of them are 

valid)

abcd@something.com		TO
abscde@something.com		TO
asdjk@@something.com		CC
asdlkas@@something.com		TO
reta@something.com		CC
adh@something.com		CC
asd@something.com		TO



--I have this procedure that sends mails to these users, but i have been asked to send mails to

--ONLY valid users, how can i modify the bewlo procedure so as to achieve this.

/* I know that there is the UTL_SMTP.VRFY function, so my customer told me to identify all the valid email addresses
and put them into a fresh table which contains ONLY VALID addressess and then use the below procedure to send
them.*/

PROCEDURE P_SEND_WORKBOOK_MEMO(
                        p_mail_host    IN VARCHAR2,
                        p_sender       IN VARCHAR2,
                        p_recp_email   IN VARCHAR2, -- TO EMAIL addressess demarcated by ";"
                        p_cc_email     IN VARCHAR2, -- CC EMAIL addressess demarcated by ";"
                        p_documentData IN ExcelDocumentLine,
                        send_msg_status         OUT VARCHAR2)
IS
-- PURPOSE:
-- Sends Mails to all users Along with a WorkBook attached.
-- p_recp_email ---> TO List delimited by ';'
-- p_cc_email   ---> CC List delimited by ';'
--

    v_text                  VARCHAR2(20000);    -- Text/Body of Memo
    v_subject               varchar2(2000);     -- Subject of Memo
    v_error_text            VARCHAR2(10000);
    n_start                 PLS_INTEGER;
    n_end                   PLS_INTEGER;
    v_address               VARCHAR2(200);
    p_delim                 VARCHAR2(2) := ';';

    conn_rec    UTL_SMTP.Connection;

    v_Boundary  VARCHAR2(100)  := '-----ABCDEF1234567890';
    n_Index     PLS_INTEGER;

    char_lob          CLOB:='';
    chunk             VARCHAR2(4096);
    offset            PLS_INTEGER              :=  1;
    chunk_size        constant pls_integer := 4096;

   PROCEDURE write_data(p_Conn_rec  IN OUT UTL_SMTP.Connection,
                        pv_Text     IN     LONG)
   IS
   BEGIN
       utl_smtp.write_data(p_Conn_rec, pv_Text || UTL_TCP.CRLF);
   END Write_Data;

BEGIN
    v_subject   := 'Test Mail - Plan Modification Notice - '||sysdate;

    v_text      := 'Hi, '||chr(10)||chr(10);
    v_text      := v_text||'This is test Mail, Please ignore this mail'||chr(10)||chr(10);

    ADM.PKG_MEMO10.P_LOG_ERRORS(null,'Sending WORKBOOK_MEMO '||v_text, null, null, null, sysdate);

        -- Open the SMTP connection.
        Conn_rec := utl_smtp.open_connection(p_mail_host, 25);

        -- Set SMTP server, sender, and recipient(2).

        utl_smtp.helo(Conn_rec, p_mail_host);
        utl_smtp.mail(Conn_rec, p_sender);

        IF p_recp_email IS NOT NULL THEN
        n_start := 1;
        LOOP
            -- Find next delimiter.
            n_end   := INSTR(p_recp_email||p_delim,p_delim, n_start+1);
        EXIT WHEN n_end = 0;
            -- Extract the next email address.
            v_address := SUBSTR(p_recp_email||p_delim, n_start, n_end-n_start);
            v_address := LTRIM(v_address,p_delim);        -- Strip possible leading delimiters.
            v_address := LTRIM(RTRIM(v_address));         -- Strip spaces.

            -- Set SMTP recipient.
            utl_smtp.rcpt(Conn_rec, v_address);

            n_start := n_end + 1;
        END LOOP;
        END IF;

        IF p_cc_email IS NOT NULL THEN
        n_start := 1;
        LOOP
          -- Find next delimiter.
            n_end   := INSTR(p_cc_email||p_delim,p_delim, n_start+1);
        EXIT WHEN n_end = 0;
                -- Extract the next email address.
            v_address := SUBSTR(p_cc_email||p_delim, n_start, n_end-n_start);
            v_address := LTRIM(v_address,p_delim);        -- Strip possible leading delimiters.
            v_address := LTRIM(RTRIM(v_address));         -- Strip spaces.

            -- Set SMTP recipient.
            utl_smtp.rcpt(Conn_rec, v_address);

            n_start := n_end + 1;
        END LOOP;
        END IF;

        utl_smtp.open_data(Conn_rec);

        Write_Data(Conn_rec, 'From: ' || p_sender);
        Write_Data(Conn_rec, 'To: ' || p_recp_email);
        IF p_cc_email IS NOT NULL
        THEN
            Write_Data(Conn_rec, 'Cc: ' || p_cc_email);
        END IF;
        Write_Data(Conn_rec, 'Subject: ' || v_subject);
        Write_Data(Conn_rec, 'MIME-Version: 1.0');
        Write_Data(Conn_rec, 'Content-Type: multipart/mixed; boundary="' ||
                              v_Boundary || '"');
        Write_Data(Conn_rec, '');

        Write_Data(Conn_rec, '--' || v_Boundary);
        Write_Data(Conn_rec, 'Content-Type: text/plain');
        Write_Data(Conn_rec, 'Content-Transfer-Encoding: 7bit');
        Write_Data(Conn_rec, '');

        Write_Data(Conn_rec,v_text);

        Write_Data(Conn_rec, '');

        Write_Data(Conn_rec, '--' || v_Boundary);
        Write_Data(Conn_rec, 'Content-Type: application/vnd.ms-excel; name="Plan Modification Report.xls"');
        Write_Data(Conn_rec, 'Content-Transfer_Encoding: 8bit');
        Write_Data(Conn_rec, 'Content-Disposition: attachment; filename="Plan Modification Report.xls"');
        Write_Data(Conn_rec, '');

        FOR arr_index IN 1 .. p_documentData.COUNT LOOP

            Write_Data(Conn_rec,p_documentData(arr_index));

        END LOOP;

        Write_Data(Conn_rec, '');
        Write_Data(Conn_rec, '--' || v_Boundary);

        Write_Data(Conn_rec, '');
        Write_Data(Conn_rec, '');

        utl_smtp.close_data(Conn_rec);
        utl_smtp.quit(Conn_rec);

        send_msg_status := 'PASS';

        ADM.PKG_MEMO10.P_LOG_ERRORS(null,
                                    'Finished sending WORKBOOK_MEMO ',
                                    null,
                                    null,
                                    null,
                                    sysdate);
EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        send_msg_status := 'FAIL';
        utl_smtp.quit(conn_rec);
        v_error_text := 'p_recp_email '||p_recp_email||length(p_recp_email
                        ||chr(10)||chr(10)||v_text)||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
        NULL;
    WHEN OTHERS THEN
        send_msg_status := 'FAIL';
        utl_smtp.quit(conn_rec);
        v_error_text := 'p_recp_email '||p_recp_email||' length:'||
        length(p_recp_email||' :)'||chr(10)||chr(10)||v_text)||SQLERRM;
        ADM.PKG_MEMO10.P_LOG_ERRORS(null,v_error_text,null,null,null,sysdate);
END;

-- I need to finish this by ASAP, PLEASE advice as to how can i use modfiy the above code to use VRFY and validate VALID email addresses?

[Updated on: Fri, 11 July 2008 09:03] by Moderator

Report message to a moderator

Re: using utl_SMPT.VRFY [message #333395 is a reply to message #333391] Fri, 11 July 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
This problem really have NOTHING to do with Oracle per se.
It is simply an SMTP issue.
Some/many/most MTA servers have disabled VRFY command to avoid confirming addresses to spammers.
AFAIK, the only way to verify an email address is valid to send a spam/message to it.
Re: using utl_SMPT.VRFY [message #333399 is a reply to message #333395] Fri, 11 July 2008 09:38 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hmmm that sounds genuine, but can you help me in modifying the above procedure to incorporate UTL_SMTP.VRFY so that the email addressess are atleast checked ?
Re: using utl_SMPT.VRFY [message #333400 is a reply to message #333399] Fri, 11 July 2008 09:49 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I get reply code as "252" when i use both VALID and INVALID email addresses, can anyone please explain ?
Re: using utl_SMPT.VRFY [message #333401 is a reply to message #333391] Fri, 11 July 2008 10:02 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that GOOGLE is broken for you.

Results 1 - 10 of about 32,800 for smtp return code 252
Previous Topic: Creating a running counter?
Next Topic: Update Table based on Separate Query
Goto Forum:
  


Current Time: Fri Dec 09 07:49:32 CST 2016

Total time taken to generate the page: 0.09790 seconds