run DBMS_SCHEDULER.create_job from sys for a stored procedure owned by another schema [Oracle SQL]

Question

I want to schedule runs to execute a stored procedure which belonged to a schema schemaA when logging in as SYS user.

The stored procedure procedureA takes in an input parameter varA. I was having some trouble running the scheduler.

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'test1',
        job_type          => 'STORED_PROCEDURE',
        job_action        => 'schemaA.procedureA(''varA''); ',
        start_date        => SYSTIMESTAMP,
        repeat_interval   => 'FREQ=minutely;BYMINUTE=0,10,20,30,40,50;BYSECOND=0',
        enabled           => TRUE,
        comments          => 'Your description of your job'
    );

END;

I got error:

Error report -
ORA-27452: "schemaA.procedureA('varA'); " is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 3
27452. 00000 -  "\"%s\" is an invalid name for a database object."
*Cause:    An invalid name was used to identify a database object.
*Action:   Reissue the command using a valid name.

Wondering how should I fix this

Answer 1

You can't specify inputs in the ACTION parameter, only the name of the procedure. Use the NUMBER_OF_ARGUMENTS and ARGUMENTS parameters to specify the inputs.

• See the documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-1BC57390-C756-4908-A4D8-8D1EEC236E25

• See an example here: Creating a dbms_scheduler.create_job with arguments

  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,  
    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');