0

I'm not an expert about DBMS_SCHEDULER.CREATE_JOB and i've been asked to create a job. When i run it i get these errors:

ORA-06550: line2,column 2
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'

My statement is:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'ME.Delete_org',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'BEGIN delete_org_sre_package.process_org_sre_deletions; END;',
   start_date         =>  '23-NOV-17 11.00.00 PM',
   repeat_interval    =>  'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=0; BYSECOND=0;',
   auto_drop          =>   FALSE,
   end_date           =>   NULL,
   enabled            =>   TRUE,
   comments           =>  'Every Friday at 11pm');
END;
/

Any ideas of what i'm doing wrong? Thanks

0

You need to review each of the parameters in the docs, located at https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72300. Specfically, take a look at 'job_action' for your specified 'job_type' of "STORED_PROCEDURE".

"The action is the name of the stored procedure. " (emphasis mine). You are saying the action is a command -- "execute".

Also, does your procedure "delete_org_sre_package.process_org_sre_deletions" have any input or output parameters?

  • Hi @EdStevens, thanks, i'll keep your link in my useful links ;) In the end i found out the problem for my issue on Friday. Below my solution (very simple) – FaMontyN2 Nov 27 '17 at 10:30
0

The problem that was causing this issue was the start_date bit, i guess because of conflict with repeat_interval. Anyway, I ran it without start_date and it worked.

  • Conflict with repeat interval? More likely because START_DATE is supposed to accept a timestamp and you gave it a string. How does oracle know what part of the string is the year? Month? etc. It depends on the NLS_* setting which may not be what you think. set START_DATE => SYSTIMESTAMP. Be aware that START_DATE is not when the job actually starts, but when the schedule goes into effect. – EdStevens Nov 28 '17 at 22:55
  • Thanks @EdStevens, i'll keep it in my mind for the next time. – FaMontyN2 Nov 29 '17 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.