OraFAQ Forum: Server Administration » DBMS

Home » RDBMS Server » Server Administration » DBMS_JOB submission error

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

DBMS_JOB submission error [message #173023]

Fri, 19 May 2006 00:53

gajini
Messages: 262
Registered: January 2006

Senior Member

SQL> create or replace procedure demo as
2 begin
3 insert into emp27 values(1);
4 end;
5 /

Procedure created.

exec dbms_job.submit(what => 'demo;',
next_date => trunc(sysdate+1/144,'MI'), -- start next 10 mts
interval => 'trunc(sysdate+1/144,''MI'')' -- Run every 10 mt
);

can anyone tell me what error in this,what i've to do submit the job properly.

SQL> exec dbms_job.submit(what => 'demo;',
BEGIN dbms_job.submit(what => 'demo;',; END;

*
ERROR at line 1:
ORA-06550: line 1, column 39:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe

SQL> next_date => trunc(sysdate+1/144,'MI'), -- start next 10 mts
SP2-0734: unknown command beginning "next_date ..." - rest of line ignored.
SQL> interval => 'trunc(sysdate+1/144,''MI'')' -- Run every 10 mt
SP2-0734: unknown command beginning "interval =..." - rest of line ignored.

Report message to a moderator

Re: DBMS_JOB submission error [message #173027 is a reply to message #173023]

Fri, 19 May 2006 01:16

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Try the following:

declare
	v_job number;
begin
	dbms_job.submit(job => v_job,
	what => 'demo;',
	next_date => sysdate,
	interval => trunc(sysdate+1/144));
commit;
end;
/

Report message to a moderator

Re: DBMS_JOB submission error [message #173057 is a reply to message #173027]

Fri, 19 May 2006 03:58

gajini
Messages: 262
Registered: January 2006

Senior Member

Hi,

I've tried the new thing,but it shows the follwing error.please correct me

create table job27(now date);

create procedure do_job_demo as
begin
Insert into job27(now) values (SYSDATE);
commit;
end;

create procedure job_call_demo as
jobno user_jobs.job%TYPE;
begin
dbms_job.submit(job=>jobno,
what=>'do_job_demo;',
next_date => sysdate,
interval => trunc(sysdate+1/1440));
end;

exec job_call_demo;

SQL> exec job_call_demo;
BEGIN job_call_demo; END;

*
ERROR at line 1:
ORA-23319: parameter value "19-MAY-06" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at "SYSTEM.JOB_CALL_DEMO", line 4
ORA-06512: at line 1

Report message to a moderator

Re: DBMS_JOB submission error [message #173120 is a reply to message #173023]

Fri, 19 May 2006 08:37

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Interval is a string not a date data type.

Report message to a moderator

Re: DBMS_JOB submission error [message #173143 is a reply to message #173057]

Fri, 19 May 2006 11:05

ebrian
Messages: 2794
Registered: April 2006

Senior Member

As smartin pointed out, there should actually being single-quotes around the interval value:

declare
	v_job number;
begin
	dbms_job.submit(job => v_job,
	what => 'demo;',
	next_date => sysdate,
	interval => 'trunc(sysdate,''mi'')+1/144');
	dbms_output.put_line(v_job);
commit;
end;
/

Those are two single-quotes around the mi in the interval entry.

Also, don't forget to include the COMMIT.

[Updated on: Fri, 19 May 2006 11:21]

Report message to a moderator

Previous Topic:	oracle install check
Next Topic:	Error while selecting user_jobs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:49:41 CDT 2024