okbob / orafce_mail

PostgreSQL extension that allows to sending mails from Postgres. It is based on `libcurl` library. Implemented API is almost compatible with API of Oracle's `UTL_MAIL` package.

orafce_mail

This is implementation of Oracle's API of packages utl_mail, DBMS_MAIL

It doesn't ensure full compatibility, but should to decrease a work necessary for successful migration.

Security

These functions can be used by user that is member of role orafce_mail. For setting of orafce_mail.smtp_server_url the user should be member of role orafce_mail_config_url. For setting of orafce_mail.smtp_server_userpwd the user should be member of role orafce_mail_config_userpwd.

Functionality

set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to '[email protected]:yourgoogleapppassword';

call utl_mail.send(sender => '[email protected]',
                   recipients => '[email protected]',
                   subject => 'ahoj, nazdar, žlutý kůň',
                   message => e'test, \nžlutý kůň');

do $$
declare
  myimage bytea = (select img from foo limit 1);
begin
  call utl_mail.send_attach_raw(sender => '[email protected]',
                                recipients => '[email protected]',
                                subject => 'mail with picture',
                                message => 'I am sending some picture',
                                attachment => myimage,
                                att_mime_type => 'image/png',
                                att_filename => 'screenshot.png');
end
$$;

Dependency

This extensions uses curl library.

An extension Orafce should be installed before

Performance

Sending to remote smtp server is pretty slow. This is not an issue of orafce_mail or curl library. So don't try to send mails from performance critical processes. Use some buffer table, and send mails from another process started by cron or some scheduler application, or use local smtp server. Postgres has great LISTEN/NOTIFY mechanism.

orafce_mail's Issues

ERROR: type varchar2 does not exist

Hi Pavel,

I have seen this error now using latest versions of PG and orafce + orafce_mail when I try to restore a dump.

gilles=# create extension orafce;
CREATE EXTENSION
gilles=# create extension orafce_mail;
ERROR:  type varchar2 does not exist

Even if I set the default search_path on this database using the oracle schema first, the error appears.

The only thing that fix that is the VI command :%s/ varchar2/ oracle.varchar2/ or if you prefer a call to SET search_path TO oracle,public; at start of the SQL script.

Kind regards,

Release tarball

Hi @okbob ,

Can you please release a tarball, so that I can build RPMs of orafce_mail?

Thanks!

Cheers, Devrim