Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: send email from pl/sql

    hi,
    I'd like to send an automatic email from a procedure.
    My procedure is as:

    PROCEDURE proc_name
    IS



    cursor my_cur is
    select col
    from table
    where.........


    BEGIN
    for rec_cur in my_cur
    loop

    if condition then

    NOW I MUST SEND AN EMAIL IN AUTOMATIC TO RECIPIENT.
    end if;


    how can I send it????

    I know that exist a package UTL_SMTP!!!
    HOW CAN I IMPLEMENT THIS IN MY STORED PROCEDURE???

    please help me!
    thanks
    bye
    Raffaele

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Here's an example procedure that will send mail based on the recipient, message and subject that is passed. Simply call this procedure to send mail.
    Code:
    
    CREATE OR REPLACE
    PROCEDURE SENDMAIL
       ( recipient IN varchar2,
         message IN varchar2,
         subject IN varchar2 DEFAULT 'Oracle Status Message')
       IS
    
       c utl_smtp.connection;
    
       PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
       BEGIN
        utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
       END;
    
    
    BEGIN
        c := utl_smtp.open_connection('YOUR.MAIL.SERVER.COM');
        utl_smtp.helo(c, 'YOUR MACHINE NAME');
        utl_smtp.mail(c, 'YOUR INSTANCE NAME');
        utl_smtp.rcpt(c, recipient);
        utl_smtp.open_data(c);
        send_header('From',    '"DBA" <you@yourcompany.com>');
        send_header('To',      '"'||recipient||'"');
        send_header('Subject', subject);
        utl_smtp.write_data(c, utl_tcp.CRLF || message);
        utl_smtp.close_data(c);
        utl_smtp.quit(c);
      EXCEPTION
        WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
          utl_smtp.quit(c);
          raise_application_error(-20000,
            'Failed to send mail due to the following error: ' || sqlerrm);
    END;
    /
    

  3. #3
    Join Date
    Jul 2002
    Posts
    227

    email from pl/sql

    Hi,
    I tried to send email with stored procedure.
    It run correctly just I connect with system/manager.
    If I change user/password don't run my procedure and I see this error:

    ERROR at line 1:
    ORA-29540: class oracle/plsql/net/TCPConnection does not exist
    ORA-06512: at "SYS.UTL_TCP", line 533
    ORA-06512: at "SYS.UTL_TCP", line 199
    ORA-06512: at "SYS.UTL_SMTP", line 99
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at "AFM.SPEDISCI_MAIL", line 17
    ORA-06512: at line 1

    is normal???

    thanks!!!
    Raffaele

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    This is normal. Just grant EXECUTE on those packages to the user.

    Remember that the owner of the procedure from which you are calling the mail proc needs to have the execute permission on the mail proc, regardless of the actual logged in user.

  5. #5
    Join Date
    Jul 2002
    Location
    upstate ny
    Posts
    3

    Smile Another option

    If you don't want to use the Java option in your db my understanding is you can not use this feature. So ... I would propose the you use a External Procedure from Oracle instead. All you have to do is create a small C app that invokes mail or mailx and sends off the mail to the approproriate people. External Procedures work similarly to the Java procs. The advantage is that you do not have to use Java. There is info on this on technet.oracle.com but I have working examples if you would like to see.

    Frank

  6. #6
    Join Date
    Jul 2002
    Posts
    227

    send email from stored procedure

    hi,
    I tried but don't run
    connect system/manager (or internal/oracle)

    grant execute on name_package to user;
    ERROR at line 1:
    ORA-04042: procedure, function, package, or package body does not exist


    grant execute on name_procedure to user;
    ERROR at line 1:
    ORA-04042: procedure, function, package, or package body does not exist

    How can I do???
    thanks
    Raffaele

  7. #7
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    You must be logged on as the object owner to grant permissions to others, unless you have the WITH GRANT option on the object.

  8. #8
    Join Date
    Jul 2002
    Posts
    227

    send email from stored procedure

    Hi Frank,
    Could you tell me how can I implement this program to avoid use Java??

    Thanks
    Raffaele

  9. #9
    Join Date
    Jul 2002
    Location
    upstate ny
    Posts
    3
    Ok. Look at this doc I'm attaching. This has a pretty decent example of creating and using an external proc. Be warned there is a security warning out there for Oracle about using external procs. I have not seen alot of documentation that supports what the hack is. Despite this this feature works well. The way you might change this example to fit your needs is to put a system call in the C program that calls mailx or mail with the approrpriate parameters.

    Good Luck
    Frank P.
    Attached Files Attached Files

Posting Permissions

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