Skip to Main Content
  • Questions

  • Display email attachment properly using UTL_TCP package

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mayank.

Asked: June 18, 2008 - 1:51 pm UTC

Last updated: September 14, 2010 - 7:12 am UTC

Version: 11.1.0

Viewed 1000+ times

You Asked

Hi Tom,

I am able to retrieve emails and attachments from POP3 server using UTL_TCP package but the attachment gets displayed on the screen in some format (that is obviously not understandable to a normal user). How can I decrypt the attachment and display the contents of the attachment in a readable format ? Also, how can I get rid of all the extra information that it displays and store mail in the table ?

My code and the output I get are as follows :

create or replace type TStrings is table of varchar2(4000);
/

Type created.

/* Formatted on 2008/06/06 09:58 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION pop3 (
   username   VARCHAR2,
   PASSWORD   VARCHAR2,
   msgnum     NUMBER
)
   RETURN tstrings PIPELINED
IS
   --POP3_SERVER             constant varchar2(19) := '127.0.0.1';
   pop3_server   CONSTANT VARCHAR2 (100)     := 'pop.secureserver.net';
   pop3_port     CONSTANT NUMBER             := 110;
   --POP3_TIMEOUT            constant number := 10;
   pop3_ok       CONSTANT VARCHAR2 (10)      := '+OK';
   e_pop3_error           EXCEPTION;
   --E_READ_TIMEOUT  exception;
   --pragma exception_init( E_READ_TIMEOUT, -29276 );
   socket                 UTL_TCP.connection;
   line                   VARCHAR2 (30000);
   BYTES                  INTEGER;

   -- send a POP3 command
   -- (we expect each command to respond with a +OK)
   FUNCTION writetopop (command VARCHAR2)
      RETURN VARCHAR2
   IS
      len    INTEGER;
      resp   VARCHAR2 (30000);
   BEGIN
      len := UTL_TCP.write_line (socket, command);
      UTL_TCP.FLUSH (socket);
      -- using a hack to check the popd response
      len := UTL_TCP.read_line (socket, resp);

      IF SUBSTR (resp, 1, 3) != pop3_ok
      THEN
         RAISE e_pop3_error;
      END IF;

      RETURN (resp);
   END;
BEGIN
   PIPE ROW ('pop3:' || pop3_server || ' port:' || pop3_port);
   -- Just to make sure there are no previously opened connections
   --UTL_TCP.close_all_connections;

   -- open a socket connection to the POP3 server
   socket :=
      UTL_TCP.open_connection (remote_host      => pop3_server,
                               remote_port      => pop3_port,
                               --tx_timeout => POP3_TIMEOUT,
                               CHARSET          => 'US7ASCII'
                              );
   -- read the server banner/response from the pop3 daemon
   PIPE ROW (UTL_TCP.get_line (socket));
   -- authenticate with the POP3 server using the USER and PASS commands
   PIPE ROW ('USER ' || username);
   PIPE ROW (writetopop ('USER ' || username));
   PIPE ROW ('PASS ' || PASSWORD);
   PIPE ROW (writetopop ('PASS ' || PASSWORD));
   -- retrieve the specific message
   PIPE ROW ('RETR ' || msgnum);
   PIPE ROW (writetopop ('RETR ' || msgnum));
   --PIPE ROW( 'LIST '||msgNum ); PIPE ROW( WriteToPop('LIST '||msgNum) );
   PIPE ROW ('*** START OF INTERNET MESSAGE BODY ***');

   LOOP
      BYTES := UTL_TCP.available (socket);

      IF BYTES > 0
      THEN
         BYTES := UTL_TCP.read_line (socket, line);
         line := REPLACE (line, CHR (13) || CHR (10), '');
         
         -- WILL HAVE TO USE PLSQL FUNCTIONS (HAVE BOOKMARKED) TO GET THE MAIL 
         -- IN THE PREFERRED FORMAT. CAN USE "REPLACE()"

         IF LENGTH (line) = 1 AND line = '.'
         THEN
            PIPE ROW ('*** END OF INTERNET MESSAGE BODY ***');
         ELSE
            PIPE ROW (line);
         END IF;
      END IF;

      EXIT WHEN LENGTH (line) = 1 AND line = '.';
   END LOOP;

   --PIPE ROW( '*** END OF INTERNET MESSAGE BODY ***' );

   -- close connection
   PIPE ROW ('QUIT');
   PIPE ROW (writetopop ('QUIT'));
   UTL_TCP.close_connection (socket);
EXCEPTION
   WHEN e_pop3_error
   THEN
      PIPE ROW ('There are no mails !');
END;




select LENGTH(column_value) as "SIZE", column_value as "LINE" from TABLE(pop3('abc@xyz.com','my_passwd',1))l;


Output:
SIZE LINE

34 pop3:pop.secureserver.net port:110
101 +OK AVG POP3 Proxy Server <24059.1213810512@p3pop01-10.prod.phx3.gdg> 8.0.80/8.0.100 [270.3.0/1505]

27 USER abc@xyz.com
6 +OK

11 PASS my_passwd
6 +OK

6 RETR 1
18 +OK 3808 octets

38 *** START OF INTERNET MESSAGE BODY ***
71 Received: (qmail 1560 invoked from network); 18 Jun 2008 17:32:31 -0000
90 Received: from unknown (HELO p3presmtp01-20.prod.phx3.secureserver.net) ([208.109.80.169])
52 (envelope-sender <lmn@gmail.com>)
73 by smtp07-02.prod.mesa1.secureserver.net (qmail-1.03) with SMTP
66 for <mayank@elevational.com>; 18 Jun 2008 17:32:31 -0000
72 Received: (qmail 22632 invoked from network); 18 Jun 2008 17:32:31 -0000
55 Received: from hu-out-0506.google.com ([72.14.214.225])
52 (envelope-sender <lmn@gmail.com>)
82 by p3presmtp01-20.prod.phx3.secureserver.net (qmail-ldap-1.03) with SMTP
66 for <abc@xyz.com>; 18 Jun 2008 17:32:30 -0000
65 Received: by hu-out-0506.google.com with SMTP id 22so8399166hug.4
75 for <abc@xyz.com>; Wed, 18 Jun 2008 10:32:29 -0700 (PDT)
53 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
29 d=gmail.com; s=gamma;
71 h=domainkey-signature:received:received:message-id:date:from:to
44 :subject:mime-version:content-type;
56 bh=8LurAsPwnC5OChFRVycrCp8iIRmRLbQNFhcKwHZAIYA=;
76 b=S37EQqlpRspBfaQc/GR8Z2506WZao88Zr9TEVP1cOTBMGTr2lE2czqqG1Lmad6ZuqJ
77 L6paBVPg3Sn8X1LBOBPIA9inb2PLJx7SW683iS8yMNp7l61g2zYag3Yh2mHgaJYF0wxA
47 x8CWF4ujLGG+4nobm9ib+1trNeZEi0R0E1e2k=
41 DomainKey-Signature: a=rsa-sha1; c=nofws;
29 d=gmail.com; s=gamma;
68 h=message-id:date:from:to:subject:mime-version:content-type;
76 b=MBv7g6sgSTiMx66yQLJG1AHMXFDyi9YC0BgvClNWZ2eUKABIJjEIqtqzxRCfegO3O5
77 0g1ow3ikn+2lRAGYL6F6vONJpsMNFFRVdmkWtNJv3/G58iWbLwDB5mPGW/Br5W7UZefL
47 vyteb4L/PvWkpEqU4Hg4Crscd8POM/ixVl6Jk=
72 Received: by 10.78.198.14 with SMTP id v14mr507395huf.115.1213810349447;
45 Wed, 18 Jun 2008 10:32:29 -0700 (PDT)
73 Received: by 10.78.23.18 with HTTP; Wed, 18 Jun 2008 10:32:29 -0700 (PDT)
73 Message-ID: <d224f4e30806181032t26fa2060p518762cbfb03229f@mail.gmail.com>
37 Date: Wed, 18 Jun 2008 13:32:29 -0400
45 From: "lmn" <lmn@gmail.com>
26 To: abc@xyz.com
20 Subject: Test msg 23
17 MIME-Version: 1.0
31 Content-Type: multipart/mixed;
49 boundary="----=_Part_624_14145315.1213810349411"
15 X-Nonspam: None
50 X-Antivirus: AVG for E-mail 8.0.100 [270.4.0/1507]


39 ------=_Part_624_14145315.1213810349411
37 Content-Type: multipart/alternative;
49 boundary="----=_Part_625_11184620.1213810349412"


39 ------=_Part_625_11184620.1213810349412
44 Content-Type: text/plain; charset=ISO-8859-1
31 Content-Transfer-Encoding: 7bit
27 Content-Disposition: inline

38 Test message 23 with Attachement !!!!!

39 ------=_Part_625_11184620.1213810349412
43 Content-Type: text/html; charset=ISO-8859-1
31 Content-Transfer-Encoding: 7bit
27 Content-Disposition: inline

42 Test message 23 with Attachement !!!!!<br>

41 ------=_Part_625_11184620.1213810349412--


39 ------=_Part_624_14145315.1213810349411
51 Content-Type: text/plain; name=PrinterSetupInfo.txt
33 Content-Transfer-Encoding: base64
28 X-Attachment-Id: f_fhm7cbx90
62 Content-Disposition: attachment; filename=PrinterSetupInfo.txt

76 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQ0KU2V0dXAgUmVzdWx0
76 cw0KPT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQ0KDQpNb2RlbCA6
76 DQoJQ2Fub24gTVg3MDAgc2VyaWVzDQoNCiAgTmV0d29yayBTZXR1cCBvZiB0aGUgUHJpbnRlciA6
76 DQoJQ29tcGxldGVkDQogICAgTmFtZSA6DQoJQ2Fub24gTVg3MDAgc2VyaWVzDQogICAgUG9ydCBO
76 YW1lIDoNCglDTkJKTlBfMDAwMDg1QzQ5NDUwDQoNCiAgTmV0d29yayBTZXR1cCBvZiB0aGUgU2Nh
76 bm5lciA6DQoJQ29tcGxldGVkDQogICAgTmFtZSA6DQoJQ2Fub24gTVg3MDAgc2VyXzAwMDA4NUM0
76 OTQ1MA0KDQogIE5ldHdvcmsgU2V0dXAgb2YgdGhlIEZheCA6DQoJQ29tcGxldGVkDQogICAgTmFt
76 ZSA6DQoJQ2Fub24gTVg3MDAgc2VyaWVzIEZBWA0KICAgIFBvcnQgTmFtZSA6DQoJQ05CSk5QRkFY
76 XzAwMDA4NUM0OTQ1MA0KDQogIE5ldHdvcmsgU2V0dXAgb2YgdGhlIENhcmQgU2xvdCA6DQoJQ29t
76 cGxldGVkDQogICAgRHJpdmUgTmFtZSA6DQoJY2Fub25fbWVtb3J5IG9uICcwMDAwMDBjNDk0NTAn
76 IChaOikNCg0KDQo9PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09DQo=

39 ------=_Part_624_14145315.1213810349411
54 Content-Type: text/plain; x-avg=cert; charset=us-ascii
43 Content-Transfer-Encoding: quoted-printable
27 Content-Disposition: inline
40 Content-Description: "AVG certification"


40 No virus found in this incoming message.
16 Checked by AVG.
77 Version: 8.0.100 / Virus Database: 270.4.0/1507 - Release Date: 6/18/2008 7:=
5 09 AM

41 ------=_Part_624_14145315.1213810349411--


36 *** END OF INTERNET MESSAGE BODY ***
4 QUIT
6 +OK


and Tom said...

You would have to

a) parse the message, break it up into its component bits

b) look at the encoding used on the attachment - in this case base64

c) decode the attachment from base64 into what it used to be.

d) then you have what you had


This isn't really an "oracle question", you'd be better off looking on forums that describe SMTP and how email is encoded - but your example is straight forward enough - you can see sort of how to parse the email (but to do it RIGHT you'll need to implement the equivalent of an entire email client!!!!). base 64 routines exist in the database (the utl_encode package)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mayank Mehta, June 20, 2008 - 8:47 am UTC


any pl/sql-java sample code parsing mails

georg-gm, September 14, 2010 - 3:31 am UTC

is there any sample
parsing the email-message and
decoding the base64

you say: but to do it RIGHT you'll need to implement the equivalent of an entire email client!!!!

is there a sample java with pl/sql wrapper?
Tom Kyte
September 14, 2010 - 7:12 am UTC

You would have to read up on the java mail api or something similar, determine what features/functions YOU wanted implemented and wrap them. It is a non-trivial task to simply "parse an email message". They are complex multi-part documents these days, not just simple text.

there are many examples of 'pl/sql wrapped' java - even some for the java mail api (although this is for SENDING, not reading, email)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805

it would be similar - in that you would write the core routines in java, doing whatever you needed to support (again, there are dozens of API calls, you probably need a small handful to support your specific needs/requirements)...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library