Email Alerts
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Email Alerts

  1. #1
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    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

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    If you use a fully qualified name, i.e., SCOTT.SEND_EMAIL? How, when do you get the error?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    try this...........
    Code:
    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

    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    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


  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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!');
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    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.

  8. #8
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    Forgot to say the procedure compile no errors

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    Thanks will do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width