Forum Stats

  • 3,783,341 Users
  • 2,254,760 Discussions
  • 7,880,368 Comments

Discussions

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

TPD-Opitz
TPD-Opitz Member Posts: 2,465 Silver Trophy
edited Nov 23, 2015 11:26AM in SQL & PL/SQL

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

Tagged:
TPD-Opitz

Answers

  • Unknown
    edited Nov 23, 2015 9:38AM
    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; 
    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
    

    add more parameters to header & body

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Nov 23, 2015 10:01AM

    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
    edited Nov 23, 2015 10:05AM
    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
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Nov 23, 2015 10:13AM
    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
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Nov 23, 2015 10:40AM

    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-OpitzTPD-Opitz
  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Nov 23, 2015 11:06AM
    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
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Nov 23, 2015 11:17AM

    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-OpitzTPD-Opitz
  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Nov 23, 2015 11:26AM
    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

This discussion has been closed.