SQL & PL/SQL

Hi All,

Can anyone please help me out, I need to read an email attachment from mailbox using plsql function/procedure and then put its content into staging table,

so first and most important thing is to read email attachment's content.

kindly suggest any way to read the content of an email.(attachment must be in .csv file or pdf)

Requirement: -To
read attachment (.csv  file)  of an email using  PL/SQL function.

Solution:

First write a PL/SQL function using UTL_TCP utility

1. Create
   a type to store the output of  function

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

1. Then
   write a plsql function- To read email

Create
or replace FUNCTION xxpoc_pop3 (

   Username 
VARCHAR2,

   PASSWORD 
VARCHAR2,

   magnum   
NUMBER

)

   RETURN tstrings PIPELINED

IS

var_base64 varchar2 (30000);

   pop3_server               CONSTANT VARCHAR2 (100)     := 'mailrelay.companyname.com';

   pop3_port   
CONSTANT NUMBER             :=110;

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

var_base64:=from_base64
(resp);

dbms_output.put_line('var_base64~'||var_base64);

      RETURN (var_base64);

   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 daemo

   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 ('*** 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;

Note: While giving port number and host name be careful,

Here host name is : mailrelay.companyname.com

It’s for receiving mails that’s why
will not use SMTP protocol but TCP/IP.

Port no for pop3:110

If we were using IMAP, Port no
would be: 143

We have used pipelined function
here to use this function as a table

Using this function first we tried
to make connection with the mail server and then transfer the content.

1. Finally
   execute the function

select
* from TABLE(xxpoc_pop3('xyz6@gilead.com', ‘user password',msgnum));

after that take the base 64 encoded
data coming out  as the output of the
above function and decode it using another plsql  function,

Note:- The below function is for encoding and decoding base64 data
into readable format,

create
or replace function from_base64(t in varchar2)

return
varchar2 is

begin

  return
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));

end
from_base64;

OR

separately we can decode data of
attached .csv file using below query .

select
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(:t))) from
dual;

Note:- After  getting the
output of the UTL_TCP  function written
above, we need to parse the whole message and take only the Base64 formatted
data which is the contenct of .csv file.

Using substr and instr function we
are trying to take only wanted part of the whole message received as a output
of executed function  xxpoc_pop3.

Limitations:-

We have reached to the final stage,
only problem is how to parse or decode the output of a function xxpoc_pop3 into
readable format(only attachment data, message body is in readable format).

Problem/Issue:  We are unable
to decode the .csv  file, although tried
many ways and reached to closer of the result yet couldn’t meet the given
requirement.

I am trying since last few days but unable to get exact output.

Regards,

Ankush Tiwari