Home » SQL & PL/SQL » SQL & PL/SQL » Job scheduling problem (oracle db 10)
Job scheduling problem [message #434533] Wed, 09 December 2009 10:37 Go to next message
ator
Messages: 38
Registered: March 2009
Member
Hi,
before open this post i've looked all over internet and the forum but i didn't find a solution.

I have a trigger which creates my job when i insert a row and the code is the one belove.
DECLARE         
   PRAGMA AUTONOMOUS_TRANSACTION;
Begin
   If INSERTING then  
      null;
        if :NEW.FREQUENZA = 'ONCE' then
           dbms_scheduler.create_job(job_name => :NEW.Nome_Job,
                                     job_type => 'STORED_PROCEDURE',
                                     job_action => :NEW.nome_procedure,
                                     number_of_arguments => 1,
                                     start_date => to_timestamp(to_char(:NEW.data_inizio,'YYYYMMDD')||:NEW.ora_inizio,'YYYYMMDDHH24MI')
                                     );
        else
          dbms_scheduler.create_job(job_name => :NEW.Nome_Job,
                                     job_type => 'STORED_PROCEDURE',
                                     job_action => :NEW.nome_procedure,
                                     number_of_arguments => 1,
                                     start_date => to_timestamp(to_char(:NEW.data_inizio,'YYYYMMDD')||:NEW.ora_inizio,'YYYYMMDDHH24MI'),
                                     repeat_interval => 'FREQ='||:NEW.Frequenza||';'||:NEW.Ripetizione,
                                     end_date => to_timestamp(to_char(nvl(:NEW.data_fine,to_date('99990101','YYYYMMDD')),'YYYYMMDD')||'2359','YYYYMMDDHH24MI')
                                     );  
        end if;  
        dbms_scheduler.set_job_argument_value(job_name => :NEW.nome_job,
					      argument_position => 1,
					      argument_value => to_char(:NEW.id_job));
        dbms_scheduler.enable(:NEW.Nome_job);                      
    ElsIf DELETING Then
        begin
		    dbms_scheduler.disable(:OLD.Nome_Job);
        	dbms_scheduler.drop_job(:OLD.Nome_Job); 
	    exception when others then
		    null;
	    end;
    End If;                                       
    
End;


If data_fine is null the job is create correctly but if is not null then i get the ORA-27483 but the end_date is greater than start_date.

I hope u can help me to figure out how to resolve this problem Smile

Cheers Ator.

ps: sorry for my english
Re: Job scheduling problem [message #434537 is a reply to message #434533] Wed, 09 December 2009 10:52 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What are the actual values you are using for data_fine and data_inizio and what are the datatypes of those columns?
Re: Job scheduling problem [message #434538 is a reply to message #434533] Wed, 09 December 2009 11:01 Go to previous messageGo to next message
ator
Messages: 38
Registered: March 2009
Member
Data_fine and data_inizio are Date type.
Data_fine usually is null or is >= at data_inizio so the error shouldn't be throw by the trigger.


Thx for the answer.

Ator
Re: Job scheduling problem [message #434602 is a reply to message #434538] Wed, 09 December 2009 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Data_fine usually is null or is >= at data_inizio so the error shouldn't be throw by the trigger.
Then you should submit a BUG Service Request via Metalink.
Re: Job scheduling problem [message #434664 is a reply to message #434533] Thu, 10 December 2009 03:13 Go to previous message
ator
Messages: 38
Registered: March 2009
Member
I found the solution hopefully.
The problem is that if the frequency is monthly the end date must be at least one month greater than start_date and if it's yearly at least one year.

they could explain it somewhere in the guide though Confused

I guess i'm the only one who does this things Sad .

thy for your help
Ator
Previous Topic: Getting an ORA-02264 Error on Table Drop
Next Topic: error in using the utl_file package
Goto Forum:
  


Current Time: Thu Sep 29 09:01:26 CDT 2016

Total time taken to generate the page: 0.14951 seconds