Home » SQL & PL/SQL » SQL & PL/SQL » run in allowed time-zone (oracle 9.2.0.1.0)
run in allowed time-zone [message #377657] Wed, 24 December 2008 02:01 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
I want to run my script only if it is in the specified time zone.
The code for this will be added in a stored procedure before procedure start doing its job.
So that if time zone is not permitted nothing will be processed.

- Procedure should run in every 5 days with the time slot from 00:00 to 06:00 H H.

Please suggest me how to proceed.

Thanks
Shaksing
Re: run in allowed time-zone [message #377661 is a reply to message #377657] Wed, 24 December 2008 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Procedure should run in every 5 days

Use jobs (see DBMS_JOB).

Quote:
with the time slot from 00:00 to 06:00 H H.

Check SYSDATE and raise an error (see RAISE_APPLICATION_ERROR) if it is not in the time slot but if you correctly set your job, it will execute in the slot.

Now you have to specify:
- does the whole job execute in the slot or does it just start in the slot?
- what should it do if it starts/executes out of the slot? Should it restart at the next slot or at the next period of 5 days?
- Should it restart from the beginning or from where it stops?
...

Many questions to answer to get full specifications and solution.

Regards
Michel

Re: run in allowed time-zone [message #377663 is a reply to message #377657] Wed, 24 December 2008 02:23 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Quote:
Now you have to specify:
- does the whole job execute in the slot or does it just start in the slot?
- what should it do if it starts/executes out of the slot? Should it restart at the next slot or at the next period of 5 days?
- Should it restart from the beginning or from where it stops?
...


1) The checking is done in a stored procedure inside IF condition, if the time is in the allowed time slot then it control moves to else part and does whole lot of work.If it is not allowed then exit after generating alarm.

2) This check is done just after BEGIN of the procedure so that nothing will be processed if not allowed in the time zone.
Re: run in allowed time-zone [message #377668 is a reply to message #377663] Wed, 24 December 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2) This check is done just after BEGIN of the procedure so that nothing will be processed if not allowed in the time zone.

And then what does happen? Has the procedure to be restarted at the next slot or at the next 5 days period?

Regards
Michel
Re: run in allowed time-zone [message #377677 is a reply to message #377657] Wed, 24 December 2008 03:19 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah Michel it should run after 5 days again.
Re: run in allowed time-zone [message #377682 is a reply to message #377677] Wed, 24 December 2008 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is easy.
- In procedure, check SYSDATE is not correct time -> RAISE_APPLICATION_ERROR or EXIT depending if you want an alert or not.
- Everay 5 days -> DBMS_JOB

Regards
Michel
Re: run in allowed time-zone [message #377688 is a reply to message #377657] Wed, 24 December 2008 05:04 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
I tried this first to check the slot.

select to_char(sysdate,'HH24:MM') into start_date from dual;
	 
	 If start_date between to_char('00:00 AM','HH24:MM AM') and to_char('06:00 AM','HH24:MM AM') THEN
		
		ELSE
		   --call to the procedure of alarm management
		   DBMS_OUTPUT.PUT_LINE ('alarm management');
		end if;


Error occured- PLS-00307 too many declarations of 'string' match this call
Re: run in allowed time-zone [message #377690 is a reply to message #377688] Wed, 24 December 2008 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post the whole (useful) code, we don't know what is the datatype of start_date?

2/ TO_CHAR on a string is meaningless, you don't convert a string to string.

3/ Always copy and paste the session don't interpret it.

Regards
Michel
Re: run in allowed time-zone [message #377693 is a reply to message #377657] Wed, 24 December 2008 05:22 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
CREATE OR REPLACE PROCEDURE TEST1
IS
start_date varchar2(40);
begin

select to_char(sysdate,'HH24:MM ') into start_date from dual;
DBMS_OUTPUT.PUT_LINE (start_date);

If start_date between to_char('00:00 AM','HH24:MM AM') and to_char('06:00 AM','HH24:MM AM') THEN
DBMS_OUTPUT.PUT_LINE (start_date);
end if;


EXCEPTION
WHEN NO_DATA_FOUND
       THEN
             raise_application_error (-20001,' NO DATA FOUND ');     

END;
/


What changes do i need to make here Michel.
Re: run in allowed time-zone [message #377696 is a reply to message #377657] Wed, 24 December 2008 06:03 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to compare dates use dates, not string representation of dates.
Also that exception handler is pointless. If that code gives no_data_found then your database is seriously messed up.

All you actually need is something like this:
IF sysdate BETWEEN trunc(sysdate) AND trunc(sysdate) + 6/24 THEN
Re: run in allowed time-zone [message #377699 is a reply to message #377693] Wed, 24 December 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to cookiemonster's answer:
WHEN NO_DATA_FOUND
       THEN
             raise_application_error (-20001,' NO DATA FOUND ');

is:
1/ Useless: what do you add to letting the exception goes?
2/ Useless: it can't happen in your procedure

Regards
Michel
Re: run in allowed time-zone [message #377702 is a reply to message #377657] Wed, 24 December 2008 07:01 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Thanks cookiemonster , that worked

Thanks a lot.

Michel,

That was just to engage exception block , i am aware that is fo no use in that code.

Thanks a lot Michel and cookiemonster for your inputs.
Previous Topic: Export Table Data into Excel sheet
Next Topic: Applying Valid Number & Date Check
Goto Forum:
  


Current Time: Wed Dec 07 18:38:07 CST 2016

Total time taken to generate the page: 0.11721 seconds