Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help to create DBMS scheduler job owned by a schema user when logging in as sys

User_MN2SUJul 11 2022

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;
/

Comments
Post Details
Added on Jul 11 2022
1 comment
1,198 views