在JOB调用存储过程,并且存储过程想加当前年日参数,请问这个JOB创建的脚本什么,下面是我创建的,最后还是报错
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 101
,what => 'YTEXP.EXP_REPORT_TRANSFER23
(to_char(sysdate-1,'YYYY-mm-dd'') ,to_char(sysdate,'YYYY-mm-dd'));' --这里报错
,next_date => to_date('24-06-2011 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
);
END;
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 101
,what => 'YTEXP.EXP_REPORT_TRANSFER23
(to_char(sysdate-1,'YYYY-mm-dd'') ,to_char(sysdate,'YYYY-mm-dd'));' --这里报错
,next_date => to_date('24-06-2011 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
);
END;
SYS.DBMS_JOB.CHANGE
(
job => 101
,what => 'YTEXP.EXP_REPORT_TRANSFER23
(to_char(sysdate-1,'YYYY-mm-dd'') ,to_char(sysdate,'YYYY-mm-dd'));' --这里报错
,next_date => to_date('24-06-2011 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
);
END;
declare
str_l_date_1 varchar2(10);
str_l_date_2 varchar2(10);
beegin
str_l_date_1 := to_char(sysdate-1,'YYYY-mm-dd');
str_l_date_2 := to_char(sysdate,'YYYY-mm-dd');
YTEXP.EXP_REPORT_TRANSFER23(str_l_date_1, str_l_date_2);
end;
SYS.DBMS_JOB.CHANGE
(
job => 101
,what => 'YTEXP.EXP_REPORT_TRANSFER23
(to_char(sysdate-1,'YYYY-mm-dd') ,to_char(sysdate,'YYYY-mm-dd'));' --这里报错,
,next_date => to_date('24-06-2011 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
);
END;去掉多余引号也报错
SQL> create table aspen (id date); SQL> create or replace procedure insert_aspen_pro(v_id date)
2 as
3 begin
4 insert into aspen values(v_id);
5 end;
6 /begin
sys.dbms_job.submit(job=>:test_job,
what=>'declare v_id date:=sysdate; begin insert_aspen_pro(v_id); end;',
next_date=>to_date('20110623 13:41:00','yyyymmdd hh24:mi:ss'),
interval=>'sysdate+1/1440');
commit;
end;
--需要两个单引号来表示一个单引号,如:SQL> select 'what''s this?' str from dual;
STR
------------
what's this?
SQL>
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 101
,what => 'YTEXP.EXP_REPORT_TRANSFER23(to_char(sysdate-1,''YYYY-mm-dd'') ,to_char(sysdate,''YYYY-mm-dd''));'
,next_date => to_date('24-06-2011 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
);
END;