-
Sending / Receiving e-mails
(1) Without creating an instance of Outlook, is there a way of sending and receiving e-mails from Oracle program ?
(2) Can I send formatted emails from Oracle (HTML format) ?
Thanks, Deepa
-
yup, I think it requires 8i with Jserver enabled. We do it all the time.
-
-
yepyep, but that's plain text, looks like. We wanted to trigger html emails based on work requests getting entered.
here's one fer html mime type:
CREATE OR REPLACE PROCEDURE send_email
(p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'mail.yourserver.com';
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection('mail.yourserver.com', 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn,'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)|| p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
/
this procedure is a little different from the one posted above, in that you can feed it the sender email, recipient email, and message from a trigger. Comes in very handy when you want to actually have the email containing data from the new record.
this particular procedure is fed by the below trigger:
CREATE OR REPLACE TRIGGER hux_email_customer
before insert on worequest
for each row
WHEN (new.req_num is not null and
new.email is not null and
new.name is not null and
new.building is not null and
new.room is not null and
new.description is not null and
new.problem is not null and
new.phone is not null)
declare to_mail varchar2(64);
name varchar2(80);
newbuilding varchar2(40);
newdescription varchar2(500);
msgtext varchar2(11048);
reqid varchar2(10);
crlf varchar2(2) := chr(13)||chr(10); -- EOL CHARACTERS
subjectline varchar2(60) := 'Subject: We have received your request, ';
compiledsubject varchar2(500);
begin
newbuilding := :new.building;
newdescription := :new.description;
compiledsubject := subjectline||:new.name||crlf;
to_mail := :new.email;
name := :new.name;
reqid := :new.req_num;
msgtext := compiledsubject||'
Facilities Management Online Work Request Received
Dear '||name||', We have received your request, and your number is: '||reqid||'.
Here is a summary of the information you submitted:
- '||newbuilding||'
- '||newdescription||'
If you have any questions at all please feel free to call us at x9999 or simply respond to this email.
Regards,
Facilities Management Customer Service';
send_mail2('fromaddy@yourserver.com',to_mail,msgtext);
end;
it ain't beautiful, but it works like a mug
edit - oops, the forum parsed the html within that trigger. If ya need it, lemme know I'll email it to ya.
-
btw - ignore this part due to poor table design.
WHEN (new.req_num is not null and
new.email is not null and
new.name is not null and
new.building is not null and
new.room is not null and
new.description is not null and
new.problem is not null and
new.phone is not null)
-
Can you guys help me with this!!!
Hello guys,
I am trying to create a form that will use a procedure to send an email when a record is added. Below is my procedure and call to the procedure with the error. I looked up the error ORA-29531, it says this.
ORA-29531 no method string in class string
Cause: An attempt was made to execute a non-existent method in a Java class.
Action: Adjust the call or create the specified method.
What do I need to get the class file loaded and where to find the class file. Any help will be appreciated.
P.S. I am running 8i server and Forms 6i on Windows.
create or replace PROCEDURE send_mail (p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'bb2.carlylevanlines.com';
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient);
utl_smtp.open_data(l_mail_conn );
utl_smtp.write_data(l_mail_conn, p_message);
utl_smtp.close_data(l_mail_conn );
utl_smtp.quit(l_mail_conn);
end;
SQL> begin
2 send_mail( 'sender@test.com',
3 'receiver@test.com',
4 'Hello Email' );
5 end;
6 /
begin
*
ERROR at line 1:
ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
ORA-06512: at "SYS.UTL_TCP", line 678
ORA-06512: at "SYS.UTL_TCP", line 671
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at "ORACLE.SEND_MAIL", line 8
ORA-06512: at line 2
-
Have you installed the Jserver? http://www.dbasupport.com/forums/sho...&threadid=4167
There was an article by Julian* that gave instructions for windows - can't find it for the moment.
Note also that patches 8.1.7.4.15 and .16 for Windows have a bug which affects UTL_SMTP & UTL_TCP (I got ORA-29532). The fix is to use orajox8.dll from (say) 8.1.7.4.13. You might want to check with support.
* corrected afterwards
Last edited by DaPi; 12-22-2004 at 07:06 AM.
-
Tim,
I think your link should be http://www.oracle-base.com/Articles/...romPLSQL9i.php
(.asp => .php)
The one you posted gives me a 404.
-
Appologies to Julian . . . he wrote the installation instructions for Windows:
http://www.dbasupport.com/forums/sho...threadid=24763
-
Thanks guys. I really appreciate the replies. I will try it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|