Re: A PL/SQL parameter puzzle

Craig,

There are many different things you can do - think waaaaay outside of the box. Oracle is more powerful and flexible. Create a function within a package (datestuff) called dateadd and have 'yy' be a TYPE at the package level.

Hope this send your mind into overdrive - good luck. Also, there is the migration toolkit. Check out OTN for info.

Al

Craig Warman wrote:
> I'm involved in a SQL Server to Oracle migration where we're trying to avoid
> modification of existing SQL statements as much as possible. This includes
> calls made to SQL Server functions such as "dateadd" which do some simple
> date arithmetic.
>
> Probably you can see where I'm going with this - I can figure out the
> equivalent Oracle expressions that I'll need in a custom-written PL/SQL
> "dateadd" function. The part that I'm having trouble with is the
> parameters. Specifically, calls in the existing SQL to functions like
> dateadd look like this:
>
> dateadd(yy,2,some_date_value)
>
> Yep, no quotes around the yy, which designates that we want to return a date
> two years beyond the given date value. So I'm trying to come up with a
> nifty sort of way to make those un-quoted parameters work in Oracle.
>
> I've been mulling over the idea of setting up some sort of synonym for
> things like "yy" that point to constant definitions in a PL/SQL package
> (assuming that would even work), or writing a little one-liner functions
> like "yy" that return a value, but these just seem a bit on the clunky side.
>
> Anyone have any ideas for something more elegant?
>
> Craig
>
>
Received on Thu Nov 11 2004 - 16:16:39 CST