Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> A PL/SQL parameter puzzle

A PL/SQL parameter puzzle

From: Craig Warman <crwarman_at_yahoo.com>
Date: Thu, 11 Nov 2004 15:35:06 -0500
Message-ID: <BDB9372A.A0B5%crwarman@yahoo.com>


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 - 14:35:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US