Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: schedule a call to send an emais

    Hai
    DBMS_JOB to schedule a call to send an email right after the
    commit took place.I followed as .I got the the below error .Any idea about that

    create table emails (id number primary key, subj varchar2(30), emailto
    varchar2(30), body varchar2(4000) )

    SQL> create or replace procedure submit_email(p_sub in varchar2,p_emailto in var
    char2,p_body in varchar2)
    2 as
    3 v_job number;
    4 begin
    5 dbms_job.submit(v_job,'send_email(p_job);');
    6 insert into emails values(v_job,p_sub,p_emailto,p_body);
    7 end;
    8 /

    Procedure created.

    SQL> create procedure send_email( p_job in number )
    2 as
    3 l_rec emails%rowtype;
    4 begin
    5 select * into l_rec from emails where id = p_job;
    6 mail_pkg.send
    7 ( p_sender_email => 'mohanrao@isolainfoindia.com',
    8 p_from => 'Oracle Database Account <mohan@yahoo.com>',
    9 p_cc => mail_pkg.array( 'mohan_203@yahoo.com' ),
    10 p_bcc => mail_pkg.array( 'mohan_anp@yahoo.com' ),
    11 p_subject => 'urgent',
    12 p_body => 'Hello Tom,how are you i am fine here .how the thongs ar
    e going on this is the mail you need' );
    13 delete from emails where id = p_job;
    14 commit;
    15 end;
    16 /

    Procedure created.

    SQL> create or replace trigger mails after insert on emails
    2 begin submit_email;
    3 commit;
    4 end;
    5 /

    Warning: Trigger created with compilation errors.

    SQL> show err
    Errors for TRIGGER MAILS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    1/7 PLS-00306: wrong number or types of arguments in call to
    'SUBMIT_EMAIL'

    1/7 PL/SQL: Statement ignored
    SQL>

    Regards
    mohan

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

    Re: schedule a call to send an emais

    Originally posted by mohan
    ...I got the the below error .Any idea about that

    SQL> create or replace procedure submit_email(p_sub in varchar2,p_emailto in var
    char2,p_body in varchar2)
    ...
    SQL> create or replace trigger mails after insert on emails
    2 begin submit_email;
    3 commit;
    4 end;
    Your procedure has 3 mandatory parameters p_sub, p_emailto and p_body, but you tried to call it with no parameters.

Posting Permissions

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