DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: calling a package from a trigger?

  1. #1
    Join Date
    Apr 2002
    Posts
    29

    calling a package from a trigger?

    is this the correct way to call a procedure which is in a package from a trigger? thanks



    create or replace trigger ABC
    after insert on XYZ


    declare

    begin


    TEST.LOAD_TABLES; -- test being the package name
    -- and load_tables being the procedure name


    end ABC;

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2003
    Posts
    2
    on a related note:

    I have a table worequest, fed by a web form. I'd like to generate an email to the customer service center whenever a record is inserted on this table.

    I have a procedure send_email:

    CREATE OR REPLACE PROCEDURE
    send_email
    IS
    from_name varchar2(64);
    to_name varchar2(64) := 'support@foo.com';
    msg varchar2(1024);
    crlf varchar2(2) := chr(13)||chr(10); -- EOL CHARACTERS

    mail_connection utl_smtp.connection;
    BEGIN
    mail_connection := utl_smtp.open_connection('mail.foo.com',25);

    utl_smtp.helo(mail_connection,'foo.com');
    utl_smtp.mail(mail_connection,from_name);
    utl_smtp.rcpt(mail_connection,to_name);

    msg := 'Date: ' || TO_CHAR(sysdate, 'dd Mon yy hh24:mi:ss') || crlf
    ||
    'From: ' || from_name || crlf
    ||
    'To: ' || to_name || crlf
    ||
    '' || crlf
    ||
    'This is a test';

    utl_smtp.data(mail_connection, msg);

    utl_smtp.quit(mail_connection);
    END;
    /

    and a before insert trigger on the table:

    CREATE OR REPLACE TRIGGER hux_email
    before insert on worequest
    for each row
    declare from_name varchar2(64);
    begin
    from_name := :new.email;
    send_email(from_name);
    end;
    /

    but my trigger refuses to compile. I've tried a multitude of different declarations, none work. The Oracle Press book I have is fairly useless re: passing arguments from trigger to procedure - any ideas?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by huxtablejones
    CREATE OR REPLACE PROCEDURE
    send_email
    IS
    .....
    END;
    /

    and a before insert trigger on the table:

    CREATE OR REPLACE TRIGGER hux_email
    before insert on worequest
    for each row
    declare from_name varchar2(64);
    begin
    from_name := :new.email;
    send_email(from_name);
    end;
    /
    Your procedure SEND_MAIL doesn't accept any parameters, yet you call it with an IN parameter in your trigger. No wonder the trigger wouldn't compile.

    Change the declaration of your procedure to accept FROM_NAME as a parameter:

    CREATE OR REPLACE PROCEDURE
    send_email (from_name IN VARCHAR2)
    IS
    to_name varchar2(64) := 'support@foo.com';
    msg varchar2(1024);
    crlf varchar2(2) := chr(13)||chr(10); -- EOL CHARACTERS
    mail_connection utl_smtp.connection;
    BEGIN
    ....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2003
    Posts
    2
    *smacks forehead*

    thanks.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You might consider calling the send_email procedure through DBMS_JOB -- yo probably don't want the user to be waiting for the procedure to complete before returning, and DBMS_JOB would effectively make the process run in the background.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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