DBMS_SCHEDULER [message #442481] |
Mon, 08 February 2010 07:43 |
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 |
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 |
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"?
|
|
|