Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Unanswered: Triggers with Email

    Hi,
    I have a situation in one of the DB tables.
    I want to track, if while inserting a record in Table A, if a field is not entered correctly (null, blank or of length less than 5), I want to send out a email to myself, about the possible missing information.

    I would appreciate if someone with good at Oracle stuff, could respond to the same.
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Triggers with Email

    Originally posted by aruneeshsalhotr
    Hi,
    I have a situation in one of the DB tables.
    I want to track, if while inserting a record in Table A, if a field is not entered correctly (null, blank or of length less than 5), I want to send out a email to myself, about the possible missing information.

    I would appreciate if someone with good at Oracle stuff, could respond to the same.
    Thanx and Regards
    Aruneesh
    Here is the simplest possible Oracle code for sending an email:

    DECLARE
    l_mailhost VARCHAR2(255) := 'myhost.com';
    l_mail_conn utl_smtp.connection;
    BEGIN
    l_mail_conn := utl_smtp.open_connection( l_mailhost );
    utl_smtp.helo(l_mail_conn, l_mailhost);
    utl_smtp.mail(l_mail_conn, 'me@myhost.com');
    utl_smtp.rcpt(l_mail_conn, 'you@yourhost.com');
    utl_smtp.open_data(l_mail_conn);
    utl_smtp.write_data(l_mail_conn,'Hello there');
    utl_smtp.close_data(l_mail_conn);
    utl_smtp.quit(l_mail_conn);
    END;
    /

    One thing to note is that sending email is quite slow, so if there is heavy inserting on table A this could be a performance problem. This can be overcome by calling DBMS_JOB.SUBMIT to send the email asynchronously. Alternatively, you could use DBMS_JOB to periodically run an exception report, rather than send an individual email per insert.

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314
    Hi Andrewst,
    I am getting the following error while execuiting the query.
    ERROR at line 1:
    ORA-29540: class oracle/plsql/net/TCPConnection does not exist
    ORA-06512: at "SYS.UTL_TCP", line 537
    ORA-06512: at "SYS.UTL_TCP", line 199
    ORA-06512: at "SYS.UTL_SMTP", line 102
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at line 5

    The following seems to be the path of the oracle/plsql thing that the error refers to.
    /u01/app/oracle/product/8.1.7/plsql

    I dont see a net directory in there. I there any place i could get the class files for the same, or I am not correct my assumption of the path.

    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by aruneeshsalhotr
    Hi Andrewst,
    I am getting the following error while execuiting the query.
    ERROR at line 1:
    ORA-29540: class oracle/plsql/net/TCPConnection does not exist
    ORA-06512: at "SYS.UTL_TCP", line 537
    ORA-06512: at "SYS.UTL_TCP", line 199
    ORA-06512: at "SYS.UTL_SMTP", line 102
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at line 5

    The following seems to be the path of the oracle/plsql thing that the error refers to.
    /u01/app/oracle/product/8.1.7/plsql

    I dont see a net directory in there. I there any place i could get the class files for the same, or I am not correct my assumption of the path.

    Thanx and Regards
    Aruneesh
    Sorry, can't help with that; maybe someone else here can. Have you tried contacting your DBA for assistance?

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    DBA : N/A

    I cant rely on the DBA, I mean .. DBA is just temp and is not around often

Posting Permissions

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