create oracle job run procedure with parameters once

Question

I want to create oracle job in specific date and time that runs only on that date and time once and then dropped automatically.

The job should runs a procedure with 2 parameters also .

note: l_id I used this variable to add number beside procedure name to avoid any kind of duplication.

P94_DATE: the user will choose date and time and it will be stored in that variable.

here what I found so far but when I run this , it gives me 'statement proceed' but when I check if the job created successfully or not on 'sys.all_scheduler_jobs' it doesn't exist.

dbms_scheduler.create_job (
job_name           =>  'eba_sb_reminder_s'||l_id,
job_type           =>  'STORED_PROCEDURE',
 job_action         =>  'BEGIN send_Schedule_reminders(1,2); END;',
 start_date         =>  :P94_DATE, -- I need to assign time also !!
 enabled            =>  true,
comments           => 'check if there is new reminders needs to be send in specific date and time'


 );
end;

D. Mika · Accepted Answer · 2021-03-22T19:28:52.177

When job_type is set to STORE_PROCEDURE you must specify the name of the procedure in job_action.

The parameter start_date is of type DATE, which has a time as well in Oracle. You just need to set :p94_date with a correct value, containing a date and a time part.

If the procedure has parameters, you need to use DBMS_SCHEDULER.set_job_argument_value to specify a parameter-value.

Edit: Sample modified

Sample:

BEGIN
   -- This needs to be configured just once.
   DBMS_SCHEDULER.create_program(program_name          => 'test_program',
                                 program_type          => 'STORED_PROCEDURE',
                                 program_action        => 'test',
                                 number_of_arguments   => 2,
                                 enabled               => FALSE,
                                 comments              => 'Comment');

   DBMS_SCHEDULER.define_program_argument(program_name        => 'test_program',
                                          argument_name       => 'p1',
                                          argument_position   => 1,
                                          argument_type       => 'NUMBER',
                                          DEFAULT_VALUE       => NULL);

   DBMS_SCHEDULER.define_program_argument(program_name        => 'test_program',
                                          argument_name       => 'p2',
                                          argument_position   => 2,
                                          argument_type       => 'NUMBER',
                                          DEFAULT_VALUE       => NULL);

   DBMS_SCHEDULER.enable(name => 'test_program');

   -- Create job
   DBMS_SCHEDULER.create_job(
      job_name       => 'test_job',
      program_name   => 'test_program',
      start_date     => :p94_date,
      enabled        => FALSE,
      comments       => 'check if there is new reminders needs to be send in specific date and time');

   -- Set Procedure Parameter
   DBMS_SCHEDULER.set_job_argument_value(job_name => 'test_job', argument_position => 1, argument_value => 1);
   DBMS_SCHEDULER.set_job_argument_value(job_name => 'test_job', argument_position => 2, argument_value => 2);

   -- Enable job
   DBMS_SCHEDULER.enable(name => 'test_job');
END;

it shows error :ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION — BODYBOND, Mar 22 '21 at 19:11

score 0 · Answer 2 · answered Mar 22 '21 at 15:27

"It's not working" is not found an any error message reference, and is totally devoid of actionable information. That said:

You specify JOB_TYPE as STORED_PROCEDURE, so JOB_ACTION should be the name of a stored procedure. Instead you give it the code for an anonymous block.
JOB_NAME include a string concatenation that seems to try to include a variable. Where does that value come from and how do you think it is being populated when you execute this CREATE_JOB?

3)START_DATE also seems to try to include a variable/parameter. Again, Where does that value come from and how do you think it is being populated when you execute this CREATE_JOB?

dbms_scheduler.create_job (
job_name           =>  'eba_sb_reminder_s',
job_type           =>  'STORED_PROCEDURE',
 job_action         =>  'send_Schedule_reminders(1,2)',
 start_date         =>  to_date('2021-03-22 13:00:00','yyyy-mm-dd hh24:mi:ss'),
 enabled            =>  true,
comments           => 'check if there is new reminders needs to be send in specific date and time'

If this fails to meet your requirement (esp those attempts to include some sort of paramter, please explain in more detail what you are trying to achieve with them.

Thanks for your reply , I tried your code but this error show up ( ORA-27452: "send_Schedule_reminders(1,2)" is an invalid name for a database object. ) — BODYBOND, Mar 22 '21 at 16:15
I took that from your pl/sql block. So what is the name (and owner) of the procedure you want to execute? SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME)='SEND_SCHEDULE_REMINDERS'; Or use your original job_action and change job_type to 'PLSQL_BLOCK'. You should review the docs on dbms_scheduler.create_job, at https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_SCHEDULER.html#GUID-7E744D62-13F6-40E9-91F0-1569E6C38BBC, paying particular attention to job_type and job_action. — EdStevens, Mar 22 '21 at 19:47

create oracle job run procedure with parameters once

2 Answers2