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