SQL & PL/SQL

Hi,

With the help of Billy's code and a little editing of my own, I am able to retrieve the emails from the POP server with the attachments in correct format. Now I am trying to connect to the POP mail server located on one of our company's server.
My code is as follows :

CREATE OR REPLACE FUNCTION pop3 (
username VARCHAR2,
PASSWORD VARCHAR2,
msgnum NUMBER
)
RETURN tstrings PIPELINED
IS
pop3_server CONSTANT VARCHAR2 (100) := '192.168.0.3';
pop3_port CONSTANT NUMBER := 995;
pop3_ok CONSTANT VARCHAR2 (10) := '+OK';
e_pop3_error EXCEPTION;
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()"

-- Checking end of email
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;

-- 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;

On running this code in the following manner :
SQL> select * from table(pop3('abc@xyz.com','my_passwd',1));

I get the error msg as:

ERROR:
ORA-29260: network error: TNS:no listener
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SCOTT.POP3", line 42

I have checked my Microsoft Outlook settings and they are as follows :

Incoming server (POP3) : 995
This server requires Encrypted Connection(SSL).
Outgoing server (SMTP) : 587
Use the following type of encryted connection: TLS

Can someone please provide me some solution to my problem.

P.S. : 192.168.0.3 is the machine that is hosting Gmail POP3 server (pop.gmail.com). I have already tried giving "pop.gmail.com" for server name (pop3_server) in the above code. In that case it gives me error:

ERROR:
ORA-29259: end-of-input reached
ORA-06512: at "SYS.UTL_TCP", line 116
ORA-06512: at "SYS.UTL_TCP", line 310
ORA-06512: at "SYS.UTL_TCP", line 380
ORA-06512: at "SCOTT.POP3", line 49

Also, when i telnet to pop.gmail.com, the command prompt goes blank and does not display anything that i type and in about 5 seconds shows the normal command prompt with default route. Whereas, telnet to 192.168.0.3 gives me error "Could not open connection to the host, on port 995: Connect failed".