Basic function question

[mwolf00]

It's been about 5 years since I trained on Oracle and I haven't used it since (I've been using SQL Server). I'm trying to write a basic function that will take a date and make sure that it happens between 7AM and 5PM M-F excluding holidays. I know that I could do it in a SQL Server procedure, but I think that Oracle won't let me declare and set variables inside a function. Should I use a procedure instead?

CREATE OR REPLACE FUNCTION rbm_fGetToolTime(dtTime date, intRegionStart smallint, intRegionEnd smallint) RETURN date

AS

/* 
this function takes a date and makes sure that it is put into
standard format for the tool

All dates returned will be within the business day of the given region
*/
[red]
DECLARE
         intBusHrs  smallInt;[/red]
	   
BEGIN

	 RETURN SYSDATE();

END;

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SantaMufasa]

I think that Oracle won't let me declare and set variables inside a function.

Someone gave you very incorrect information.


Please post the code that you would use, and we'll respond to your code assertion.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Dagon]

Oracle PL/SQL would be poor indeed if it didn't allow you to "declare and set variables". You have already declared one (intBusHrs) and there is nothing to stop you setting this.

 

[mwolf00]

DOH!

The problem was that I was using the DECLARE keyword where none is needed --> Declarations are implicit before the BEGIN I guess.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook