3

I am trying to create a simple job using dbms_scheduler.create_job with arguments. Below is my simple procedure for creating a job:

create or replace procedure SCRIPT_DBMS_SCHEDULER
as
   v_text varchar2(255):='Parameter passed from Oracle to Linux';
   l_job_exists number;

Begin
   select count(*) into l_job_exists 
   from user_scheduler_jobs 
   where job_name = 'script_dbms_scheduler_test';

  if l_job_exists = 1 then
    dbms_scheduler.drop_job(job_name => 'script_dbms_scheduler_test');
  end if;

  dbms_output.put_line('I am in Procedure');
  dbms_scheduler.create_job 
  (job_name=>'script_dbms_scheduler_test',
  job_action=>'/data/home/workflow/script_dbms_scheduler.ksh',
  job_type=>'executable',
  number_of_arguments=>1,  
  enabled=>TRUE,
  auto_drop => TRUE,
  comments=> 'Run shell-script script_dbms_scheduler.ksh');
  dbms_scheduler.set_job_argument_value(job_name    =>'script_dbms_scheduler_test', argument_position => 1, argument_value => v_text);
  dbms_scheduler.enable('script_dbms_scheduler_test');
  dbms_output.put_line('I am back in Procedure');

Exception
   when others then
   dbms_output.put_line(sqlcode||sqlerrm);
end;

It works fine when when the arguments is 0. But when I try to pass at-least 1 argument, I get the following error:

ORA-27457: argument 1 of job "IITWFU.SCRIPT_DBMS_SCHEDULER_TEST" has no value

Not sure what I am missing. Any help is much appreciated.

Thanks

APC
  • 144,005
  • 19
  • 170
  • 281
Sand
  • 43
  • 1
  • 1
  • 3

1 Answers1

8

The problem is this line in your script:

enabled=>TRUE,

This means the job is enabled immediately upon creation. However, at that point you have not not set the argument. Hence the error.

So what you need to do is set enabled=>FALSE (this is actually the default for CREATE_JOB()). You already have the call to ENABLE() the job after your call to SET_ARGUMENT enable it, so your code should now work.

APC
  • 144,005
  • 19
  • 170
  • 281