SQL & PL/SQL :: Send Emails From Oracle Using Both UTL_MAIL As Well As UTL_SMTP
Oct 9, 2013
I developed a program to send e-mails from Oracle using both UTL_MAIL as well as UTL_SMTP. My requirement now is to send around 75,000 emails per day, When I tested for 1000 emails it is taking around 6 mins., Then for 75,000 it would be around 7-8 hours. Not to put the server busy for these many hours. Can we really send this much of e-mails from Oracle? UTL_MAIL is little faster than UTL_SMTP)
View 12 Replies
ADVERTISEMENT
Aug 2, 2006
I tried using UTL_SMTP to send simple emails. My code is as follows:-
PROCEDURE prc_send
IS
- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;
BEGIN
-- establish the connection to the smtp server
[code].........
When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated. I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.
View 9 Replies
View Related
Nov 7, 2013
Our scheduled job is created using a chain of programs. Below is the flow.start the
job-->prg1-->COMPLEDED-->prg2--COMPLETED-->prg3-->COMPLETED-->prg4-->COMPLETED-->finish
the job I want to get the email notification(error messge also), when any of the steps in the chain fails in the job.I have tried this scenario, but I am not getting the error message if a program in the chain fails.
View 0 Replies
View Related
Jun 6, 2011
I am trying to send an email attachment using "utl_mail.send_raw" utility and not successful. Constantly I keep getting an error:
"ORA-20001: An error has occured: ORA-20001: ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "APPOWNER.SANDEEP_CT_FILE", line 103"
I think it's emanating from "ORA-06508: PL/SQL: could not find program unit being called".
a) this morning my production dba " utlmail.sql in DEVELOPMENT as sys.
b) Create or replace directory TEMP_SANDEEP as '/u01/data' (This was created so that BFILENAME could read it + "dbms_lob.fileopen" needs to access this from Oracle directory and not Operating system directory)
c) 'u01/data' exists on Unix development box.
d) Then I created the following procedure to create a text file (text file's name is currentdate.txt i.e. 06062011.txt) that has some information. Once created, the UTL_MAIL.SEND_RAW should pick this file up and email this to concerned parties(and in this case it's me as I am doing this on development environment).
Code for the above is as follows:
CREATE OR REPLACE procedure sandeep_ct_file is
/*Variable declarations */
v_dir_location varchar2(100);
v_file_name varchar2(100);
v_file_handle UTL_FILE.FILE_TYPE;
v_report LONG := chr(10)||'Prog Code' ||' '||
[code]....
Do I have to defined SMTP connections and parameters?
View 10 Replies
View Related
Dec 30, 2011
I'm trying to send e-mail using UTL_SMTP with UTF-8 coding. I have a problem that the UTF-8 letters become `?`.
So here is the
p_to varchar2(250);
p_from varchar2(250):='email_address';
p_subject varchar2(250):='...vienotā...';
P_smtp_hostname varchar2(255) := 'IP';
P_smtp_portnum varchar2(5) := '25';
[code].......
The code is taken an example from internet source. Everything works fine but the UTF-8 not .
View 21 Replies
View Related
Sep 14, 2010
I am sending mail thru UTL_SMTP. But i am unable to send the CLOB in it.If i break the CLOB into varchar2 then also it doesn't work.
Variables:-
vBuffer VARCHAR2 (4000);
l_amount BINARY_INTEGER := 4000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
[code].....
While using the write_data procedure it is giving error as "Invalid OPeration"...how we can send the mail with CLOB.
View 1 Replies
View Related
Jul 17, 2012
OS: RHEL 5.7 64 bit
DB: 11.2.0.2 Standard Edition 64 bit
Everyday EOD is run and after the eod, users are requesting to receive a mail confirming the same from the database. For this we need to configure automated email which will be sent to a list of users email ID immediately after the EOD is done.
View 3 Replies
View Related
Apr 2, 2010
i used UTL_SMTP to send email. it works fine and send email. but when i used UTL_MAIL, its throwing below error
Error at line 1
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_MAIL", line 395
ORA-06512: at "SYS.UTL_MAIL", line 608
ORA-06512: at line 2;
i ran below
begin
utl_mail.send('abc.def@xyz.com',
'abc.def@xyz.com','Testing UTL_MAIL Option','blah blah blah');
end;
if utl_smtp is working, means SMTP server is configured properly. then why am i not able to get email using UTL_MAIL ?
utl_mail hav public grant.
also i smtp_out_server has value in v$parameter :
select * from v$parameter where name = 'smtp_out_server';
View 7 Replies
View Related
May 26, 2012
Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
[code]......
View 7 Replies
View Related
Jan 19, 2010
Am facing a problem in sending an email to certain group of people, if the recipient just one person it will work but if more it will not work, I tried to read every one as a single recipient through a cursor but it was not working, the following is the sending
v_mail=:control.test;
mail_pkg.send
( p_sender_email => 'test@test.com',
p_from => 'test@test.com',
p_to => mail_pkg.array(V_MAIL),
[code].......
:control.test data is quotes between to quotes for each address.
View 2 Replies
View Related
Aug 17, 2010
Need pl/sql block to fetch all the emails of all the employees from the database and separate them by semicolon....
View 4 Replies
View Related
Apr 9, 2013
Using UTL_MAIL package.
My server version is:- 10.2.0.3.0
Here is the INIT Parameter file.
My question is, how it will affect if I configure the UTL_MAIL package by executing the Scripts ("utlmail.sql" and "prvtmail.plb")? And setting the Parameter SMTP_OUT_SERVER=''?
View 8 Replies
View Related
Aug 8, 2012
SQL> conn system/sys@ORCL;
Connected.
SQL> @F:oracleproduct10.2.0client_5RDBMSADMINutlmail.sql
Package created.
Synonym created.
SQL> @F:oracleproduct10.2.0client_5RDBMSADMINprvtmail.plb
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY UTL_MAIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
319/5 PL/SQL: SQL Statement ignored
320/10 PL/SQL: ORA-00942: table or view does not exist
325/7 PL/SQL: SQL Statement ignored
326/12 PL/SQL: ORA-00942: table or view does not exist
View 2 Replies
View Related
Aug 3, 2011
I have 2 questions :
1. How many oracle mechanism can link with oracle send mail mechanism ??
2. Any samples which the oracle send mail mechanism to run with some oracle alert mechanism.
View 3 Replies
View Related
Dec 29, 2011
user can't use utl_smtp. I tried - [URL] can use utl_smtp but other user can't.
I selected:
SELECT NVL(dbms_network_acl_admin.check_privilege_aclid(aclid, 'user123', 'connect'), 0)
FROM xds_acl;
And the result is:
1
0
0
0
0
0
0
0
0
0
0
0
View 6 Replies
View Related
Jul 7, 2012
We can send E-mail through Oracle DB, can we send SMS through Oracle DB to any Mobile No.
View 1 Replies
View Related
Mar 5, 2012
i am sending { Daily Status } via E-Mail by Oracle 10g,,,,
now My client want to Receive By sms to his mobile ,,,
Is there any possibilities to sent sms by oracle 10g ,,if exists pls sent the Code,,,or any procedure to sent sms via Oracle 10g
View 11 Replies
View Related
Feb 17, 2004
how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.
View 9 Replies
View Related
Mar 5, 2012
I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
View 4 Replies
View Related
Apr 24, 2013
what is the best way to send an email in oracle?
1.utl_smtp
2.utl_mail
3.utl_tcp
View 6 Replies
View Related
Feb 4, 2013
I have successfully implemented the from which I can send SMS using Forms 6i and any mobile modem connected with OS.Now you can send SMS using Oracle Forms 6i.
View 15 Replies
View Related
Jul 26, 2011
I had a question :
How to send mail from Oracle ??
Implement utl_mail??
How to implement ?? , or have any examples for reference??
View 6 Replies
View Related
Feb 7, 2012
I want to attache an external file to a email using UTL_SMTP package.
A file which attach will be present in directory.
View 3 Replies
View Related
Jul 23, 2012
I have a procedure called SANDEEP_INC105657, which calls another procedure called "Send_Prodcontrol_Mail_Pr". This procedure should be sending out an email with the following message:
From: <sandeep.tanjore@xxx.xx.ca>
Subject: Remit Advice Slips Report (TR000336).
To: sandeep.tanjore@xxx.xx.ca
Report TR000336 - Remit Advice Slips.
Total number of records that needs slips printed are: 1
However what's happening is we had a database change from 10g to 11g and ever since this has happened the email is sent with "From", "Subject" and "To" but the body(content of the email : Total number of records that needs slips printed are: 1) is missing.The code in Send_prodcontrol_Mail_pr is as follows:
CREATE OR REPLACE PROCEDURE Send_Prodcontrol_Mail_Pr( v_mailhost IN VARCHAR2,
v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subject IN VARCHAR2,
v_message1 IN VARCHAR2,
v_message2 IN VARCHAR2,
v_message3 IN VARCHAR2)
[code]....
Any reason why the body of the email is missing? I tried the following:
replaced utl_smtp.data(mail_conn,mesg); with utl_smtp.write_data(mail_conn, utl_tcp.CRLF||mesg);
It sends out the email as required but it repeats the "From" two times with no "TO", and "Subject"... however then embeds the whole "from" "to" and the body in an email.
View 4 Replies
View Related
Jun 21, 2013
I am trying to add an image as attachment to an e-mail.
I am reading the image from the table and then passing it as a BLOB in my procedure.
Following is the part of the code which adds the attachment
{code}
PROCEDURE add_mail_attachment
(
/*
[Code].....
View 10 Replies
View Related
Dec 21, 2009
Am trying to send email from Oracle using utl_smtp.
My issue is regarding Arabic character. I set the charset to windows-1256 and even tried with other different charset like UTF-8.
I used also: utl_smtp.write_raw_data(l_connection,utl_raw.cast_to_raw(dbms_lob.substr(data).
Before i set all these changes to the procedure i was receiving the Arabic text as '??????'
However, after above modifications, the text display changed to something like this : ÇäÇÓààååÑêå
Does the problem still in the DB side or it can be coming from mail server ?
View 24 Replies
View Related
Jun 29, 2012
i want to send sms through gsm modem in oracle form 6i.
View 3 Replies
View Related
Jun 21, 2013
i want to send mail from database for this
C:UsersAdministrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 21 16:03:04 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys@orcl as sysdba
Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> @ F:oracleproduct10.2.0db_1RDBMSADMINutlmail.sql
[code]....
View 17 Replies
View Related
Sep 27, 2011
I wanted to send email from oracle 10g forms at any hotmail,yahoo and gmail so how can i send.
View 16 Replies
View Related
Jun 23, 2010
I have ORacle XE installed on my machine and everything works fine. I can connect to it using forms, run my application etc. Now, I wanted to write a proc which can send email from Oracle XE.So after investigating, I think UTL_SMTP and UTL_MAIL are my options. For which I need to enable the ut_mail package and one more which I have done. But everytime I run a package, I get the error
DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'test@mycompany.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
[code].....
Ora 29278 SMTP transient error.
View 13 Replies
View Related
