ORA-01008: not all variables bound 错误

今天在测试一个job时 出现下面错误

Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as dba_test
 
SQL>
SQL> begin
  2    sys.dbms_job.submit(job => :job,
  3                        what => 'insert into tb_client_concurrent_online(prefix,event_time,event_date,online_num,created_date,last_modified_date,status)
  4         select nvl(c.role_prefix,''other''),to_char(sysdate,''HH24''),sysdate,count(a.id),sysdate,sysdate,1
  5         from tb_test_status a, tb_test b, tb_test_detail c
  6         where a.client_id = b.id and b.role_id = c.role_id(+)
  7         and online_ = ''T''
  8         group by c.role_prefix;',
  9                        next_date => to_date('14-08-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
 10                        interval => 'trunc(SYSDATE+1/24,''HH'')');
 11    commit;
 12  end;
 13  /
 
begin
  sys.dbms_job.submit(job => :job,
                      what => 'insert into tb_client_concurrent_online(prefix,event_time,event_date,online_num,created_date,last_modified_date,status)
       select nvl(c.role_prefix,''other''),to_char(sysdate,''HH24''),sysdate,count(a.id),sysdate,sysdate,1
       from tb_test_status a, tb_test b, tb_test_detail c
       where a.client_id = b.id and b.role_id = c.role_id(+)
       and online_ = ''T''
       group by c.role_prefix;',
                      next_date => to_date('14-08-2008 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(SYSDATE+1/24,''HH'')');
  commit;
end;
 
ORA-01008: not all variables bound

没定义变量

 一看是没定义  sys.dbms_job.submit(job => :job,  job这个变量

SQL> var job number      --定义变量
SQL> begin
  2    sys.dbms_job.submit(job => :job,
  3                        what => 'insert into tb_client_concurrent_online(prefix,event_time,event_date,online_num,created_date,last_modified_date,status)
  4         select nvl(c.role_prefix,''other''),to_char(sysdate,''HH24''),sysdate,count(a.id),sysdate,sysdate,1
  5         from tb_test_status a, tb_test b, tb_test_detail c
  6         where a.client_id = b.id and b.role_id = c.role_id(+)
  7         and online_ = ''T''
  8         group by c.role_prefix;',
  9                        next_date => to_date('14-08-2008 11:00:00', 'dd-mm-yyyy hh24:mi:ss'),
 10                        interval => 'trunc(SYSDATE+1/24,''HH'')');
 11    commit;
 12  end;
 13  /
 
PL/SQL procedure successfully completed
job
---------
84

成功

查看job下次运行时间

select job,what,interval,to_char(next_date, 'yyyy-mm-dd hh24:mi:ss ') from user_jobs;

删除job

begin 
  FOR r_job IN (SELECT job FROM user_jobs where what like '%dbms_stats.gather_table_stats%' and interval='TRUNC(LAST_DAY(SYSDATE )+2)+6/24') LOOP
  dbms_job.REMOVE(r_job.job);
  end loop;
  commit;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-426879/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-426879/