Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler - procedure with parameters
dbms_scheduler - procedure with parameters [message #268934] Thu, 20 September 2007 02:36 Go to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Hi all,

I have a problem with scheduling procedure with parameters. When I tried to schedule the procedure without parameters it worked fine, but the one with parameters can not be scheduled :/
Here is my code:

create table test_table(
    id number,
    txt varchar2(1000)
);

create or replace procedure test_proc(p_id number,p_txt varchar2) is

begin
    insert into test_table values(p_id, p_txt);
    commit;    
end;
/

begin

  dbms_scheduler.create_job(
      job_name => 'job_test'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'mine.run_report(30,''text30'')'
     ,start_date => trunc(sysdate)
       ,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=6;BYMINUTE=50;'
     ,enabled => TRUE
     ,comments => 'test');

end;
/

when I try to create the job, I get an error:
Error at line 1
ORA-27452: mine.run_report(30,'text30') is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 3

It doesn't happen, when I create a job to run a procedure without parameters.....

;/
Re: dbms_scheduler - procedure with parameters [message #268956 is a reply to message #268934] Thu, 20 September 2007 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use job_type=>'PLSQL_BLOCK' and job_action => 'begin mine.run_report(30,''text30''); end;'

Regards
Michel

Re: dbms_scheduler - procedure with parameters [message #269008 is a reply to message #268934] Thu, 20 September 2007 07:47 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I haven't moved to the scheduler myself yet, but it looks like, as an alternative solution, you can also use the DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ptocedure to set your args.
Re: dbms_scheduler - procedure with parameters [message #269010 is a reply to message #269008] Thu, 20 September 2007 07:50 Go to previous messageGo to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Quote:
Use job_type=>'PLSQL_BLOCK' and job_action => 'begin mine.run_report(30,''text30''); end;'

that works fine - thanks

I'll also try the other solution.
Re: dbms_scheduler - procedure with parameters [message #276659 is a reply to message #268934] Thu, 25 October 2007 21:01 Go to previous message
buckeye
Messages: 1
Registered: April 2007
Junior Member
hi, I have encountered the same problem. Google has taken me here, but there is no reason about the problem.
I have erase this problem by the following way, wish it could helpful for who 'Google' to here.

1. create the procedure
create or replace procedure ts_storedproc
(a in VARCHAR2)
is
begin
INSERT INTO ttt VALUES(a, SYSDATE, current_timestamp);
COMMIT;
end ts_storedproc;

2. create the scheduler program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'ts_program2',
program_type => 'stored_procedure',
program_action => 'ts_program1',
--program_action => 'ts_storedproc(''1''); ',
number_of_arguments => 1,
enabled => FALSE,
comments => 'test sceduler');

dbms_scheduler.define_program_argument (
program_name => 'ts_program2'
,argument_position => 1
,argument_name => 't1'
,argument_type => 'VARCHAR2'
);

dbms_scheduler.enable(name => 'ts_program2');
END;


BE NOTE that the program_action parameter, there is no '()' or parameter. Parameters are set by dbms_scheduler.define_program_argument. It could be enabled only when the parameters have been defined.

[Updated on: Fri, 26 October 2007 04:59]

Report message to a moderator

Previous Topic: how to delete data
Next Topic: HAVING Clause
Goto Forum:
  


Current Time: Sun Dec 04 10:39:26 CST 2016

Total time taken to generate the page: 0.12209 seconds