Home » SQL & PL/SQL » SQL & PL/SQL » Setting up Email service on Oracle 9i (Oracle Database 9i)
Setting up Email service on Oracle 9i [message #683904] Thu, 04 March 2021 00:26 Go to next message
rfcl
Messages: 7
Registered: March 2021
Junior Member
We have oracle 9i database and we wish to send an email to a person using oracle database procedure. We have google g-suite email package for emails facility. Please tell me how to set up procedure to send email. also tell what configuration we have to make on the server for sending email.

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683905 is a reply to message #683904] Thu, 04 March 2021 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

You can find many procedures to send a mail in Oracle 9i, search for UTL_SMTP here or on Google.

Of course, you need a SMTP server which can be private (search for "smtp server" to download one if you have none) or public (like gmail one, but in this case you most likely need to have authentication credentials).

[Updated on: Thu, 04 March 2021 00:45]

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683907 is a reply to message #683905] Thu, 04 March 2021 00:51 Go to previous messageGo to next message
rfcl
Messages: 7
Registered: March 2021
Junior Member
thanks michel .can u tell me step by step in detail as i am novice.

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683908 is a reply to message #683907] Thu, 04 March 2021 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, you already have this on the web and here, so do search.

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683912 is a reply to message #683904] Thu, 04 March 2021 12:37 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
You do realize that 9i was released 20 years ago and had its last patch released almost 14 years ago . . . Is the rest of your technology stack equally old and out of support?

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683913 is a reply to message #683912] Thu, 04 March 2021 21:43 Go to previous messageGo to next message
rfcl
Messages: 7
Registered: March 2021
Junior Member
yes EdStevens wrote on Thu, 04 March 2021 12:37
You do realize that 9i was released 20 years ago and had its last patch released almost 14 years ago . . . Is the rest of your technology stack equally old and out of support?

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683914 is a reply to message #683913] Thu, 04 March 2021 22:05 Go to previous messageGo to next message
rfcl
Messages: 7
Registered: March 2021
Junior Member
When I run the following procedure to send email, I got error "ORA-29278: SMTP transient error: 421 Service not available". parameters successfully passed to procedure.



CREATE OR REPLACE PROCEDURE TEST.send_mail_new (p_sender IN VARCHAR2, p_recipient IN VARCHAR2,p_message IN VARCHAR2)
as
l_mailhost VARCHAR2(255) := 'smtp.gmail.com';
l_mail_conn utl_smtp.connection;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 587);
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;
/

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683916 is a reply to message #683914] Fri, 05 March 2021 00:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Thu, 04 March 2021 07:36
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
...

https://www.google.com/search?q=SMTP+transient+error%3A+421+Service+not+available

[Updated on: Fri, 05 March 2021 00:04]

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683919 is a reply to message #683916] Fri, 05 March 2021 00:20 Go to previous messageGo to next message
rfcl
Messages: 7
Registered: March 2021
Junior Member
I have gmail email facility with its credentials

Michel Cadot wrote on Fri, 05 March 2021 00:00

Michel Cadot wrote on Thu, 04 March 2021 07:36
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
...
https://www.google.com/search?q=SMTP+transient+error%3A+421+Service+not+available

Report message to a moderator

Re: Setting up Email service on Oracle 9i [message #683920 is a reply to message #683919] Fri, 05 March 2021 00:54 Go to previous message
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You may have some credentials but you did not send them.

Click on the link(s).

Report message to a moderator

Previous Topic: How to call/execute a visual basic script using PL / SQL
Next Topic: Need help on Tabibitosan (start of group)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Mar 23 05:38:18 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.