-
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;
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
-
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?
-
*smacks forehead*
thanks.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|