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

CREATE OR REPLACE FUNCTION pop3 (
   username   VARCHAR2,
   PASSWORD   VARCHAR2,
   msgnum     NUMBER
)
   RETURN tstrings PIPELINED
IS
   pop3_server   CONSTANT VARCHAR2 (100)     := 'pop.secureserver.net';
   pop3_port     CONSTANT NUMBER             := 110;
   pop3_ok       CONSTANT VARCHAR2 (10)      := '+OK';
   e_pop3_error           EXCEPTION;
   
   socket                 UTL_TCP.connection;
   line                   VARCHAR2 (30000);
   line2				  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);
      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,
                               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), '');
         	
		 -- Decoding the email Attachment
		 IF line = 'Content-Transfer-Encoding: base64'
		 THEN
			PIPE ROW (line);

			-- Reading and printing X-Attachment-Id: xyz
			BYTES := UTL_TCP.available (socket);
			BYTES := UTL_TCP.read_line (socket, line);
			line := REPLACE (line, CHR (13) || CHR (10), '');
			PIPE ROW (line);
			
			-- Reading and printing Content-Disposition: attachment; filename=PrinterSetupInfo.txt
			BYTES := UTL_TCP.available (socket);
			BYTES := UTL_TCP.read_line (socket, line);
			line := REPLACE (line, CHR (13) || CHR (10), '');
			PIPE ROW (line);
			
			-- Reading and printing blank line
			BYTES := UTL_TCP.available (socket);
			BYTES := UTL_TCP.read_line (socket, line);
			line := REPLACE (line, CHR (13) || CHR (10), '');
			PIPE ROW (line);
			
			-- Reading, decoding and printing the Attachment
			PIPE ROW('Reading the Attachment !!!!');
			BYTES := UTL_TCP.available (socket);
			BYTES := UTL_TCP.read_line (socket, line);
			line := REPLACE (line, CHR (13) || CHR (10), '');
			
			LOOP 
				
				--IF LENGTH(line) = 1 AND line = ''
				IF line IS NULL	-- This is where the condition never gets satisfied in spite of reaching the end of attachment. I have pasted the original encoded attachment in the post as retrieved using Telnet command
				THEN
					PIPE ROW ('Last line of the attachment !!');
					PIPE ROW (line);
				ELSE	
					line2 := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(line)));
					PIPE ROW (line2);
				END IF;
				
				BYTES := UTL_TCP.available (socket);
				BYTES := UTL_TCP.read_line (socket, line);
				--line := REPLACE (line, CHR (13) || CHR (10), '');
				
				--EXIT WHEN LENGTH(line) = 1 AND line is NULL;
				EXIT WHEN line IS NULL;
			END LOOP;
			
		 ELSIF 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 emails !');
END;