今天,一同事创建job作业时遇到一个问题,在使用dbms_scheduler.create_job创建job作业遭遇PLS-00306报错。
具体的报错信息如下:
dbms_scheduler.create_job
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
PLS-00306报错相关分析处理过程如下:
一、job作业创建语句
begin
dbms_scheduler.create_job(
job_name =>'DSG_CHECKER',
job_type =>'PLSQL_BLOCK',
job_action =>'declare
begin
update SCOTT.dsgchecker set time=SYSDATE;
commit;
end;',
start_date =>SYSDATE,
repeate_interval => 'FREQ=MINUTELY;INTERVAL = 5');
end;
/
二、排除数据库版本问题
据同事说,这个job作业在其他版本数据库上能执行,在19c执行的时候碰到这个错误,我亲自验证后发现无论哪个版本,
相同的job作业创建语句报错都是相同的。
Oracle 19.7.0.0上的执行,有PLS-00306报错提示:
[oracle@oratop]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 9 11:05:33 2021
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SYS@orcl1>alter session set container=PDBORCL;
Session altered.
SYS@orcl1>begin
dbms_scheduler.create_job(
job_name =>'DSG_CHECKER',
job_type =>'PLSQL_BLOCK',
job_action =>'declare
begin
update scott.dsgchecker set time=SYSDATE;
commit;
end;',
start_date =>SYSDATE,
repeate_inter 2 3 4 5 6 7 8 9 10 11 val => 'FREQ=MINUTELY;INTERVAL = 5');
end;
/ 12 13
dbms_scheduler.create_job(
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
SYS@orcl1>
Oracle 11.2.0.4上的执行,有PLS-00306报错提示:
[oracle@orcl11gpri ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 24 06:18:17 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> begin
dbms_scheduler.create_job(
job_name =>'DSG_CHECKER',
job_type =>'PLSQL_BLOCK',
job_action =>'declare
begin
update scott.dsgchecker set time=SYSDATE;
commit;
end;',
start_date =>SYSDATE,
repeate_interval => 'FREQ=MINUTELY;INTERVAL = 5');
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13
dbms_scheduler.create_job(
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
SQL>
三、问题解决
看job作业的创建语句,不太好看出来到底哪里出现问题,由于自己做过开发,看他job作业目的是想
创建个job定期执行一个update语句,那就通过开发工具plsql工具操作吧。
sys或者具有dba权限的用户登录数据库,找到jobs选中右键选择新建(New)
然后,按照创建语句填写job相关的必要参数:Name ,Type,Action,Start date,Frequency,Interval,Job Class。
点击Apply按钮提示报错信息如下:
到此为止,该job创建语句执行触发PLS-00306的一个原因已经找到,就是start_date需要一个具体的日期。点击start date最
右侧的下三角选取时间。
然后,点击View SQL按钮查看job作业创建语句,一定要在Apply前点击,否则看不到创建SQL语句:
begin
sys.dbms_scheduler.create_job(job_name => 'SYS.DSG_CHECKER',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
begin
update scott.dsgchecker set time=SYSDATE;
commit;
end;',
start_date => to_date('09-07-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Minutely;Interval=5',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => '');
end;
/
然后,点击Apply按钮,job作业成功创建:
如果有需要,可以删除掉相关job作业,根据创建语句进行调整重建即可。
总结:job作业创建,相关参数有具体的数据类型要求,一定要符合相关要求才行,本次是start date参数非法导致PLS00306.
这里奉上job作业创建的官方在线指导文档:https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235