pop3_get — get messages from a POP3 server


array pop3_get ( in host varchar ,
in user varchar ,
in password varchar ,
in buffer_size integer ,
in command varchar ,
in exclude_uidl_list vector );


Pop3_get is used to retrieve and delete messages from a server running the Post Office Protocol version 3 as defined in rfc1725. In its default form it returns a vector of vectors containing messages retrieved from the POP3 server. Each vector within the vector contains a pair of VARCHAR UIDL and VARCHAR Message body, i.e. to get the message body of the second message retrieved, one would use aref (aref (msg_vec, 1), 1) . Total length of messages retrieved will not exceed the value of buffer_size parameter in bytes.

The optional parameter command can be used to control output or delete messages. When command is passed a VARCHAR 'uidl', pop3_get outputs single vector containing VARCHAR UIDLs. The buffer_size constraint is effective here. Thus, the vector will only contain UIDLs of messages whose total message text length does not exceed buffer_size bytes. These message lengths are accumulated in the order returned by the POP3 server.

Command 'delete' will cause retrieved messages to be deleted from the server after retrieval.



The host to connect with. IP address or hostname:port. There is no default for port, so to connect to the standard port for POP3, use <hostname/IP address>:110


string user id in remote host.


string password in remote host.


integer maximum total length of message text for messages/uidls to be retrieved.


Command string . Valid values are empty, 'uidl' or 'delete'


A vector containing UIDLs. A message whose UIDL appears in this list will not be retrieved or deleted.

Return Types

A vector of vectors containing UIDL/Message text strings or a 'flat' vector containing UIDL strings .


Table24.62.Errors signalled by

SQLState Error Code Error Text Description
2E000 PO001 Cannot resolve host in pop3_get
08001 PO002 Cannot connect in pop3_get
08006 PO003 No response from remote POP3 server
08006 PO004 Not valid user in remote POP3 server
08006 PO005 UIDL command to remote POP3 server failed
08006 PO006 Could not get output of UIDL from remote POP3 server.
08006 PO007 LIST command to remote POP3 server failed.
08006 PO008 Could not get output of LIST from remote POP3 server.
08006 PO010 Failed reading output of LIST command on remote POP3 server
08006 PO011 Could not DELE messages from remote POP3 server
08006 PO012 Could not QUIT from remote POP3 server
08000 PO013 Argument 6 to pop3_get must be a vector
08006 PO014 Misc. error in connection in pop3_get


Example24.244.Get messages from remote POP3

This example retrieves messages from a remote POP3 server and stores them in a table.

                      MSG_HOST VARCHAR,
                      MSG_UIDL VARCHAR,
                      MSG_TEXT LONG VARCHAR,
                      primary key (MSG_ID, MSG_HOST, MSG_UIDL));

create procedure
get_msgs (in pop_host varchar, in pop_uid varchar, in pop_pwd varchar)
  declare msg_vec any;
  declare inx integer;

  msg_vec := pop3_get (concat (pop_host, ':110'),

  inx := 0;

  while (inx < length (msg_vec))
      insert into MY_MSGS (MSG_HOST, MSG_UIDL, MSG_TEXT)
             values (pop_host,
                     aref (aref (msg_vec, inx), 0),
                     aref (aref (msg_vec, inx), 1));

      inx := inx + 1;

Here is a test run. Just for the fun, let's get the message subjects, too.

SQL> get_msgs('', 'ghard', '|_337h4x0R');

SQL> select MSG_UIDL, length (MSG_TEXT), get_keyword ('Subject', aref (mime_tree (MSG_TEXT), 0)) from MY_MSGS;
MSG_UIDL          callret   callret

1003930514.maildrop7.14798  3482      [Fwd: Linux Expo]
1003930555.maildrop7.15349  7683      [Fwd: SOAP options example]

2 Rows. -- 8 msec.

See Also

mime_tree , RFC1725