This discussion is archived
2 Replies Latest reply: Apr 22, 2012 2:43 PM by 806917 RSS

ORA-29277: invalid SMTP operation

user13529823 Newbie
Currently Being Moderated
Hi,

I have a package to send mails from oracle and it is using UTL_SMTP to send the mail, this package is configured as job and it will be running four time per day.

when it was running at first time, i was getting the below error.

ORA-29277: invalid SMTP operation

I am unable to identify which operation or which flow is going wrong. Can any body help me on the same.

Below is my code.

Executealert procedure will executing first then RowBasedAlarm will start execute. From RowBasedAlarm you can find the flow

create or replace package body AlarmManagement is

CARRIAGERETURN CONSTANT CHAR(1) := chr(13);
NEWLINE CONSTANT CHAR(1) := chr(10);

BOUNDARY CONSTANT VARCHAR2(256) := '7D81B75CCC90D2974F7A1CBD';
FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY ||
CARRIAGERETURN || NEWLINE;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
CARRIAGERETURN || NEWLINE;
v_vectorreply utl_smtp.reply;

v_mailDate varchar2(20);
v_executionDate Date;

procedure proc_init is

begin
SELECT valuesql into v_sql FROM alerts WHERE alert_ID = v_alertId;

SELECT procedurename
into v_procedure
FROM alerts
WHERE alert_ID = v_alertId;

SELECT parameters
into v_parameter
FROM alerts
WHERE alert_ID = v_alertId;

SELECT querytype
into v_querytype
FROM alerts
WHERE alert_ID = v_alertId;

select alertname, message
into v_alert_name, v_message
from alerts a
where a.alert_id = v_alertId;

select setting
into v_smtp_gateway
from settings s
where s.parameter = 'AlrtSmtpGwAddr';

select setting
into v_smtp_gateway_port
from settings s
where s.parameter = 'AlrtSmtpGwAddrPort';

select setting
into v_emailID
from settings s
where s.parameter = 'AlrtSmtpFromID';

select setting
into v_pwd
from settings s
where s.parameter = 'AlrtSmtpFromIDPwd';

select t.isthreshold
into v_alertType
from alerts t
where alert_ID = v_alertId;

select t.threshold
into v_threshold_alert
FROM alerts t
WHERE alert_ID = v_alertId;

select s.setting
into v_AlrtSMSGateway
from settings s
where s.parameter = 'AlrtSMSGateway';

v_executionDate := localtimestamp;
select to_char(v_executionDate, 'MM/DD/YYYY HH24:MI:SS')
into v_mailDate
from dual;

select t.setting
into v_AuthLogin
from settings t
where upper(t.parameter) = upper('AlrtAuthLogin');

exception
when others then
null;
end proc_init;

procedure OpenSmtpConnection(v_mesgType in varchar2,
v_toAddress in varchar2) as
v_add_src varchar2(1000);
slen number(10) := 1;
v_addr varchar2(1000);
begin
v_vectorreply := utl_smtp.open_connection(v_smtp_gateway,
v_smtp_gateway_port,
v_mailConn);
v_vectorreply := utl_smtp.helo(v_mailConn, v_smtp_gateway);

if (v_AuthLogin = 1) then
--if no authentication is used for smtp , use siteornot as 1
if (v_emailID is not null and v_pwd is not null) then
utl_smtp.command(v_mailConn, 'AUTH LOGIN');
utl_smtp.command(v_mailConn,
demo_base64.encode(utl_raw.cast_to_raw(v_emailID)));
utl_smtp.command(v_mailConn,
demo_base64.encode(utl_raw.cast_to_raw(v_pwd)));
end if;
end if;

v_vectorreply := utl_smtp.mail(v_mailConn, v_emailID);
if v_mesgType = 'ATCH' or v_mesgType = 'MAIL' then

if (instr(v_toAddress, ',') = 0) then
utl_smtp.rcpt(v_mailConn, v_toAddress);
else
v_add_src := ltrim(rtrim(v_toAddress)) || ',';
while (instr(v_add_src, ',', slen) > 0) loop
v_addr := substr(v_add_src,
slen,
instr(substr(v_add_src, slen), ',') - 1);
slen := slen + instr(substr(v_add_src, slen), ',');
utl_smtp.rcpt(v_mailConn, v_addr);
end loop;
end if;
v_vectorreply := utl_smtp.rcpt(v_mailConn, v_toAddress);
v_usr_mail := v_toAddress;
elsif v_mesgType = 'SMS' then
v_vectorreply := utl_smtp.rcpt(v_mailConn,
v_toAddress || '@' || v_AlrtSMSGateway);
v_usr_mail := v_toAddress || '@' || v_AlrtSMSGateway;
end if;
v_vectorreply := utl_smtp.open_data(v_mailConn);
exception
when others then
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
insert into alerterrors
values
(localtimestamp, dbms_utility.format_error_backtrace);
commit;
null;
end OpenSmtpConnection;

procedure CloseSmtpConnection(v_mesgType in varchar2) as
begin

if v_mesgType = 'ATCH' then
utl_smtp.write_data(v_mailConn, LAST_BOUNDARY);
utl_smtp.write_data(v_mailConn, ' ' || CARRIAGERETURN || NEWLINE);
end if;
v_vectorreply := utl_smtp.close_data(v_mailConn);
utl_smtp.quit(v_mailConn);
exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
null;
end CloseSmtpConnection;

procedure WriteMimeType(v_mesgType in varchar2, v_username in varchar2) as
begin

utl_smtp.write_data(v_mailConn,
'Subject: ' || 'Alarm Result: ' || v_alert_name ||
CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn,
'From: ' || v_emailID || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
'To: ' || v_usr_mail || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
'Date: ' || v_mailDate || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
'MIME-version' || ': 1.0' || CARRIAGERETURN ||
NEWLINE);
if v_mesgType = 'SMS' then

utl_smtp.write_data(v_mailConn,
'Content-Type: text/plain' || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
CARRIAGERETURN || NEWLINE || CARRIAGERETURN ||
NEWLINE);
elsif v_mesgType = 'ATCH' then

utl_smtp.write_data(v_mailConn,
'Content-Type' ||
': multipart/mixed; boundary="7D81B75CCC90D2974F7A1CBD"' ||
CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn, CARRIAGERETURN || NEWLINE);

utl_smtp.write_data(v_mailConn, FIRST_BOUNDARY);
utl_smtp.write_data(v_mailConn,
'Content-Type' || ': text/plain' ||
CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn, CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn,
'Hi ' || v_username || ', ' || CARRIAGERETURN ||
NEWLINE || 'Alert details are as follows: ' ||
CARRIAGERETURN || NEWLINE || 'Alert Name: ' ||
v_alert_name || CARRIAGERETURN || NEWLINE ||
'Message: ' || v_message || CARRIAGERETURN ||
NEWLINE || 'Generated on: ' || v_mailDate);
utl_smtp.write_data(v_mailConn,
CARRIAGERETURN || NEWLINE || CARRIAGERETURN ||
NEWLINE);

utl_smtp.write_data(v_mailConn, FIRST_BOUNDARY);
utl_smtp.write_data(v_mailConn,
'Content-Disposition: inline; filename="' ||
(v_alert_name) || '.csv"' || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
'Content-Type' || ': text/plain; charset="UTF-8"
' || CARRIAGERETURN || NEWLINE);

utl_smtp.write_data(v_mailConn, CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn,
CARRIAGERETURN || NEWLINE || CARRIAGERETURN ||
NEWLINE);
elsif v_mesgType = 'ATCH' then
utl_smtp.write_data(v_mailConn,
'Content-Type: text/plain' || CARRIAGERETURN ||
NEWLINE);
utl_smtp.write_data(v_mailConn,
CARRIAGERETURN || NEWLINE || CARRIAGERETURN ||
NEWLINE);
end if;
exception
when others then
v_errmsg := SQLERRM || CARRIAGERETURN || NEWLINE ||
dbms_utility.format_error_backtrace;
insert into alerterrors values (SYSDATE, v_errmsg);
commit;
null;
end WriteMimeType;

procedure WriteSmsAlarm(v_smsType in boolean,
v_setThreshold in varchar2,
v_actualThreshold in varchar2) is
mesg varchar2(165);
begin

if (v_smsType = true) then
mesg := 'Alert Name:' || v_alert_name || ', Set Threshold is: ' ||
v_setThreshold || ', Actual Threshold is: ' ||
v_actualThreshold;
else
/*mesg := 'Alert Name:' || v_alert_name || ' has been generated at ' ||
v_mailDate;*/
mesg := v_alert_name || '; ' || v_message;
end if;

utl_smtp.write_data(v_mailConn, mesg || CARRIAGERETURN || NEWLINE);
utl_smtp.write_data(v_mailConn, ' ' || CARRIAGERETURN || NEWLINE);

EXCEPTION
when others then
null;

end WriteSmsAlarm;

procedure writeAttachment(v_content in varchar2) is
begin
utl_smtp.write_data(v_mailConn, v_content || CARRIAGERETURN || NEWLINE);
exception
when others then
null;
end writeAttachment;

procedure writeThresholdAlarm(v_username in varchar2,
v_setThreshold in varchar2,
v_actualThreshold in varchar2) is
mesg varchar2(500);
begin
mesg := 'Hi ' || v_username || ', ' || CARRIAGERETURN || NEWLINE ||
'Alert details are as follows: ' || CARRIAGERETURN || NEWLINE ||
'Alert Name: ' || v_alert_name || CARRIAGERETURN || NEWLINE ||
'Message: ' || v_message || CARRIAGERETURN || NEWLINE ||
'Set Threshold for the alert is: ' || v_setThreshold ||
CARRIAGERETURN || NEWLINE ||
'Actual Threshold for the alert is: ' || v_actualThreshold ||
CARRIAGERETURN || NEWLINE;
utl_smtp.write_data(v_mailConn, mesg || CARRIAGERETURN || NEWLINE);
exception
when others then
null;
end writeThresholdAlarm;

PROCEDURE ThresholdBasedAlarm as
begin

If v_querytype = 1 Then
If v_parameter IS NULL Then
v_procedure := 'Begin ' || v_procedure || '; End;';
execute immediate (v_procedure);
Else
v_procedure := 'Begin ' || v_procedure || '(' || v_parameter || ')' ||
'; End;';
execute immediate (v_procedure);
End if;
End if;

EXECUTE IMMEDIATE v_sql
into v_threshold_query;
IF (v_threshold_alert <= v_threshold_query) and (v_run = 0) THEN

FOR item IN (select su.firstname username,
n.mobileno mobiletype,
n.email emailtype,
u.mobileno mobileno,
u.email_id emailid
from security_users su, notifications n, userdetails u
where su.userid = n.user_id
and u.user_id = n.user_id
and (n.email = 1 or n.mobileno = 1 or n.netsend = 1)
and n.alert_id = v_alertid) LOOP
if item.emailtype = 1 then
OpenSmtpConnection('MAIL', item.emailid);
WriteMimeType('MAIL', item.username);
WriteThresholdAlarm(item.username,
v_threshold_alert,
v_threshold_query);
CloseSmtpConnection('MAIL');

end if;
if item.mobiletype = 1 then
OpenSmtpConnection('SMS', item.mobileno);
WriteMimeType('SMS', item.username);
WriteSmsAlarm(true, v_threshold_alert, v_threshold_query);
CloseSmtpConnection('SMS');
null;
end if;

END LOOP;
SELECT AlertResults_Run_ID_SEQ.nextval into v_runID from dual;

INSERT INTO AlertResults
(Run_ID, Alert_ID, Threshold, Result, Datestamp, alert_type)
VALUES
(v_runID,
v_alertID,
v_Threshold_Alert,
to_char(v_threshold_query),
v_executionDate,
v_alertType);
END IF;

EXCEPTION
when others then
v_errmsg := SQLERRM || CARRIAGERETURN || NEWLINE ||
dbms_utility.format_error_backtrace;
insert into alerterrors values (SYSDATE, v_errmsg);
commit;
null;
end ThresholdBasedAlarm;

procedure RowBasedAlarm as

v_tableName varchar2(2000) := 'ALERT' || '_' ||
to_char(localtimestamp,
'dd_mm_yy_hh_mi_ss');
v_query varchar2(5000) := 'select ';
v_col varchar2(5000);
v_mailcol varchar2(5000);
refcur_query sys_refcursor;
v_query_content varchar(32000);
v_query_mailcontent varchar(32000);
v_user_flag boolean := true;
lv_count number(5) := 0;

cursor v_user_cur is
select su.firstname username,
n.mobileno mobiletype,
n.email emailtype,
u.mobileno mobileno,
u.email_id emailid
from security_users su, notifications n, userdetails u
where su.userid = n.user_id
and u.user_id = n.user_id
and (n.email = 1 or n.mobileno = 1 or n.netsend = 1)
and n.alert_id = v_alertid;

cursor v_col_cursor is
select t.column_name
from all_tab_cols t, security_users su
where t.owner = upper(su.username)
and su.userid = 0
and t.table_name = upper(v_tableName)
order by t.internal_column_id;

begin

select t.setting
into v_maxRecords
from settings t
where t.parameter = 'AlertMaxRecords';

select t.setting
into v_FieldSeperator_Alert
from settings t
where upper(t.parameter) = upper('FieldSeperatorForAlertDetails');

select t.setting
into v_listSeparator
from settings t
where upper(t.parameter) = upper('listSeparator');

If v_querytype = 1 Then
If v_parameter IS NULL Then
v_procedure := 'Begin ' || v_procedure || '; End;';
execute immediate (v_procedure);
Else
v_procedure := 'Begin ' || v_procedure || '(' || v_parameter || ')' ||
'; End;';
execute immediate (v_procedure);
End if;
End if;

execute immediate 'create table ' || v_tableName ||
' as ( select * from (' || v_sql || '))';

execute immediate 'select count(*) from ' || v_tableName
into v_count_records;

if v_count_records != 0 then

for row_cur in v_col_cursor loop
v_query := v_query || 't."' || row_cur.column_name || '"||''' ||
v_FieldSeperator_Alert || '''||';
v_col := v_col || row_cur.column_name || v_FieldSeperator_Alert;
end loop;

v_rowcount := 0;
v_col := Substr(v_col,
1,
length(v_col) - length(v_FieldSeperator_Alert));
v_query := Substr(v_query,
1,
length(v_query) - 6 -
length(v_FieldSeperator_Alert)) ||
' as ColumnEntry from ' || v_tableName || ' t';
if lv_count = 0 then
insert into alertresults_detail
values
(v_alert_name, v_col, v_executionDate, v_rowcount);
end if;

for row_cur in v_user_cur loop
v_rowcount := 0;
v_user_flag := false;

if row_cur.mobiletype = 1 then
OpenSmtpConnection('SMS', row_cur.mobileno);
WriteMimeType('SMS', row_cur.username);
WriteSmsAlarm(false, '', '');
CloseSmtpConnection('SMS');
end if;
if row_cur.emailtype = 1 then
OpenSmtpConnection('ATCH', row_cur.emailid);
WriteMimeType('ATCH', row_cur.username);
v_mailcol := replace(v_col,
v_FieldSeperator_Alert,
v_listSeparator);
writeAttachment(v_mailcol);
end if;

open refcur_query for v_query;
loop
fetch refcur_query
into v_query_content;

exit when(refcur_query%NOTFOUND or v_rowcount >= v_maxRecords);

v_rowcount := v_rowcount + 1;
if lv_count = 0 then
insert into alertresults_detail
values
(v_alert_name, v_query_content, v_executionDate, v_rowcount);
end if;
v_query_mailcontent := replace(v_query_content,
v_FieldSeperator_Alert,
v_listSeparator);
writeAttachment(v_query_mailcontent);

end loop;
CloseSmtpConnection('ATCH');
lv_count := lv_count + 1;
end loop;

if v_user_flag = true then
open refcur_query for v_query;
loop
fetch refcur_query
into v_query_content;

exit when(refcur_query%NOTFOUND or v_rowcount >= v_maxRecords);

v_rowcount := v_rowcount + 1;

insert into alertresults_detail
values
(v_alert_name, v_query_content, v_executionDate, v_rowcount);

end loop;
end if;

SELECT AlertResults_Run_ID_SEQ.nextval into v_runID from dual;
If v_alertID = 41 and v_querytype <> 1 then

INSERT INTO AlertResults
(Run_ID, Alert_ID, Threshold, Result, Datestamp, alert_type)
VALUES
(v_runID,
v_alertID,
v_threshold_alert,
to_char(v_rowcount) || 'Routing Plans voilated the QOS Values.',
v_executionDate,
v_alertType);
Else
INSERT INTO AlertResults
(Run_ID, Alert_ID, Threshold, Result, Datestamp, alert_type)
VALUES
(v_runID,
v_alertID,
v_threshold_alert,
to_char(v_rowcount) || ' Records',
v_executionDate,
v_alertType);
End if;

end if;

execute immediate 'drop table ' || v_tableName;

exception
when others then

v_errmsg := SQLERRM || CARRIAGERETURN || NEWLINE ||
dbms_utility.format_error_backtrace || v_alertID;
insert into alerterrors values (SYSDATE, v_errmsg);
commit;
execute immediate 'drop table ' || v_tableName;

end RowBasedAlarm;

PROCEDURE ExecuteAlert(alertId IN OUT varchar2) AS

BEGIN

v_alertId := alertId;
proc_init;

if v_alertType = 1 then
ThresholdBasedAlarm;
else
RowBasedAlarm;
end if;

end;

end AlarmManagement;

Edited by: user13529823 on Apr 20, 2012 4:44 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points