Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SCHEDULER (10g)
DBMS_SCHEDULER [message #442481] Mon, 08 February 2010 07:43 Go to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
Hi,

I am trying to execute a job using dbms_scheduler but its not working as expecting. I need to create a job which accepts a paramater and then call that job passing a variable paramter.

Here is what I have done:

begin  
  
  -- Stored Procedure with Arguments. 
  DBMS_SCHEDULER.create_program ( 
    program_name        => 'TEST_PROG1', 
    program_type        => 'STORED_PROCEDURE', 
    program_action      => 'NLPRC_SALESORDER_PKG.enrich_sales_order', 
    number_of_arguments => 1, 
    enabled             => FALSE, 
    comments            => 'TEST PROGRAM'); 
    
  DBMS_SCHEDULER.define_program_argument ( 
    program_name      => 'TEST_PROG1', 
    argument_name     => 'p_order_id', 
    argument_position => 1, 
       default_value => 'p_order_id',
    argument_type     => 'VARCHAR2'); 

  DBMS_SCHEDULER.enable (name => 'TEST_PROG1'); 
END; 

begin
  DBMS_SCHEDULER.create_job ( 
    job_name      => 'ENRICH_THE_ORDER1', 
    program_name  => 'TEST_PROG1', 
    enabled       => TRUE, 
    comments      => 'Job defined by an existing program and
schedule.');

end;    


begin
  DBMS_SCHEDULER.run_job('ENRICH_THE_ORDER1(1)'); 
end;  



Details:
begin
  DBMS_SCHEDULER.run_job('ENRICH_THE_ORDER1(1)'); 
end;

Error at line 32
ORA-20001: comma-separated list invalid near R1(1)
ORA-06512: at "SYS.DBMS_UTILITY", line 238
ORA-06512: at "SYS.DBMS_UTILITY", line 271
ORA-06512: at "SYS.DBMS_SCHEDULER", line 446
ORA-06512: at line 2


Re: DBMS_SCHEDULER [message #442489 is a reply to message #442481] Mon, 08 February 2010 08:21 Go to previous messageGo to next message
TinchoGomez
Messages: 11
Registered: January 2010
Location: Santa Fe - Argentina
Junior Member

Hi, I can not posible run a job with input parameter. Try refactory the store procedure.
Please confirm the run store procedure sucefully.

Excuseme my bad inglish.

Hola Según entiendo no es posible ejecutar un job con parametros de entrada. deberías rediseñar la lógica del Sp para que lea los parametros desde otro lugar.
Chequea que el la ejecución del Sp sea satisfactoria.

Saludos
TinchoGomez

[Updated on: Mon, 08 February 2010 08:26]

Report message to a moderator

Re: DBMS_SCHEDULER [message #442633 is a reply to message #442489] Tue, 09 February 2010 10:35 Go to previous message
gjcjan
Messages: 5
Registered: February 2010
Location: Canada
Junior Member
Will you be able to substitute the parameters by having queries inside your procedure to determine the correct values?

Can you store the parameter values into a table before the job gets run. And the procedure query that "parameter table"?
Previous Topic: Enclosing comma separated values within single quotes (4 merged)
Next Topic: Need help writing elements of an array to a single row (merged 3)
Goto Forum:
  


Current Time: Thu Nov 07 16:47:28 CST 2024