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)
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.
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.
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?
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.
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';
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]......
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
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=''?
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.
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.
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.
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.
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:
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.
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
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
