Monday, May 19, 2008

UTL_MAIL???

How can I use utl_mail?

SOURCE :
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Exeception:
-29261 => Invalid_argument
-44101 => Invalid_priority

Anyway I should set initialization Parameter:
SMTP_OUT_SERVER

utl_mail.send(
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain;
charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3);


utl_mail.send_attach_raw (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain;
charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3,
attachment IN RAW,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT
'text/plain; charset=us-ascii',
att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

utl_mail.send_attach_varchar2(
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain;
charset=us-ascii',
priority IN PLS_INTEGER DEFAULT 3,
attachment IN VARCHAR2 CHARACTER SET ANY_CS,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT
'text/plain; charset=us-ascii,
att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

Let's go to test it:
$ @$ORACLE_HOME/rdbms/admin/utlmail.sql
$ @$ORACLE_HOME/rdbms/admin/prvtmail.plb


Example:

CREATE OR REPLACE PROCEDURE test_sendmail01 IS
vSender VARCHAR2(50) := 'sender@domain';
vRecip VARCHAR2(50) := 'to@domain';
vSubj VARCHAR2(50) := 'TEST';
vMesg VARCHAR2(4000):= 'TEST Message';
vMType VARCHAR2(30) := 'text/plain; charset=tis-620';
BEGIN
utl_mail.send(vSender, vRecip, NULL, NULL, vSubj, vMesg, vMType, NULL);
END test_sendmail01;
/

SQL> exec test_sendmail01
BEGIN test_sendmail01; END;

*
ERROR at line 1:
ORA-29261: bad argument
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_MAIL", line 386
ORA-06512: at "SYS.UTL_MAIL", line 599
ORA-06512: at "SYS.TEST_SENDMAIL01", line 9
ORA-06512: at line 1

***********************************************************************
Becuase, we didn't set smtp_out_server initialization Parameter
***********************************************************************
SQL> show parameter smtp_out_server

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server string


SQL> alter session SET smtp_out_server = 'smtpserver.domain';

Session altered.

SQL> exec test_sendmail01;

PL/SQL procedure successfully completed.

...

1 comment:

nitin said...

Hi

Please refer the article Using UTL Mail Package in Oracle 10g. Hope that would be useful for you.

Nitin