Home » SQL & PL/SQL » SQL & PL/SQL » ORA-23319 error (Oracle 10gR2, Windows XP)
ORA-23319 error [message #408320] Mon, 15 June 2009 14:23 Go to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Hello,
i'm facing with a problem when running this:
declare
v_job number;
interval1 integer;
begin
select trunc(min(data_start)-sysdate) into interval1 from semestru where sysdate<data_Start;
dbms_job.submit(v_job,'roger.proc_upd_idsemestru_eleviclasa;',sysdate, 'sysdate+interval1');
end;

i got the error

ORA-23319: parameter value "sysdate+interval1" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at line 7

how can i correct it?

Thanks!
Re: ORA-23319 error [message #408321 is a reply to message #408320] Mon, 15 June 2009 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/node/871

[Updated on: Mon, 15 June 2009 14:35]

Report message to a moderator

Re: ORA-23319 error [message #408325 is a reply to message #408321] Mon, 15 June 2009 14:31 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
BlackSwan wrote on Mon, 15 June 2009 22:27
later

?
later?
Re: ORA-23319 error [message #408331 is a reply to message #408320] Mon, 15 June 2009 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'sysdate+'||to_char(interval1)

Regards
Michel
Re: ORA-23319 error [message #408334 is a reply to message #408331] Mon, 15 June 2009 14:56 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Mon, 15 June 2009 22:45
'sysdate+'||to_char(interval1)

Regards
Michel


Thanks Michael, it worked!
now how can i see the status of this job (if it's running, or some details about it) in PL/SQL?

Regards,
Re: ORA-23319 error [message #408335 is a reply to message #408320] Mon, 15 June 2009 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
QL> desc dba_jobs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB					   NOT NULL NUMBER
 LOG_USER				   NOT NULL VARCHAR2(30)
 PRIV_USER				   NOT NULL VARCHAR2(30)
 SCHEMA_USER				   NOT NULL VARCHAR2(30)
 LAST_DATE					    DATE
 LAST_SEC					    VARCHAR2(8)
 THIS_DATE					    DATE
 THIS_SEC					    VARCHAR2(8)
 NEXT_DATE				   NOT NULL DATE
 NEXT_SEC					    VARCHAR2(8)
 TOTAL_TIME					    NUMBER
 BROKEN 					    VARCHAR2(1)
 INTERVAL				   NOT NULL VARCHAR2(200)
 FAILURES					    NUMBER
 WHAT						    VARCHAR2(4000)
 NLS_ENV					    VARCHAR2(4000)
 MISC_ENV					    RAW(32)
 INSTANCE					    NUMBER
Re: ORA-23319 error [message #408337 is a reply to message #408320] Mon, 15 June 2009 16:14 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
i have a problem regarding to this job..

declare
v_job number;
interval1 integer;
begin
select trunc(min(data_start)-sysdate) into interval1 from semestru where sysdate<data_Start;
dbms_job.submit(v_job,'roger.proc_upd_idsemestru_eleviclasa;',sysdate, 'sysdate+'||to_char(interval1));
end;

i wrote this in an anonymous block, so when i execute it, the value to_char(interval1) is passed at the last parameter of dbms_job.submit. and this will be permanently the same in the job (i.e. if now i run this job, the last parameter will be: sysdate+1, so this is the repeating interval).
after the job executes successfully, the next_time is still sysdate+1. i wanna after the job is executed, to dynamically obtain that interval1 from the query
select trunc(min.........


is it possible? so i wanna set dynamically the next time the job will execute, depending on that select

Thanks
Re: ORA-23319 error [message #408339 is a reply to message #408320] Mon, 15 June 2009 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>is it possible?
It might be possible if you write a custom PL/SQL function
which make the desired SELECT & returns the desired results.
Re: ORA-23319 error [message #408379 is a reply to message #408339] Tue, 16 June 2009 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use:
dbms_job.submit (
  v_job,
  'begin 
     select sysdate+trunc(min(data_start)-sysdate)
       into next_date 
       from semestru 
       where sysdate<data_Start;
     roger.proc_upd_idsemestru_eleviclasa;
   end;',
  sysdate,
  'sysdate+'||to_char(interval1)
);

Regards
Michel

[Updated on: Tue, 16 June 2009 00:58]

Report message to a moderator

Re: ORA-23319 error [message #408388 is a reply to message #408379] Tue, 16 June 2009 01:37 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Hello,
thanks for reply
but
select sysdate+trunc(min(data_start)-sysdate)
into next_date
what is that next_date?
and at the repeating interva, i should not use interval1, isn't it?

Thanks

[Updated on: Tue, 16 June 2009 01:37]

Report message to a moderator

Re: ORA-23319 error [message #408391 is a reply to message #408388] Tue, 16 June 2009 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"next_date" is the internal variable Oracle uses to put the next execution date.

Regards
Michel
Re: ORA-23319 error [message #408392 is a reply to message #408391] Tue, 16 June 2009 01:56 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Tue, 16 June 2009 09:55
"next_date" is the internal variable Oracle uses to put the next execution date.

Regards
Michel


and then, at the end, should be 'sysdate+'||to_char(next_date) ?

[Updated on: Tue, 16 June 2009 01:57]

Report message to a moderator

Re: ORA-23319 error [message #408393 is a reply to message #408392] Tue, 16 June 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it is "next_date".

Regards
Michel
Re: ORA-23319 error [message #408395 is a reply to message #408393] Tue, 16 June 2009 02:05 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Tue, 16 June 2009 09:56
No it is "next_date".

Regards
Michel


So:

dbms_job.submit (
  v_job,
  'begin 
     select sysdate+trunc(min(data_start)-sysdate)
       into next_date 
       from semestru 
       where sysdate<data_Start;
     roger.proc_upd_idsemestru_eleviclasa;
   end;',
  sysdate,
  'sysdate+'||to_char(interval1)
);

if i use sysdate+trunc..... i will obtain a new date in next_date
and the repeat interval how should look?
'sysdate+next_date' ?
Re: ORA-23319 error [message #408406 is a reply to message #408395] Tue, 16 June 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can set "interval" to NULL as this is the execution of "what" parameter that will set the next date.

Regards
Michel
Re: ORA-23319 error [message #408410 is a reply to message #408406] Tue, 16 June 2009 02:46 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Tue, 16 June 2009 10:38
You can set "interval" to NULL as this is the execution of "what" parameter that will set the next date.

Regards
Michel


i didn't understand exactly what do you mean..
so the repeat interval how should like?
'sysdate+..... ?

Regards,
Re: ORA-23319 error [message #408441 is a reply to message #408410] Tue, 16 June 2009 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so the repeat interval how should like?

NULL, empty, nothing, nada...

Regards
Michel
Re: ORA-23319 error [message #408546 is a reply to message #408441] Tue, 16 June 2009 12:23 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Tue, 16 June 2009 12:25
Quote:
so the repeat interval how should like?

NULL, empty, nothing, nada...

Regards
Michel


it worked, thanks! Smile
Re: ORA-23319 error [message #408548 is a reply to message #408546] Tue, 16 June 2009 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, it is appreciated.

Regards
Michel
Re: ORA-23319 error [message #408550 is a reply to message #408548] Tue, 16 June 2009 13:21 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Michel Cadot wrote on Tue, 16 June 2009 20:39
Thanks for the feedback, it is appreciated.

Regards
Michel


one more question: in order to execute that job, what process should run? the TNS Listener, the database instance, or..? if i have the computer turned off, the job executes?

Thanks
Re: ORA-23319 error [message #408552 is a reply to message #408550] Tue, 16 June 2009 13:45 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The instance should run.
If the server is off, nothing run.

Regards
Michel
Previous Topic: How to Create Table with CLOB column (Size 1 GB Only)
Next Topic: which one is more faster
Goto Forum:
  


Current Time: Sat Dec 03 00:52:13 CST 2016

Total time taken to generate the page: 0.07735 seconds