1 / 16

By John Missinne McKee Foods Collegedale, TN

By John Missinne McKee Foods Collegedale, TN. Sending Email from PL/SQL. Custom Programs. Fun with SMTP. From http://marcel.wanda.ch/Fun/SMTP “Hitchhiker’s Guide to SMTP” Last known to work in April of 1996: % telnet mail.germany.eu.net smtp Trying 192.76.144.65 ...

samuel-mejia
Download Presentation

By John Missinne McKee Foods Collegedale, TN

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. By John Missinne McKee Foods Collegedale, TN Sending Email from PL/SQL Custom Programs

  2. Fun with SMTP From http://marcel.wanda.ch/Fun/SMTP “Hitchhiker’s Guide to SMTP” Last known to work in April of 1996: % telnet mail.germany.eu.net smtp Trying 192.76.144.65 ... Connected to mail.germany.eu.net. Escape character is '^]'. 220-mail.Germany.EU.net - EUnet gateway to Germany 220 ESMTP spoken here EHLO nice.ethz.ch 250-mail.Germany.EU.net Hiya nice.ethz.ch [129.132.66.13], long time no see 250-SIZE 20000000 250 HELP MAIL from: waldvogel@nice.ch SIZE=335 250 waldvogel@nice.ch... A real hoopy frood. [...] . 250 QAA01569 Message accepted for delivery RSET 250 Reset state: 38911 BASIC BYTES FREE. QUIT 221 So long, and thanks for all the fish.

  3. Oracle Provides SMTP From Oracle’s UTL_SMTP Package specification comments… A SMTP connection is initiated by a call to open_connection, which returns a SMTP connection. After a connection is established, the following calls are required to send a mail: • helo() - identify the domain of the sender • mail() - start a mail, specify the sender • rcpt() - specify the recipient • open_data() - start the mail body • write_data() - write the mail body (multiple calls allowed) • close_data() - close the mail body and send the mail The SMTP connection is closed by calling quit().

  4. UTL_SMTP Example From Oracle’s UTL_SMTP Package specification comments… DECLARE c utl_smtp.connection; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF); END; BEGIN c := utl_smtp.open_connection('smtp-server.acme.com'); utl_smtp.helo(c, 'foo.com'); utl_smtp.mail(c, 'sender@foo.com'); utl_smtp.rcpt(c, 'recipient@foo.com'); utl_smtp.open_data(c); send_header('From', '"Sender" <sender@foo.com>'); send_header('To', '"Recipient" <recipient@foo.com>'); send_header('Subject', 'Hello'); utl_smtp.write_data(c, utl_tcp.CRLF || 'Hello, world!'); utl_smtp.close_data(c); utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN NULL; -- When the SMTP server is down or unavailable, we don't -- have a connection to the server. The quit call will -- raise an exception that we can ignore. END; raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); END;

  5. More complex Email? • Set Message Priority • Send HTML Text • Send Attachments • A simple, common email send api.

  6. Possible Email solutions • Metalink – Complex PL/SQL sample packages. I spent a lot of time trying to figure out the package instead of figuring out SMTP. • Ask Tom – Java Mail Client. The java was not trivial to install, was horribly slow, and then was still complicated to use. • Metalink – Simple UTL_TCP example. This simple UTL_TCP example showed a simple SMTP interaction including an attachment. We took the simple UTL_TCP example, a Mime reference, and a little bit of LOB research and created MFC_Mail.

  7. MFC_Mail (core program) vConn := utl_tcp.open_connection(cServer, 25); -- Establish Connection with Email Server bb := utl_tcp.write_line(vConn, 'HELO '||cServer); -- Shake Hands with SMTP bb := utl_tcp.write_line(vConn, 'EHLO '||cServer); -- Shake Hands with ESMTP (Extended SMTP) bb := utl_tcp.write_line(vConn, 'MAIL FROM: '||vFrom); -- Specify Sender Address(pTo); -- Call Internal procedure that Send RCPT Messages for all recipients in a comma separated list Address(pCC); -- Call Internal procedure that Send RCPT Messages for all recipients in a comma separated list Address(pBCC); -- Call Internal procedure that Send RCPT Messages for all recipients in a comma separated list bb := utl_tcp.write_line(vConn, 'DATA'); -- Start Body of Email bb := utl_tcp.write_line(vConn, 'Date: '||to_char( sysdate,'dd Mon yy hh24:mi:ss')); -- Send MIME Timestamp bb := utl_tcp.write_line(vConn, 'From: '||vFrom); -- Send MIME From bb := utl_tcp.write_line(vConn, 'MIME-Version: 1.0'); -- Send MIME Version bb := utl_tcp.write_line(vConn, 'To: '||pTo); -- Send MIME Address Info if pCC is not null then bb := utl_tcp.write_line(vConn, 'Cc: '||pCC); end if; -- Send MIME CC Info if pBCC is not null then bb := utl_tcp.write_line(vConn, 'Bcc: '||pBCC); end if; -- Send MIME BCC Info bb := utl_tcp.write_line(vConn, 'Subject: '||pSubject); -- Send MIME Subject if pImportance IN ('High', 'Medium', 'Low') then -- Set Importance bb := utl_tcp.write_line(vConn, 'Importance: '||pImportance); end if; bb := utl_tcp.write_line(vConn, 'X_Mailer: '||MailerSig); -- Send Mailer Signature if pAttachments.Count = 0 then -- If Simple Mail, no attachments bb := utl_tcp.write_line(vConn, 'Content-Type: text/'||pBodyMType||nl); -- Set Content Type bb := utl_tcp.write_line(vConn, vBody); -- Send Body Else -- Else bb := utl_tcp.write_line(vConn, 'Content-Type: '||Mixed||nl); -- Set Multipart Mixed Content Type bb := utl_tcp.write_line(vConn, '--'||MixBndry); -- Send Mixed Boundry if rCount > 0 then -- If Related Attachments exist bb := utl_tcp.write_line(vConn, 'Content-Type: '||Related||nl); -- Set Multipart Related Content Type bb := utl_tcp.write_line(vConn, '--'||RelBndry); -- Send Related Boudry end if; bb := utl_tcp.write_line(vConn, 'Content-Type: text/'||pBodyMType); -- Send Body bb := utl_tcp.write_line(vConn, vBody||nl); if rCount > 0 then SendAttachments(pAttachments, RelBndry, True); end if; -- If Related Attachments exist, send them SendAttachments(pAttachments, MixBndry, False); -- Send General (Non Related) Attachments end if; bb := utl_tcp.write_line(vConn, '.'); -- Send SMTP Close bb := utl_tcp.write_line(vConn, 'QUIT'); -- Say Goodbye GetFeedback; utl_tcp.close_connection(vConn); -- Close Connection

  8. MFC_Mail (Attachments) SendAttachments -- Send Attachments in Attachment list, use the given boundary. procedure SendAttachments(pAttachments in Attachment_List, pBndry in varchar2, pRFlag Boolean default False) is aCtr integer; begin for aCtr in 1 .. pAttachments.count loop if NVL((lower(substr(pAttachments(aCtr).cType,1,1)) = 'r'),False) = pRFlag then bb := utl_tcp.write_line(vConn, '--'||pBndry); bb := utl_tcp.write_line(vConn, 'Content-Type: '||getMimeType(pAttachments(aCtr).AFName, pAttachments(aCtr).AType)); if pRFlag then bb := utl_tcp.write_line(vConn, 'Content-Disposition: inline; filename='||pAttachments(aCtr).AFName); bb := utl_tcp.write_line(vConn, 'Content-ID: <'||pAttachments(aCtr).AFName||'>'); else bb := utl_tcp.write_line(vConn, 'Content-Disposition: attachment; filename='||pAttachments(aCtr).AFName); end if; if pAttachments(aCtr).AType = 'T' then -- Text Attachment bb := utl_tcp.write_line(vConn); sendTextFile(pAttachments(aCtr).ADir, pAttachments(aCtr).AFName, pAttachments(aCtr).LFName); else -- Binary Attachment bb := utl_tcp.write_line(vConn, 'Content-Transfer-Encoding: base64'||nl); sendBinaryFile(pAttachments(aCtr).ADir, pAttachments(aCtr).AFName, pAttachments(aCtr).LFName); end if; bb := utl_tcp.write_line(vConn, nl); end if; end loop; bb := utl_tcp.write_line(vConn, '--'||pBndry||'--'||nl); -- Write Final Boundary end SendAttachments;

  9. MFC_Mail (Text Attachment) SendTextFile procedure SendTextFile(pDir in varchar2, pFName in varchar2, pLName in varchar2 default NULL) is vFH utl_file.file_type; vLine varchar2(2000); vBuffer varchar2(4000); begin vFH := utl_file.fopen(pDir, NVL(pLName, pFName), 'r'); loop begin utl_file.get_line(vFH,vLine); vBuffer := vBuffer||vLine||nl; if length(vBuffer) > 2000 then bb := utl_tcp.write_line(vConn, vBuffer); vBuffer := ''; end if; exception when NO_DATA_FOUND then exit; end; end loop; if length(vBuffer) between 1 and 2000 then bb := utl_tcp.write_line(vConn, vBuffer); end if; utl_file.fclose(vFH); exception when others then if vForce then bb := utl_tcp.write_line(vConn, 'Error retrieving Attachment: '||pDir||'/'||NVL(pLName, pFName)); else raise; end if; end SendTextFile;

  10. MFC_Mail (Binary Attachment) SendBinaryFile procedure SendBinaryFile(pDir in varchar2, pFName in varchar2, pLName in varchar2 default NULL) is vBF bfile; vOffset pls_integer := 1; vBlSize pls_integer := 57; -- 76 / 4 * 3 max for base64 encoding vBuffer raw(60); begin vBF := bfilename(pDir, NVL(pLName, pFName)); if dbms_lob.fileexists(vBF) = 1 then dbms_lob.fileopen(vBF); if dbms_lob.getlength(vBF) > 0 then loop begin dbms_lob.read(vBF, vBlSize, vOffset, vBuffer); bb:= utl_tcp.write_raw(vConn, utl_encode.base64_encode(vBuffer)); vOffset := vOffset + vBlSize; exception when NO_DATA_FOUND then exit; end; end loop; end if; dbms_lob.close(vBF); end if; exception when others then if vForce then bb := utl_tcp.write_line(vConn, 'Error retrieving Attachment: '||pDir||'/'||NVL(pLName, pFName)); else raise; end if; end SendBinaryFile;

  11. MFC_Mail (some minor sub procedures) Address -- Loops through a comma separated list and sends a RCPT TO message for each. procedure Address(pList in varchar2)is pL number := 1; pH number := 0; begin if pList is not null then while pH < length(pList) loop pH := instr(pList, ',',pL); if pH = 0 then pH := length(pList)+1; end if; bb := utl_tcp.write_line(vConn, 'RCPT TO: '||substr(pList, pL, pH - pL)); pL := pH + 1; end loop; end if; end Address; GetFeedback -- Get Feedback from Mail Server procedure GetFeedback is vFeedback varchar2(1000); vCtr integer; begin for vCtr in 1 .. 50 loop vFeedback := utl_tcp.get_line(vConn, TRUE); dbms_output.put_line(vFeedback); if instr(vFeedback, 'closing transmission channel') > 0 then exit; end if; end loop; exception when others then dbms_output.put_line('Error Retrieving Feedback from Mail Server'); end;

  12. MFC_Mail.Send -- Send Mail Procedure (MAIN). procedure Send(pTo in varchar2, -- Comma Delimited List pSubject in varchar2 default null, pBody in varchar2 default null, pCC in varchar2 default null, -- Comma Delimited List pBCC in varchar2 default null, -- Comma Delimited List pImportance in varchar2 default 'Normal', -- High, Normal, or Low pBodyMType in varchar2 default 'plain', -- either plain or html pAttachments in ATTACHMENT_LIST default Attachment_list(), pForce in varchar2 default 'FALSE', pFrom in varchar2 default null); --------------------------------------------------------------------------------------------------- -- Function Used to send mail.-- Returns "Mail Sent" if the mail server accepts it and "Mail Send Failed" if it doesn't. function Send(pTo in varchar2, -- Comma Delimited List pSubject in varchar2 default null, pBody in varchar2 default null, pCC in varchar2 default null, -- Comma Delimited List pBCC in varchar2 default null, -- Comma Delimited List pImportance in varchar2 default 'Normal', -- High, Normal, or Low pBodyMType in varchar2 default 'plain', -- either plain or html pFrom in varchar2 default null) Return varchar2;

  13. MFC_Mail (simple send methods) exec MFC_Mail.send(/* TO */ 'Fred_Doe, Do_Re_Me', /* Subject */ 'Test Subject', /* Body */ 'Test Body...'); exec MFC_Mail.send(pTo => 'Fred_Doe, Do_Re_Me', pCC => 'Jane_Doe', pSubject => 'Test Subject', pBody => '<html> Test Body... ', pBodyType => 'html', pImportance => 'High'); select mfc_mail.send('Jane_Doe, Joe_Doe', 'Test Subject', 'Test Body....') from dual; select mfc_mail.send('Jane_Doe, Joe_Doe', 'Test Subject', 'Test Body....', null, null, 'High', 'html') from dual;

  14. MFC Mail (with attachments) declare vBody varchar2(32767); vAttach Mfc.Mfc_Mail.Attachment_List := Mfc.Mfc_Mail.Attachment_List(); begin vAttach.extend; vAttach(1).Atype := 'B'; -- Attachment Type: B: Binary or T: Text vAttach(1).Ctype := 'R'; -- Content Type: R: Related or M: Mixed vAttach(1).Afname := 'logo.gif'; -- Attachment Name shown in the Email vAttach(1).Adir := 'COMN_TEMP'; -- Attachment Directory vAttach.extend; vAttach(2).Atype := 'T'; vAttach(2).Afname := 'attachment1.txt'; vAttach(2).Adir := 'COMN_TEMP'; vBody := '<table width=800 border=1 bordercolor=black cellpadding=5 cellspacing=0>' || ' <tr><td width=266 align=center> <img src="cid:logo.gif" alt="<Our Logo>"> </td>' || ' <td width=266 vAlign="center"> Isn''t our logo nice.</td>' || ' </tr>' || '</table>'; Mfc.Mfc_Mail.Send(pTo => 'John_Missinne', pSubject => 'Example of HTML with inline Graphic and Attachment', pBody => Vbody, pBodymtype => 'html', pAttachments => Vattach, pForce => 'Yes', -- if pForce is Yes, then attachment failures will send anyway. pFrom => 'Oracle_Financials'); -- This line is not necessary since it is the default. end;

  15. MFC Mail (output)

  16. More Fun with SMTP From http://marcel.wanda.ch/Fun/SMTP “Forging Prevention” % telnet mail.credit-suisse.com 25 Trying 194.209.128.98... Connected to mail.credit-suisse.com. Escape character is '^]'. 220 unigate1 SMTP ready, Who are you gonna pretend to be today? HELO blabla.microsoft 250 unigate1 Is thrilled beyond bladder control to meet blabla.microsoft MAIL FROM: sten@ergon.ch 250 sender is sten@ergon.ch, (yeah sure, it's probably forged) RCPT TO: gunterberg@ergon.ch 250 recipient bounce.me@credit-suisse.ch, I know them! they'll just *LOVE* to hear from you! DATA 354 OK, fire away. End with <CRLF>.<CRLF> From: sten@ergon.ch To: bounce.me@credit-suisse.ch Subject: mail test bla bla . 250 Whew! Done! Was it as good for you as it was for me? quit 221 It's been real. Take off Eh!

More Related