Hello,
I'd like to change the example from oracle docs https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse002.htm#CHDCJFEJ
so that the procedure called accepts parameters. But I get an error:
CREATE OR REPLACE PROCEDURE prog1( p_job_id VARCHAR2 ) AS
BEGIN
DBMS_LOCK.sleep( 55 );
END;
DECLARE
newjob SYS.job;
newjobarr SYS.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array( );
-- Allocate sufficient space in the array
newjobarr.EXTEND( 5 );
-- Add definitions for 5 jobs
FOR i IN 1 .. 5 LOOP
-- Create a JOB object type
newjob :=
sys.job( job_name => 'TESTJOB'
|| TO_CHAR( i )
, job_style => 'REGULAR'
, job_template => 'PROG1(?)'
, repeat_interval => 'FREQ=HOURLY'
, start_date => SYSTIMESTAMP
+ INTERVAL '600' SECOND
, max_runs => 2
, auto_drop => FALSE
, enabled => TRUE
);
-- Add it to the array
newjobarr( i ) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
sys.DBMS_SCHEDULER.create_jobs( newjobarr
, 'TRANSACTIONAL'
);
FOR i IN 1 .. 5 LOOP sys.DBMS_SCHEDULER.set_job_argument_value( job_name => 'TESTJOB'
|| TO_CHAR( i )
, argument_position => 1
, argument_value => TO_CHAR( i )
);
END LOOP;
END;
/
Error at line 2
ORA-27452: PROG1(?) is an invalid name for a database objekt.
ORA-06512: in "SYS.DBMS_ISCHED", Zeile 6667
ORA-06512: in "SYS.DBMS_SCHEDULER", Zeile 3868
ORA-06512: in Zeile 32
Script Terminated on line 11.
how do declare the job, if the procedure has parameters?
bye
TPD