0

I want to create a scheduled job in oracle sql whose owner is user1 when logging in sys. the job is supposed to run a stored procedure which takes an argument. I was able to create the job myjob which belongs to the sys user, but not user1. I tried to create the job user1.myjob, but that did not work. How should I fix this?

begin
dbms_scheduler.create_program
(
program_name=>'myprog1',
program_action=>'user1.procedure1',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>1, enabled=>FALSE
) ;

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'myprog1',
argument_position=>1,
argument_type=>'VARCHAR2',
DEFAULT_VALUE=>'table1');


dbms_scheduler.enable('myprog1');
end;
/

-- create a job pointing to a program and set both argument values
set feedback on;
set serveroutput on;
begin
dbms_scheduler.create_job('myjob',program_name=>'myprog1');
dbms_scheduler.enable('myjob1');
end;
/
jiii
  • 71
  • 4

1 Answers1

0

According to the documentation:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363

Table 93-11 CREATE_JOB Procedure Parameters

Parameter Description job_name

This attribute specifies the name of the job and uniquely identifies the job. The name has to be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema. If the job being created will reside in another schema, it must be qualified with the schema name.

All you need to do is to prefix the job_name with the schema where you want it to be created.

It might be smarter to logon as the schema owner instead of using sys to create the job. The results of creating scheduler objects in other schema’s are not always what you expect. Besides that, working as sys is considered a risk and should be minimized.

  • Thanks for your reply. I saw the schema.job_name information in the documentation. I was trying to create a job with name user1.myjob using the code i had above, and i could not succeed. – jiii Jul 11 '22 at 20:19
  • Then you receiver error messages that can help solve this. Don’t forget that you need the program too. –  Jul 11 '22 at 20:23