Email+triggers+oracle

Originally Posted by 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:
<code>
CREATE OR REPLACE TRIGGER scheck
AFTER insert
ON memorydetail
FOR EACH ROW

DECLARE
tomsg mailid.mail_id%type;
tomsg:='jitendra.kumar4@wipro.com';
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('jitendra.kumar4@wipro.com', 'Hello from Oracle', 'This is the body of the message');
</code>
it works.
where am i going wong???
can anybody help?

Originally Posted by 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:

Originally Posted by 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) := 'jitendra.kumar4@wipro.com';

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.