建立了job,运行又出问题了,真是一波三折啊。

Question

SQL> declare
  2   job_city_day2t_city_orders number;
  3  begin
  4    sys.dbms_job.submit(
  5    job =>job_city_day2t_city_orders,
  6    what=>'proc_city_date2t_city_orders;',
  7    next_date=>sysdate,
  8    interval =>'sysdate+1/1440'
  9  );
 10    commit;
 11  end;
 12  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> begin
  2    dbms_job.run(:job_city_day2t_city_orders);
  3  end;
  4  /
 
begin
  dbms_job.run(:job_city_day2t_city_orders);
end;
 
ORA-01008: 并非所有变量都已绑定 

Answer 1

你单独运行存储过程可以运行嘛，会报错嘛？
另外看了下你的存储过程，其实不需要用游标的，因为
SELECT max(oper_date) FROM city_day; 
和select count(cityid) from t_city_orders
你没有分组，就始终只会出来一条纪录
直接用
SELECT max(oper_date)  l_maxOper_date FROM city_day; 
select count(cityid) into l_maxId from t_city_orders;
这样直接给两个变量赋值

Answer 2

SQL> variable
  2   job_city_day2t_city_orders number; 
  3  begin 
  4    sys.dbms_job.submit( 
  5    job =>job_city_day2t_city_orders, 
  6    what=>'proc_city_date2t_city_orders;', 
  7    next_date=>sysdate, 
  8    interval =>'sysdate+1/1440' 
  9  ); 
 10    commit; 
 11  end; 
 12  / ---------declare 改为variable就OK了

Answer 3

呵呵，你这是job建立的时候出问题了撒，job都还没建成功哦

Answer 4

PL/SQL procedure successfully completed 
这不是说明建成功了吗？

Answer 5

忘记写into了
应该是SELECT max(oper_date) 
     into
 l_maxOper_date FROM city_day;

Answer 6

next_date=>sysdate, 
这句不对，一定要固定日期
next_date=>to_date('2008-05-15 10:00:00','yyyy-mm-dd hh24:mi:ss')

Answer 7

照你的改法：
SQL> 
SQL> declare
  2   job_city_day2t_city_orders number;
  3  begin
  4    sys.dbms_job.submit(
  5    job =>job_city_day2t_city_orders,
  6    what=>'proc_city_date2t_city_orders;',
  7    next_date=>to_date('2008-05-15 10:00:00','yyyy-mm-dd hh24:mi:ss'),
  8    interval =>'sysdate+1/1440'
  9  );
 10    commit;
 11  end;
 12  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> begin
  2    dbms_job.run(:job_city_day2t_city_orders);
  3  end;
  4  /
 
begin
  dbms_job.run(:job_city_day2t_city_orders);
end;
 
ORA-01008: 并非所有变量都已绑定

Answer 8

把你的job改一下DECLARE
   job_city_day2t_city_orders   NUMBER;
BEGIN
   SYS.DBMS_JOB.submit (job            => job_city_day2t_city_orders,
                        what           => 'proc_city_date2t_city_orders;',
                        next_date      => TO_DATE ('2008-05-15 10:00:00',
                                                   'yyyy-mm-dd hh24:mi:ss'
                                                  ),
                        INTERVAL       => 'sysdate+1/1440',
                        no_parse       => FALSE
                       );
   COMMIT;
END;你复制好再去试试

Answer 9

DECLARE
   job_city_day2t_city_orders   NUMBER;
BEGIN
   SYS.DBMS_JOB.submit (job            => job_city_day2t_city_orders,
                        what           => 'proc_city_date2t_city_orders;',
                        next_date      => TO_DATE ('2008-05-15 11:00:00',
                                                   'yyyy-mm-dd hh24:mi:ss'
                                                  ),
                        INTERVAL       => 'sysdate+1/1440',
                        no_parse       => FALSE
                       );
   COMMIT;
END;

Answer 10

SQL> 
SQL> DECLARE
  2     job_city_day2t_city_orders   NUMBER;
  3  BEGIN
  4     SYS.DBMS_JOB.submit (job            => job_city_day2t_city_orders,
  5                          what           => 'proc_city_date2t_city_orders;',
  6                          next_date      => TO_DATE ('2008-05-15 10:00:00',
  7                                                     'yyyy-mm-dd hh24:mi:ss'
  8                                                    ),
  9                          INTERVAL       => 'sysdate+1/1440',
 10                          no_parse       => FALSE
 11                         );
 12     COMMIT;
 13  END;
 14  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> begin
  2    dbms_job.run(:job_city_day2t_city_orders);
  3  end;
  4  /
 
begin
  dbms_job.run(:job_city_day2t_city_orders);
end;
 
ORA-01008: 并非所有变量都已绑定
 
SQL> 
SQL> DECLARE
  2     job_city_day2t_city_orders   NUMBER;
  3  BEGIN
  4     SYS.DBMS_JOB.submit (job            => job_city_day2t_city_orders,
  5                          what           => 'proc_city_date2t_city_orders;',
  6                          next_date      => TO_DATE ('2008-05-15 11:00:00',
  7                                                     'yyyy-mm-dd hh24:mi:ss'
  8                                                    ),
  9                          INTERVAL       => 'sysdate+1/1440',
 10                          no_parse       => FALSE
 11                         );
 12     COMMIT;
 13  END;
 14  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> begin
  2    dbms_job.run(:job_city_day2t_city_orders);
  3  end;
  4  /
 
begin
  dbms_job.run(:job_city_day2t_city_orders);
end;
 
ORA-01008: 并非所有变量都已绑定还是不行．

Answer 11

dbms_job.run(:job_city_day2t_city_orders); 
你这个要直接输入job号试啊
查看你建立的JOB号
select job,next_date,next_sec,failures,broken from user_jobs假设你的job号是41
dbms_job.run(41); 

Answer 12

begin 
  dbms_job.run(:job_city_day2t_city_orders); 
end; 
你这样写还是要叫你输入job号，你不知道job号怎么运行啊

Answer 13

噢,输入job号是可以了.
不知道为什么指定job,在pl/sql中生成的job的sql,job没有名字,换成啦job号 
begin
  sys.dbms_job.change(job => 35,
                      what => 'proc_city_date2t_city_orders;',
                      next_date => to_date('16-05-2008 11:38:58', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/1440');
  commit;
end;
/

Answer 14

job 都是一个个号码，应该是有个系统的序列，你可以试下，同一个job脚本，多次运行,job号增加上去的

Answer 15

pl/sql中能不能直观的看出来job是否在工作?