Email+triggers+oracle

[jith87]

hi all,
i need to call a proceedure for sending email by setting up a "INSERT AFTER" trigger for a table.i need to pass the mail id as a parameter to the procedure for sending email. that procedure works just fine.the probelm is when i try to dclare variables inside the trigger and run it i get a warning message:Warning: Trigger created with compilation errors.
here is my code for the trigger:

CREATE OR REPLACE TRIGGER scheck 
AFTER  insert
ON memorydetail
FOR EACH ROW
 
DECLARE
tomsg    mailid.mail_id%type;
tomsg:='[e-mail]';
BEGIN
IF (to_number(new.WS)>50000) THEN
smail(tomsg,'Hello from Oracle','This is the body of the message');
END IF
END;
.
run;
 

even if i try to change the datatype of the variable "tomsg" i get the warning message and the email is not sent.
but if i explicitly give the email id in the call to "smail" like

smail('[e-mail]', 'Hello from Oracle', 'This is the body of the message');
 

it works.
where am i going wong???
can anybody help?

[amitpatel66]

hi all,
i need to call a proceedure for sending email by setting up a "INSERT AFTER" trigger for a table.i need to pass the mail id as a parameter to the procedure for sending email. that procedure works just fine.the probelm is when i try to dclare variables inside the trigger and run it i get a warning message:Warning: Trigger created with compilation errors.
here is my code for the trigger:
<code>
CREATE OR REPLACE TRIGGER scheck
AFTER insert
ON memorydetail
FOR EACH ROW

DECLARE
tomsg mailid.mail_id%type;
tomsg:='[e-mail]';
BEGIN
IF (to_number(new.WS)>50000) THEN
smail(tomsg,'Hello from Oracle','This is the body of the message');
END IF
END;
.
run;
</code>
even if i try to change the datatype of the variable "tomsg" i get the warning message and the email is not sent.
but if i explicitly give the email id in the call to "smail" like
<code>
smail('[e-mail]', 'Hello from Oracle', 'This is the body of the message');
</code>
it works.
where am i going wong???
can anybody help?

Please POST the warning message that you are getting for my reference.

[debasisdas]

If you are getting the message Trigger created with compilation errors. that means the trigger is not compiled and contains some errors.

[shakmca]

hi all,
i need to call a proceedure for sending email by setting up a "INSERT AFTER" trigger for a table.i need to pass the mail id as a parameter to the procedure for sending email. that procedure works just fine.the probelm is when i try to dclare variables inside the trigger and run it i get a warning message:Warning: Trigger created with compilation errors.
here is my code for the trigger:

CREATE OR REPLACE TRIGGER scheck 
AFTER  insert
ON memorydetail
FOR EACH ROW
 
DECLARE
tomsg    mailid.mail_id%type;
tomsg:='[e-mail]';
BEGIN
IF (to_number(new.WS)>50000) THEN
smail(tomsg,'Hello from Oracle','This is the body of the message');
END IF
END;
.
run;
 

even if i try to change the datatype of the variable "tomsg" i get the warning message and the email is not sent.
but if i explicitly give the email id in the call to "smail" like

smail('[e-mail]', 'Hello from Oracle', 'This is the body of the message');
 

it works.
where am i going wong???
can anybody help?

Put the exception in the trigger and execute the trigger.
add the following code in trigger script
then it will display the complete error message.

when others then
raise_application_error(-20001,'Error '||sqlerrm);

[jith87]

i am able to create the trigger successfully. but the problem is at times i don get the email. infact i get the following eror message:
<code>
ERROR at line 1:
ORA-29278: SMTP transient error: 454 5.7.3 Client does not have permission to
submit mail to this server.
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "NATHIYA.STATUS_CHECK", line 8
ORA-04088: error during execution of trigger 'NATHIYA.STATUS_CHECK'
</code>

this is my trigger:
<code>
CREATE OR REPLACE TRIGGER scheck
AFTER insert ON memorydetail

FOR EACH ROW
WHEN (to_number(new.WS)>50000)
DECLARE
ipad memorydetail.ipaddress%type:= :new.ipaddress;to_msg varchar2(50);mpid memorydetail.pid%type:=:new.pid;rn integer:=0;tempampm varchar2(3);temphr integer;thr integer;tempmin integer;tmin integer;temp_time varchar2(10);
BEGIN
tempampm:=trim(substr(:new.time,7,8));
temphr:=to_number(trim(substr(:new.time,1,2)));
tempmin:=to_number(trim(substr(substr(:new.time,4, 5),1,2)));
tmin:=tempmin-3;
thr:=temphr;
if (tmin<0) then
tmin:=60+tmin;
if (thr=12) then
tempampm:='AM';
end if;
thr:=temphr-1;
end if;
if (thr<10) then
temp_time:='0'||thr||':'||tmin||' '||tempampm;
else
temp_time:=''||thr||':'||tmin||' '||tempampm;
end if;

select run_no into rn from mailmemory where ipaddress=ipad and pid=mpid and time=temp_time and d_date=:new.d_date;
if (rn mod 5=0 or rn=0) then
select mail_id into to_msg from mailid where ipaddress=ipad;
smail(msg_to=>to_msg,msg_subject => 'Hello from Oracle',msg_text => 'This is the body of the message'||:new.WS);
insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,:new.ti me,:new.ws,rn+1,'yes');
else
insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,:new.ti me,:new.ws,rn+1,'no');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN

insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,temp_ti me,:new.ws,rn+1,'no');
END;

.
run;
</code>

the code for the procedure smail is as follows:
<code>
CREATE OR REPLACE PROCEDURE smail (

msg_to varchar2,

msg_subject varchar2,

msg_text varchar2 )

IS

c utl_smtp.connection;

rc integer;

msg_from varchar2(50) := '[e-mail]';

mailhost VARCHAR2(30) := 'webmail.wipro.com'; -- local database host



BEGIN

c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25

utl_smtp.helo(c, mailhost);

utl_smtp.mail(c, msg_from);

utl_smtp.rcpt(c, msg_to);



utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||

'To: ' || msg_to || utl_tcp.crlf ||

'Subject: ' || msg_subject ||

utl_tcp.crlf || msg_text);

utl_smtp.quit(c);



EXCEPTION

WHEN UTL_SMTP.INVALID_OPERATION THEN

dbms_output.put_line(' Invalid Operation in Mail attempt

using UTL_SMTP.');

WHEN UTL_SMTP.TRANSIENT_ERROR THEN

dbms_output.put_line(' Temporary e-mail issue - try again');

WHEN UTL_SMTP.PERMANENT_ERROR THEN

dbms_output.put_line(' Permanent Error Encountered.');

END;

/
</code>
the trigger gets created.
but when i try to insert a record into "memorydetail" table i get the abov mentioned error.

[amitpatel66]

i am able to create the trigger successfully. but the problem is at times i don get the email. infact i get the following eror message:
<code>
ERROR at line 1:
ORA-29278: SMTP transient error: 454 5.7.3 Client does not have permission to
submit mail to this server.
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "NATHIYA.STATUS_CHECK", line 8
ORA-04088: error during execution of trigger 'NATHIYA.STATUS_CHECK'
</code>

this is my trigger:
<code>
CREATE OR REPLACE TRIGGER scheck
AFTER insert ON memorydetail

FOR EACH ROW
WHEN (to_number(new.WS)>50000)
DECLARE
ipad memorydetail.ipaddress%type:= :new.ipaddress;to_msg varchar2(50);mpid memorydetail.pid%type:=:new.pid;rn integer:=0;tempampm varchar2(3);temphr integer;thr integer;tempmin integer;tmin integer;temp_time varchar2(10);
BEGIN
tempampm:=trim(substr(:new.time,7,8));
temphr:=to_number(trim(substr(:new.time,1,2)));
tempmin:=to_number(trim(substr(substr(:new.time,4, 5),1,2)));
tmin:=tempmin-3;
thr:=temphr;
if (tmin<0) then
tmin:=60+tmin;
if (thr=12) then
tempampm:='AM';
end if;
thr:=temphr-1;
end if;
if (thr<10) then
temp_time:='0'||thr||':'||tmin||' '||tempampm;
else
temp_time:=''||thr||':'||tmin||' '||tempampm;
end if;

select run_no into rn from mailmemory where ipaddress=ipad and pid=mpid and time=temp_time and d_date=:new.d_date;
if (rn mod 5=0 or rn=0) then
select mail_id into to_msg from mailid where ipaddress=ipad;
smail(msg_to=>to_msg,msg_subject => 'Hello from Oracle',msg_text => 'This is the body of the message'||:new.WS);
insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,:new.ti me,:new.ws,rn+1,'yes');
else
insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,:new.ti me,:new.ws,rn+1,'no');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN

insert into mailmemory values(:new.ipaddress,:new.pid,:new.d_date,temp_ti me,:new.ws,rn+1,'no');
END;

.
run;
</code>

the code for the procedure smail is as follows:
<code>
CREATE OR REPLACE PROCEDURE smail (

msg_to varchar2,

msg_subject varchar2,

msg_text varchar2 )

IS

c utl_smtp.connection;

rc integer;

msg_from varchar2(50) := '[e-mail]';

mailhost VARCHAR2(30) := 'webmail.wipro.com'; -- local database host



BEGIN

c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25

utl_smtp.helo(c, mailhost);

utl_smtp.mail(c, msg_from);

utl_smtp.rcpt(c, msg_to);



utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||

'To: ' || msg_to || utl_tcp.crlf ||

'Subject: ' || msg_subject ||

utl_tcp.crlf || msg_text);

utl_smtp.quit(c);



EXCEPTION

WHEN UTL_SMTP.INVALID_OPERATION THEN

dbms_output.put_line(' Invalid Operation in Mail attempt

using UTL_SMTP.');

WHEN UTL_SMTP.TRANSIENT_ERROR THEN

dbms_output.put_line(' Temporary e-mail issue - try again');

WHEN UTL_SMTP.PERMANENT_ERROR THEN

dbms_output.put_line(' Permanent Error Encountered.');

END;

/
</code>
the trigger gets created.
but when i try to insert a record into "memorydetail" table i get the abov mentioned error.

From the Error message, there is some access right problem. The Client does not have rights to submit or send mail through the server that you are using for sending mail.