SQL & PL/SQL :: Unable To Send Mail Using UTL SMTP
Mar 6, 2013
I am using utl_smtp to send emails. I am getting the below error message.
Error in XX(Package name) ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO" at - 5150
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 6000
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7250
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 8450.
I have checked the mail id in the "TO" list and found them to be valid. Is there could be any other reason? I have run this package in test instance and it is working fine. I am able to receive mails. In the test instance, I have put my mail id in the to list.
I m using UTL_SMTP to send mail using my mail server. could i check the availability of 1st mail server, if not available then could i set alternate mail server.
I have created a procedure and i am scheduling the procedure to run in dbms jobs. In the procedure, i am using UTL_SMTP connection to receive mail everything is working fine it. In the receipent if i give one email id it is working fine. if i give multiple email id in it am getting error.
BEGIN dbms_network_acl_admin.add_privilege (acl=> 'mails_senden.xml', principal=> 'USER2', is_grant=> true, privilege=> 'connect');
[code]....
USER1 sent a mail via UTL_SMTP successful whereas USER2 got the errors:
DECLARE * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 17 ORA-06512: at "SYS.UTL_TCP", line 267
[code]....
There is still the following fact:
USER1 has the role DBA, USER2 got the EXECUTE privilege to packages UTL_TCP, UTL_SMTP (GRANT from SYS).
IF the role DBA was granted to USER2 too then he couldt send emails just as well as USER1.
ERROR at line 1: ORA-29279: SMTP permanent error: 550 <account@test2.com[/email]> No such user here ORA-06512: at "SYS.UTL_SMTP", line 21 ORA-06512: at "SYS.UTL_SMTP", line 99 ORA-06512: at "SYS.UTL_SMTP", line 241 ORA-06512: at "SYS.UTL_MAIL", line 424 ORA-06512: at "SYS.UTL_MAIL", line 594 ORA-06512: at line 2
how to create trigger or procedure for send all ORA- error alert mail from database. if any ORA - error comes in ALERT_SID.log then after trigger send the mail to my mail id
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 want to send mail with xml attachment.i create a procedure to send mail, when i send pdf,text file, they are open properly but if i send xml file i does not open...
/* Formatted on 2010/09/24 15:25 (Formatter Plus v4.8.8) */ CREATE OR REPLACE PROCEDURE apps.send_mail AS p_sender VARCHAR2 (80) := 'my@company.com'; p_recipient VARCHAR2 (80)
Send a CSV file as attachment in a mail PLSQL. MY CSV file is already present in server. I just need to pickup that file and send the same as mail attachment.
if i click the column it shows the text output(various tags ,symbols)
if i click the HTML it shows the correct format .,
i m sending the mail using that message column .,the mail Received with the text format ,actually i need it in the html format., How to send HTML output?
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
SELECT DAY_NBR ||' '||HOUR_NBR ||' '||MINUTE_NBR AS "@TIMEPOINT", RTRIM ( xmlagg (xmlelement (c, procedure_nm || ', ')).extract ('//text()'), ', ' ) AS "@LABEL", RTRIM ( xmlagg (xmlelement (c, procedure_id || ', ')).extract ('//text()'), ', ' ) AS "@PROCEDURE_ID", cursor (SELECT SUBJECT_NBR AS "@LABEL",TO_CHAR (PSUB.procedure_tm,'HH24:MI') AS "@TIME", PROCEDURE_ID AS "@PROCEDURE_ID", PROCEDURE_SUB_ID AS "@PROCEDURE_SUB_ID" FROM PROCEDURE_SUBJECT_TIME PSUB WHERE PSUB.ACTIVE_FLG = 1 AND PSUB.PROCEDURE_ID in pr.PROCEDURE_ID ORDER BY PSUB.procedure_tm,PROCEDURE_ID )AS VOLUNTEER FROM SCHEDULE_PROCEDURE PR WHERE active_flg = 1 and SCHEDULE_ID = 200900195269 group BY day_nbr,hour_nbr,minute_nbr,procedure_nm;
and the error getting is ORA-00979: not a GROUP BY expression
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
I am succeeded to send mail (through my comapany mail server) using following pl/sql. But, i am unable to send mails using the same coding, when i connected to smtp.gmail.com. i received the following error :
ERROR at line 1: ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first y78sm463148pyg ORA-06512: at "SYS.UTL_SMTP", line 17 ORA-06512: at "SYS.UTL_SMTP", line 98
[code]...
Then I received the following error message: ERROR at line 1: ORA-29278: SMTP transient error: 454 TLS not available due to temporary reason ORA-06512: at "SYS.UTL_SMTP", line 17 ORA-06512: at "SYS.UTL_SMTP", line 96
[code]...
my demo_mail package body coded as
FUNCTION begin_session RETURN utl_smtp.connection IS conn utl_smtp.connection; BEGIN -- open SMTP connection
I am using Oracle 10.2.0.4, on redhat linux 5.1 and microsoft exchange 2003.
I have created below mentioned Procedure to send my email...I am able to send the mails successfully inside the premises (in same domain). But when i send them to another domain (Outside)..I get the error "SMTP permanent error: 503 5.5.2 Send hello first."
CREATE OR REPLACE PROCEDURE E_Mail( p_to IN VARCHAR2, p_from IN VARCHAR2, p_subject IN VARCHAR2, p_text IN VARCHAR2 DEFAULT NULL,
Intention is to run the UTL_SMTP and send a mail to my mail id in gmail. Host used is localhost.
Oracle version is 11g
Since my machine is running on Windows 7 and since SMTP server is no more part of IIS, I have not configured any SMTP server for the Operating system.When I run the plsql block , I get the error.
ORA-29278: SMTP transient error: 421 Service not available
see the code below. Some suggesting the SMTP SERVER configuration in Operating system level.
I created the acl list and assigned acls
--Creating acls begin dbms_network_acl_admin.create_acl ( acl => 'utl_smtp.xml', description => 'Allow mail to be send',
