OraFAQ Forum: SQL & PL/SQL » dbms_scheduler

Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler - procedure with parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_scheduler - procedure with parameters [message #268934]

Thu, 20 September 2007 02:36

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.....

;/

Report message to a moderator

Re: dbms_scheduler - procedure with parameters [message #268956 is a reply to message #268934]

Thu, 20 September 2007 03:51

Michel Cadot
Messages: 68769
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use job_type=>'PLSQL_BLOCK' and job_action => 'begin mine.run_report(30,''text30''); end;'

Regards
Michel

Report message to a moderator

Re: dbms_scheduler - procedure with parameters [message #269008 is a reply to message #268934]

Thu, 20 September 2007 07:47

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.

Report message to a moderator

Re: dbms_scheduler - procedure with parameters [message #269010 is a reply to message #269008]

Thu, 20 September 2007 07:50

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.

Report message to a moderator

Re: dbms_scheduler - procedure with parameters [message #276659 is a reply to message #268934]

Thu, 25 October 2007 21:01

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 02 12:09:00 CDT 2025