Email Alerts

[joyce_ank]

I used Julian's procedure which sends email to the mail host IP address:


(P_SENDER in varchar2,
P_RECIPIENT in varchar2,
P_SUBJECT in varchar2,
P_MESSAGE in varchar2)
IS

mailhost varchar2(30) := 'mailhost';
mail_conn utl_smtp.connection;
crlf varchar2(2):= CHR(13)||CHR(10);
mesg varchar2(4000);
BEGIN
mail_conn := utl_smtp.open_connection(mailhost,25);
mesg:= 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss' )||crlf
||'FROM: '||P_SENDER||' >'||crlf||'Subject: '||P_SUBJECT||crlf ||'To: '||P_RECIPIENT
||crlf||''|| crlf ||P_MESSAGE;
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,P_SENDER);
utl_smtp.rcpt(mail_conn,P_RECIPIENT);
utl_smtp.data(mail_conn,mesg);
utl_smtp.quit(mail_conn);
END SEND_EMAIL;

SEND_EMAIL is the name of the procedure.

I keep getting error PL-00201 - SEND_EMAI must be declared.

Any thoughts on this?

Thanks

[TimHall]

The error message says "SEND_EMAI". I assume the procedure was called "SEND_EMAIL". Is this a typo in your post or a type in your procedure call?

Cheers

[julian]

If you use a fully qualified name, i.e., SCOTT.SEND_EMAIL? How, when do you get the error?

[Tarry]

try this...........

CREATE OR REPLACE  PROCEDURE SCHEMA.SEND_EMAIL
(P_SENDER in varchar2, 
P_RECIPIENT in varchar2, 
P_SUBJECT in varchar2, 
P_MESSAGE in varchar2) 
IS 

mailhost varchar2(30) := 'mailhost'; 
mail_conn utl_smtp.connection; 
crlf varchar2(2):= CHR(13)||CHR(10); 
mesg varchar2(4000); 
BEGIN 
mail_conn := utl_smtp.open_connection(mailhost,25); 
mesg:= 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss' )||crlf 
||'FROM: '||P_SENDER||' >'||crlf||'Subject: '||P_SUBJECT||crlf ||'To: '||P_RECIPIENT 
||crlf||''|| crlf ||P_MESSAGE; 
utl_smtp.helo(mail_conn,mailhost); 
utl_smtp.mail(mail_conn,P_SENDER); 
utl_smtp.rcpt(mail_conn,P_RECIPIENT); 
utl_smtp.data(mail_conn,mesg); 
utl_smtp.quit(mail_conn); 
END SEND_EMAIL;

it'll work!!

cheers

Tarry

[joyce_ank]

Sorry I should've clarified this. Error I'm getting is when I execute the procedure

exec workflow3r.send_email(''.'', 'Test', 'Hello World!');
*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00905: object WORKFLOW3R.SEND_EMAIL is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[julian]

Is the procedure compiled? No errors?

This is the correct syntax to execute the procedure is:

exec workflow3r.send_email('scott@oracle.com',scott@oracle.com', 'Test', 'Hello World!');

[joyce_ank]

Sorry this is a typo. This is the code I'm executing

exec workflow3r.send_email('emailaddress', 'emailaddress','Test', 'Hello World');

and still getting the same error message. Do you think it could be due to an error in the jvm set up because when I run

loadjava -force .....

I get this error :

SQL error while connecting with oci8 drive to epdrop13
ORA-01017 invalid username/password.

loadjava 2 errors

Oracle 8.1.7(patches 8.1.7.1.1, 8.1.7.1.5)
Win2K
Oracle Failsafe 3.1.2

Sorry to bother you.

[joyce_ank]

Forgot to say the procedure compile no errors

[julian]

Originally posted by joyce_ank
I get this error :

SQL error while connecting with oci8 drive to epdrop13
ORA-01017 invalid username/password.

loadjava 2 errors

That is the reason. I suggest you reinstall the JVM.

Run $ORACLE_HOME/javavm/install/rmjvm.sql in order to remove everything and start installling again.

[joyce_ank]

Thanks will do.