SQL & PL/SQL :: Using UTL_SMTP To Send Simple Emails
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.
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)
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.
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
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.
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 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.
I have to embed a picture as a header with an e-mail content for my work, I google'd and tested with different options but I failed. (The JPG is stored in a table as a blog object) When I tried with the below code the JPG file is e-mailed as an attachment with an e-mail content, I want it to be displayed inside an e-mail.
I tried att_inline: TRUE option as well - still not working.
Here is my code I tested and worked as an attachment.
1. i register scheme like this DECLARE schemaurl VARCHAR2(256); xmlschema xmltype; BEGIN
[code]...
/2. i try to insert XML insert into TMP_SPM_ACTLOGLINE values (xmltype( '<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
[code]...
The question is how should the scheme look like to insert this XML?
I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:
update MASTERMICODES t1 set t1.TEMPTA = ( select t2.TAFCODE from TA_FEATURES t2 where t2.FCODE = t1.FCODE )
It returns Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action:
I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_T (PRODUCT_ID)); (where the second one has a space between PRODUCT_T and (PRODUCT_ID));
I have three SQLS which I am running using "Union"
e.g
select col1,count(*) from table where type ='parts' group by col1 union select col1,count(*) from table where type ='consumables' group by col1 union select 'Total',count(*) from table group by 'Total'
I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!
I get "ORA-00936: missing expression" when I issue the following SQL:
select 4 - .2 from dual
from the following VB.NET code (3.5 framework):
Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn) conn.Open() Dim da As Oracle.DataAccess.Client.OracleDataAdapter Dim ds As New DataSet da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn) da.Fill(ds) MsgBox(ds.Tables(0).Rows(0)(0))
I have the following installed when connecting to 10.2.0.3 database: 11.2.0.1 client installed 11.2.0.3.20 ODP installed
This is really strange behaviour as the following SQL variants work: select 4 - 0.2 from dual select 4 - '.2' from dual select -.2 + 4 from dual
This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11
am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate
e.g
Jan 1 - 30 deletes 2 - 30 keeps data for 1st Feb 1 - 28 deletes 2 - 28 keeps data for 1st Mar Apr Current sysdate May
I am trying to use OCIColl datatype and thus wanted to check if a simple program using OCI functions and data types gets compile or not. sO I wrote the follwoing function which I want to compile as a DLL.
extern "C" CLUSTERINGDLL_API OCIColl *fnClusteringDLL(OCIColl *dxm[]) { OCIError *errhp; OCINumber num_1, num_2, sum; OCINumberAdd(errhp, &num_1, &num_2, &sum); std::cout<<"Members in the array are "<<dxm[1]<<std::endl; std::cout<<"2 Member in the array are "<<dxm[0]<<std::endl; printf("Is this printed %f", dxm[0]); return dxm[1]; }
But I am getting the following error:- Error6error LNK2019: unresolved external symbol _OCINumberAdd referenced in function _fnClusteringDLLclusteringdll.obj...
I have included the library folder containing oci.lib in the project directories.
Linux 2.4.21-37.ELsmp #1 SMP Wed Sep 7 13:28:55 EDT 2005 i686 i686 i386 GNU/Linux...As you'll see I do not understand anything about PRO*C. I need to compile this PRO*C code to use it to retrieve DBMS PIPES being sent.
I'm trying to compile it using the following command: gcc -o IAPIPE IAPIPE.c
The code - file IAPIPE.c #include <stdio.h> #include <string.h>
EXEC SQL BEGIN DECLARE SECTION; VARCHAR username[20]; int status; int msg_length; char retval[2000]; EXEC SQL END DECLARE SECTION; [code]....
Why does is the code blow reuturning a null for a simple subtraction and division?I am trying to do 4-13/9 for one and 4-22/9 for 22
select unit, term_1201,term_1202,r_slope,r_intercept, (r_intercept - term_1201) / decode((r_slope),0,1) as ONE, (r_intercept - term_1202) / decode((r_slope),0,1) as TWO
