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!

on job creation: how to pass a procedure that has parameters?

TPD-OpitzNov 23 2015 — edited Nov 23 2015

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

Comments

unknown-951199

TPD-Opitz wrote:

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:

  1. CREATE OR REPLACE PROCEDURE prog1( p_job_id VARCHAR2 ) AS 
  2. BEGIN 
  3.   DBMS_LOCK.sleep( 55 ); 
  4. END
  5.  
  6. DECLARE 
  7.   newjob     SYS.job; 
  8.   newjobarr  SYS.job_array; 
  9. BEGIN 
  10.   -- Create an array of JOB object types 
  11.   newjobarr  := sys.job_array( ); 
  12.  
  13.   -- Allocate sufficient space in the array 
  14.   newjobarr.EXTEND( 5 ); 
  15.  
  16.   -- Add definitions for 5 jobs 
  17.   FOR i IN 1 .. 5 LOOP 
  18.     -- Create a JOB object type 
  19.     newjob          := 
  20.       sys.job( job_name         =>   'TESTJOB' 
  21.                                   || TO_CHAR( i ) 
  22.              , job_style        => 'REGULAR' 
  23.              , job_template     => 'PROG1(?)' 
  24.              , repeat_interval  => 'FREQ=HOURLY' 
  25.              , start_date       =>  SYSTIMESTAMP 
  26.                                   + INTERVAL '600' SECOND 
  27.              , max_runs         => 2 
  28.              , auto_drop        => FALSE 
  29.              , enabled          => TRUE 
  30.               ); 
  31.  
  32.  
  33.     -- Add it to the array 
  34.     newjobarr( i )  := newjob; 
  35.   END LOOP; 
  36.   -- Call CREATE_JOBS to create jobs in one transaction 
  37.   sys.DBMS_SCHEDULER.create_jobs( newjobarr 
  38.                                 , 'TRANSACTIONAL' 
  39.                                  ); 
  40.  
  41.   FOR i IN 1 .. 5 LOOP    sys.DBMS_SCHEDULER.set_job_argument_value( job_name           =>   'TESTJOB' 
  42.                                                                     || TO_CHAR( i ) 
  43.                                              , argument_position  => 1 
  44.                                              , argument_value     => TO_CHAR( i ) 
  45.                                               ); 
  46.   END LOOP; 
  47.  
  48. 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

add more parameters to header & body

TPD-Opitz

proc1 is a plain procedure, no header and no (explicit) body...

proc1 has exactly one parameter, do you really think adding some more makes a difference?

I think the way I write it in the job constructor is wrong, but I can't figure out how...

bye

TPD

unknown-951199

TPD-Opitz wrote:

proc1 is a plain procedure, no header and no (explicit) body...

proc1 has exactly one parameter, do you really think adding some more makes a difference?

I think the way I write it in the job constructor is wrong, but I can't figure out how...

bye

TPD

YES

You are free to produce any code you deem appropriate.

You could produce different procedures, each that take there own collection of parameters.

TPD-Opitz

sol.beach wrote:

TPD-Opitz wrote:

proc1 is a plain procedure, no header and no (explicit) body...

proc1 has exactly one parameter, do you really think adding some more makes a difference?

I think the way I write it in the job constructor is wrong, but I can't figure out how...

bye

TPD

YES

You are free to produce any code you deem appropriate.

You could produce different procedures, each that take there own collection of parameters.

I don't understand your answer.

I have one parameter in my procedure declaration.

I have one parameter to that procedure in the job_template definition.

Where should I add more parameters and why?

bye

TPD

Chris Hunt

According to https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHGGCBJ job_template is "equivalent to the program_name argument of the CREATE_JOB procedure" (why they didn't call it program_name is anyone's guess). So set it to "PROG1", not "PROG1(?)".


You should also set the number_of_args value to 1

sys.job( job_name        =>  'TESTJOB'

                          || TO_CHAR( i )

       , job_style        => 'REGULAR'

        , job_template     => 'PROG1'

       , number_of_args   => 1

        , repeat_interval  => 'FREQ=HOURLY'

        , start_date       =>  SYSTIMESTAMP

                             + INTERVAL '600' SECOND

        , max_runs         => 2

        , auto_drop        => FALSE

        , enabled          => TRUE

         );

TPD-Opitz

Chris Hunt wrote:

According to https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHGGCBJ job_template is "equivalent to the program_name argument of the CREATE_JOB procedure" (why they didn't call it program_name is anyone's guess). So set it to "PROG1", not "PROG1(?)".


You should also set the number_of_args value to 1

sys.job( job_name        =>  'TESTJOB'

                          || TO_CHAR( i )

       , job_style        => 'REGULAR'

        , job_template     => 'PROG1'

       , number_of_args   => 1

        , repeat_interval  => 'FREQ=HOURLY'

        , start_date       =>  SYSTIMESTAMP

                             + INTERVAL '600' SECOND

        , max_runs         => 2

        , auto_drop        => FALSE

        , enabled          => TRUE

         );

is ther a predefines type for the jobarg-array which needs to be passed with the also needed arguments parameter?

bye

TPD

Chris Hunt

Ah, looking at it again, I see that job_template/program_name would hold the name of a program defined by DBMS_SCHEDULER.CREATE_PROGRAM.

If you just want to call a PL/SQL procedure directly, leave job_template NULL and set program_action='PROG1', action_type='STORED_PROCEDURE'.

Frankly, when I've scheduled procedure calls that include parameters, I've found it simpler to set the type to 'PLSQL_BLOCK' so that the action can simply be 'BEGIN my_proc(123); end;'.

TPD-Opitz

Chris Hunt wrote:

Ah, looking at it again, I see that job_template/program_name would hold the name of a program defined by DBMS_SCHEDULER.CREATE_PROGRAM.

If you just want to call a PL/SQL procedure directly, leave job_template NULL and set program_action='PROG1', action_type='STORED_PROCEDURE'.

Did that, but still have the arguments problem.

Chris Hunt wrote:

Frankly, when I've scheduled procedure calls that include parameters, I've found it simpler to set the type to 'PLSQL_BLOCK' so that the action can simply be 'BEGIN my_proc(123); end;'.

I'll consider this as a last resort, but binds are the state of the art and I would prefer them over string concatination...

Thanks so far...

TPD

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 21 2015
Added on Nov 23 2015
8 comments
1,751 views