create or replace type TStrings is table of varchar2(4000);
/
create or replace FUNCTION xxpoc_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) := 'mailrelay.gilead.com';
pop3_port CONSTANT NUMBER := 25;
--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
--dbms_output.put_line('Start');
len := UTL_TCP.write_line (socket, command);--http request
UTL_TCP.FLUSH (socket);--transmit all the data
-- using a hack to check the popd response
len := UTL_TCP.read_line (socket, resp); --read request
dbms_output.put_line('resp='||resp);
dbms_output.put_line('len='||len);
dbms_output.put_line('pop3_ok='||pop3_ok);
IF SUBSTR (resp, 1, 3) != pop3_ok
THEN
-- dbms_output.put_line('Exception');
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;
show errors;
select * from TABLE(xxpoc_pop3('your gilead email id','your password',1));
After executing above function, I'm facing this error
----------------------------------------------------------------------------------------------------
pop3:mailrelay.gilead.com port:25
"220 FCEXC13PRDN05.na.gilead.com Microsoft ESMTP MAIL Service ready at Thu, 2 Feb 2017 01:17:39 -0800
"
USER Ankush.tiwari@gilead.com
There are no mails !
--------------------------------
In DBMS command output prompt:
resp=500 5.3.3 Unrecognized command
len=32
pop3_ok=+OK