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

[suncheng_hong]

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: 并非所有变量都已绑定

[hxmsammi]

你这是想做什么啊

[hebo2005]

你单独运行存储过程可以运行嘛，会报错嘛？
另外看了下你的存储过程，其实不需要用游标的，因为
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;
这样直接给两个变量赋值

[suncheng_hong]

噢,输入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;
/

[hebo2005]

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

[oracle_dba_11]

学习ing

[suncheng_hong]

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

[liuhc10]

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了

[liuhc10]

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

[suncheng_hong]

[Quote=引用 2 楼 hebo2005 的回复:]
你单独运行存储过程可以运行嘛，会报错嘛？
另外看了下你的存储过程，其实不需要用游标的，因为
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;
这样直接给两个变量赋值
[/Quote]

单独运行存储过程是没有问题的.
你的这种赋值方法不错，受教拉。

[suncheng_hong]

[Quote=引用 4 楼 liuhc10 的回复:]
呵呵，你这是job建立的时候出问题了撒，job都还没建成功哦
[/Quote]
PL/SQL procedure successfully completed
这不是说明建成功了吗？

[hebo2005]

[Quote=引用 5 楼 suncheng_hong 的回复:]
引用 2 楼 hebo2005 的回复:
你单独运行存储过程可以运行嘛，会报错嘛？
另外看了下你的存储过程，其实不需要用游标的，因为
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;
这样直接给两个变量赋值
…
[/Quote]
忘记写into了
应该是SELECT max(oper_date)
into
l_maxOper_date FROM city_day;

[hebo2005]

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

[suncheng_hong]

[Quote=引用 8 楼 hebo2005 的回复:]
next_date=>sysdate,
这句不对，一定要固定日期
next_date=>to_date('2008-05-15 10:00:00','yyyy-mm-dd hh24:mi:ss')
[/Quote]

照你的改法：
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: 并非所有变量都已绑定

[hebo2005]

把你的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;

你复制好再去试试

[hebo2005]

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;

[suncheng_hong]

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: 并非所有变量都已绑定

还是不行．

[hebo2005]

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);

[hebo2005]

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