1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

problem scheduling a stored procedure

Discussion in 'Oracle Apps Technical' started by vamsioracle, May 26, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi all

    I am unable to schedule a procedure. I don't find any error in my code.

    Code (SQL):

    dbms_job.submit (
        job =>jobid ,
        what =>'cbtt_reminder_mail_proc(full_from_name,l_forward_to,begindate,enddate);',
        next_date => begin_date
        );
     
    The above code i am calling in a procedure.The code for cbtt_reminder_mail_proc() is posted below.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE cbtt_reminder_mail_proc (
       fromname     IN   VARCHAR2,
       sendto       IN   VARCHAR2,
       begin_date   IN   DATE,
       end_date     IN   DATE
    )
    IS
       full_to_name   VARCHAR2 (100);
       to_mail        VARCHAR2 (100);
                     --- mail id of the employee to whom delegation has been done
       empid_to       NUMBER;
       mesg           VARCHAR2 (4000);
       conn           UTL_SMTP.connection;
       cbtt_mail      VARCHAR2 (50)       := 'CBTT MAIL ALERT';
       subject        VARCHAR2 (100)  := 'REMINDER: Delegation of Responsibility';
       jobid          NUMBER              := 0;
    BEGIN
       SELECT employee_id, email_address
         INTO empid_to, to_mail
         FROM fnd_user
        WHERE user_name = sendto;

       SELECT full_name
         INTO full_to_name
         FROM per_all_people_f
        WHERE person_id = empid_to;

       mesg :=
             fromname
          || ' ,  has delegated the responsibility for approvals to '
          || full_to_name
          || ' from '
          || begin_date
          || ' to '
          || end_date
          || '    Please process all the leave applications before 48 hours                                                              



                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                '

          || 'THIS IS AN AUTO GENERATED MAIL, PLEASE DONOT  REPLY BACK.';
       conn := UTL_SMTP.open_connection ('CBMS1', 25);
       UTL_SMTP.helo (conn, 'CBMS1');
       UTL_SMTP.mail (conn, to_mail);
       UTL_SMTP.rcpt (conn, to_mail);
    /*utl_smtp.data( conn, mesg );*/
       UTL_SMTP.open_data (conn);
       UTL_SMTP.write_data (conn,
                               'Date: '
                            || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                            || CHR (13)
                           );
       UTL_SMTP.write_data (conn, 'From: ' || cbtt_mail || CHR (13));
       UTL_SMTP.write_data (conn, 'Subject: ' || subject || CHR (13));
       UTL_SMTP.write_data (conn, 'To: ' || to_mail || CHR (13));
       UTL_SMTP.write_data (conn, '' || CHR (13));
       UTL_SMTP.write_data (conn, mesg);
       UTL_SMTP.close_data (conn);
       UTL_SMTP.quit (conn);
    END cbtt_reminder_mail_proc;
     

    Is there any error inthe way i am caling the procedure or in any other part of the code.


    vamsi
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Hi vamsi, you are calling the dbms_job.submit within Begin-end blocks right? And is it giving you any error?

    Secondly check the view DBA_JOBS to see whether or not your Job got submitted or not.
     
  3. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    YEs i am calling it within the begin and end and there are no errors.

    There is no job in dba_jobs.


    vamsi
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    HI vamsi,

    There are problems with your DBMS_JOB code. Try this
    Code (SQL):

    DECLARE
       jobid   NUMBER;
    BEGIN
       DBMS_JOB.submit
          (job            => jobid,
           what           => 'cbtt_reminder_mail_proc(full_from_name,l_forward_to,begindate,enddate);',
           next_date      => TO_DATE ('05/27/2009 17:00:00',
                                      'mm/dd/yyyy hh24:mi:ss'
                                     ),
           INTERVAL       => 'SYSDATE+30/1440 ',
           no_parse       => TRUE
          );
       SYS.DBMS_OUTPUT.put_line ('Job Number is: ' || TO_CHAR (x));
    END;
    /
    The above code will submit the Job at 5 PM today and subsequently every half an hour. Replace the values with your own. Remember if you use a variable for next_date or Interval they must be declared first.
     
  5. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi i just noticed... in the call to cbtt_reminder_mail_proc(full_from_name,l_forward_to,begindate,enddate);

    How will the cbtt_reminder_mail_proc get the values for the arguments full_from_name,l_forward_to,begindate,enddate inside the dbms_job code?

    What you should do is this

    1. Write a procedure in which you get the values for the arguments for cbtt_reminder_mail_proc, something like this:
    Code (SQL):

    CREATE OR REPLACE PROCEDURE test_procedure
    AS
       full_from_name   VARCHAR2 (50);
       l_forward_to     VARCHAR2 (50);
       begindate        DATE;
       enddate          DATE;
    BEGIN
       /*
       SELECT STATEMENTS TO GET THE VALUES for thw arguments
        full_from_name,
        l_forward_to,
        begindate,
        enddate
        */

       cbtt_reminder_mail_proc (full_from_name, l_forward_to, begindate, enddate);
    END;

    2. Now submit the DBMS_JOB as my previous post but the procedure should be test_procedure, something like this
    Code (SQL):

    DECLARE
       jobid   NUMBER;
    BEGIN
       DBMS_JOB.submit
          (job            => jobid,
           what           => 'test_procedure;',
           next_date      => TO_DATE ('05/27/2009 17:00:00',
                                      'mm/dd/yyyy hh24:mi:ss'
                                     ),
           INTERVAL       => 'SYSDATE+30/1440 ',
           no_parse       => TRUE
          );
       SYS.DBMS_OUTPUT.put_line ('Job Number is: ' || TO_CHAR (x));
    END;
    /
    If you want to start the execution from a begin_date that you can't determine beforehand, find it here in the above code block in the Begin block before the DBMS_JOB call.