oracle jobs 自動執行存儲過程的問題！謝謝！

Question

begin
  sys.dbms_job.submit(job => :job,
                      what => 'bmms_pk_statistics.bmmsp_report_com;',
                      next_date => to_date('24-06-2008', 'dd-mm-yyyy'),
                      interval => 'TRUNC(SYSDATE+1)');
  commit;
end;
/在user_jobs表中已經有了數據，也已經添加上去了！
我用它統計前一天的數據，插入到另外一張表中，每天執行一次！
但是它沒有執行,數據并沒有統計到令一張表中！
手動執行:
execute bmms_pk_statistics.bmmsp_report_com
這樣是可以的,可以看到另一張表中有插入數據！下面 job 是37的會執行！
40和41是同一個，就是上面哪個JOB,它沒有執行！    JOB NEXT_DATE   NEXT_SEC                   FAILURES BROKEN
---------- ----------- ------------------------ ---------- ------
        41 2008-6-24   00:00:00                          0 N
        40 2008-6-24   00:00:00                          0 N
        37 2008-6-24   00:00:00                          0 N
        48 2008-6-24   00:00:00                          0 N
請問高手：這是咋了？半個月啦！
謝謝您給我的幫助！

Answer 1

job不能执行其他用户的存储过程
会报错

Answer 2

 /*統計1天之前所有帳戶的發送統計信息，包括代理商，企業下所有用戶的發送信息*/
   procedure bmmsp_report_com
    as
      v_numcount number;
      v_numsuc number;
      m_numsuc number;
      v_numfail number;
      m_numfail number;
      v_numtotal number;
      m_numtotal number;
      i integer;
      v_vc2userid varchar2(20);
    begin
         insert into mms_report_com
           (vc2userid, datdate, numsuccesssum,numsuccessMoney, numfailsum,numfailMoney,
           NUMTOTAL, numtotalMoney, numlevel, numuserguid, numparentguid,numrole)
         select r.vc2userid, r.datdate, r.numsuccesssum,r.numsuccessmoney,r.numfailsum,r.numfailmoney,
                r.numallsum,r.NUMTOTALMONEY, u.NUMLEVEL,u.numuserguid,u.numparentguid,u.numrole
         from mms_report_send r,dgd_user u
         where r.vc2userid = u.vc2userid
         and trunc(r.datdate) = trunc(sysdate)-1;
         commit;
         
         for report in (select t.vc2userid,u.numrole,t.numuserguid,t.datdate from mms_report_com t, dgd_user u
                          where trunc(t.datdate) = trunc(sysdate) and t.numuserguid=u.numuserguid )
         loop         if report.numrole = 4 then --普通員工
            null;
         end if;
         if report.numrole = 3 then --
            for users in(select vc2userid,numuserguid  from dgd_user where numparentguid=report.numuserguid)
            loop               select count(*),
                      sum(numsuccesssum),
                      sum(numsuccessmoney),
                      sum(numfailsum),
                      sum(numfailmoney),
                      sum(numtotal),
                      sum(numtotalMoney)
               into v_numcount,v_numsuc,m_numsuc,v_numfail,m_numfail,v_numtotal,m_numtotal
               from mms_report_com
               where numuserguid=users.numuserguid --子帳號
               and trunc(datdate) = trunc(report.datdate);               if v_numcount>0 then
                 update mms_report_com set 
                 numsuccesssum=numsuccesssum+v_numsuc,
                 numsuccessmoney=numsuccessmoney+m_numsuc,
                 numfailsum=numfailsum+v_numfail,
                 numfailmoney=numfailmoney+m_numfail,
                 numtotal=numtotal+v_numtotal,
                 numtotalmoney=numtotalmoney+m_numtotal
                    where numuserguid=report.numuserguid--當前帳號
                  and trunc(datdate) = trunc(report.datdate);
                 commit;
               end if;
            end loop;
         end if;
         if report.numrole = 2 then --
            for users in(select vc2userid,numuserguid  from dgd_user where numparentguid=report.numuserguid)
            loop               select count(*),
                      sum(numsuccesssum),
                      sum(numsuccessmoney),
                      sum(numfailsum),
                      sum(numfailmoney),
                      sum(numtotal),
                      sum(numtotalMoney)
               into v_numcount,v_numsuc,m_numsuc,v_numfail,m_numfail,v_numtotal,m_numtotal
               from mms_report_com
               where numuserguid=users.numuserguid--子帳號
               and trunc(datdate) = trunc(report.datdate);               if v_numcount>0 then
                  update mms_report_com set 
                     numsuccesssum=numsuccesssum+v_numsuc,
                     numsuccessmoney=numsuccessmoney+m_numsuc,
                     numfailsum=numfailsum+v_numfail,
                     numfailmoney=numfailmoney+m_numfail,
                     numtotal=numtotal+v_numtotal,
                     numtotalmoney=numtotalmoney+m_numtotal
                         where numuserguid=report.numuserguid--當前帳號
                     and trunc(datdate) = trunc(report.datdate);
                 --yanghx 20080204
                 commit;
               end if;               for childuser in(select vc2userid,numuserguid  from dgd_user where numparentguid=users.numuserguid)
               loop
                   select count(*),
                      sum(numsuccesssum),
                      sum(numsuccessmoney),
                      sum(numfailsum),
                      sum(numfailmoney),
                      sum(numtotal),
                      sum(numtotalMoney)
                   into v_numcount,v_numsuc,m_numsuc,v_numfail,m_numfail,v_numtotal,m_numtotal
                   from mms_report_com
                       where numuserguid=childuser.numuserguid--子子帳號
                   and trunc(datdate) = trunc(report.datdate);                   if v_numcount>0 then
                     update mms_report_com set 
                     numsuccesssum=numsuccesssum+v_numsuc,
                     numsuccessmoney=numsuccessmoney+m_numsuc,
                     numfailsum=numfailsum+v_numfail,
                     numfailmoney=numfailmoney+m_numfail,
                     numtotal=numtotal+v_numtotal,
                     numtotalmoney=numtotalmoney+m_numtotal
                         where numuserguid=report.numuserguid--當前帳號
                     and trunc(datdate) = trunc(report.datdate);
                     --yanghx 20080204
                     commit;
                   end if;
               end loop;
            end loop;
         end if;
      end loop;
      
    end;
    存儲過程沒有問題，可以執行！

Answer 3

what => 'bmms_pk_statistics.bmmsp_report_com;', 
中间bmms_pk_statistics是用户名么
我以前也试过
用job跑其他user的存储过程
虽然有权限跑但是就是用job出错
建到自己用户下跑就没有问题了

Answer 4

    JOB NEXT_DATE  NEXT_SEC                  FAILURES BROKEN 
---------- ----------- ------------------------ ---------- ------ 
        41 2008-6-24  00:00:00                          0 N 
        40 2008-6-24  00:00:00                          0 N 
        37 2008-6-24  00:00:00                          0 N 
        48 2008-6-24  00:00:00                          0 N 這些都是同一個用戶，37會執行！40 沒有執行！
我手動執行的時候，也不會報錯！run job!