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:
[code=oracle]
CREATE OR REPLACE TRIGGER scheck
AFTER insert
ON memorydetail
FOR EACH ROW

DECLARE
tomsg mailid.mail_id% type;
tomsg:='jitendr a.kumar4@wipro. com';
BEGIN
IF (to_number(new. WS)>50000) THEN
smail(tomsg,'He llo 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=oracle]
smail('jitendra .kumar4@wipro.c om', 'Hello from Oracle', 'This is the body of the message');
[/code]
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:='jitendr a.kumar4@wipro. com';
BEGIN
IF (to_number(new. WS)>50000) THEN
smail(tomsg,'He llo 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.c om', '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:
[code=oracle]
CREATE OR REPLACE TRIGGER scheck
AFTER insert
ON memorydetail
FOR EACH ROW

DECLARE
tomsg mailid.mail_id% type;
tomsg:='jitendr a.kumar4@wipro. com';
BEGIN
IF (to_number(new. WS)>50000) THEN
smail(tomsg,'He llo 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=oracle]
smail('jitendra .kumar4@wipro.c om', 'Hello from Oracle', 'This is the body of the message');
[/code]
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_applicati on_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_SM TP", line 17
ORA-06512: at "SYS.UTL_SM TP", line 96
ORA-06512: at "SYS.UTL_SM TP", 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.ip address%type:= :new.ipaddress; to_msg varchar2(50);mp id memorydetail.pi d%type:=:new.pi d;rn integer:=0;temp ampm varchar2(3);tem phr integer;thr integer;tempmin integer;tmin integer;temp_ti me varchar2(10);
BEGIN
tempampm:=trim( substr(:new.tim e,7,8));
temphr:=to_numb er(trim(substr( :new.time,1,2)) );
tempmin:=to_num ber(trim(substr (substr(:new.ti me,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_d ate;
if (rn mod 5=0 or rn=0) then
select mail_id into to_msg from mailid where ipaddress=ipad;
smail(msg_to=>t o_msg,msg_subje ct => 'Hello from Oracle',msg_tex t => 'This is the body of the message'||:new. WS);
insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,:n ew.time,:new.ws ,rn+1,'yes');
else
insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,:n ew.time,:new.ws ,rn+1,'no');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN

insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,te mp_time,: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.connec tion;

rc integer;

msg_from varchar2(50) := 'jitendra.kumar 4@wipro.com';

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



BEGIN

c := utl_smtp.open_c onnection(mailh ost, 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.INVALI D_OPERATION THEN

dbms_output.put _line(' Invalid Operation in Mail attempt

using UTL_SMTP.');

WHEN UTL_SMTP.TRANSI ENT_ERROR THEN

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

WHEN UTL_SMTP.PERMAN ENT_ERROR THEN

dbms_output.put _line(' Permanent Error Encountered.');

END;

/
</code>
the trigger gets created.
but when i try to insert a record into "memorydeta il" 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_SM TP", line 17
ORA-06512: at "SYS.UTL_SM TP", line 96
ORA-06512: at "SYS.UTL_SM TP", 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.ip address%type:= :new.ipaddress; to_msg varchar2(50);mp id memorydetail.pi d%type:=:new.pi d;rn integer:=0;temp ampm varchar2(3);tem phr integer;thr integer;tempmin integer;tmin integer;temp_ti me varchar2(10);
BEGIN
tempampm:=trim( substr(:new.tim e,7,8));
temphr:=to_numb er(trim(substr( :new.time,1,2)) );
tempmin:=to_num ber(trim(substr (substr(:new.ti me,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_d ate;
if (rn mod 5=0 or rn=0) then
select mail_id into to_msg from mailid where ipaddress=ipad;
smail(msg_to=>t o_msg,msg_subje ct => 'Hello from Oracle',msg_tex t => 'This is the body of the message'||:new. WS);
insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,:n ew.time,:new.ws ,rn+1,'yes');
else
insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,:n ew.time,:new.ws ,rn+1,'no');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN

insert into mailmemory values(:new.ipa ddress,:new.pid ,:new.d_date,te mp_time,: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.connec tion;

rc integer;

msg_from varchar2(50) := 'jitendra.kumar 4@wipro.com';

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



BEGIN

c := utl_smtp.open_c onnection(mailh ost, 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.INVALI D_OPERATION THEN

dbms_output.put _line(' Invalid Operation in Mail attempt

using UTL_SMTP.');

WHEN UTL_SMTP.TRANSI ENT_ERROR THEN

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

WHEN UTL_SMTP.PERMAN ENT_ERROR THEN

dbms_output.put _line(' Permanent Error Encountered.');

END;

/
</code>
the trigger gets created.
but when i try to insert a record into "memorydeta il" 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.