Re: A PL/SQL parameter puzzle

From: Craig Warman <crwarman_at_yahoo.com>
Date: Thu, 11 Nov 2004 20:16:29 -0500
Message-ID: <BDB9791D.A0C9%crwarman@yahoo.com>

Al - Thank you for the helpful reply. I am in fact using the migration workbench for the database objects, and have had good luck with getting that part out of the way. I considered making use of user-defined TYPEs for this particular problem, but had thought that those would only be useful in the declaration section of a routine - so based on your advice I'll take another look at that, then. Thanks a bunch.

Craig

On 11/11/04 5:16 PM, in article %wRkd.6203$jE2.3862_at_bignews4.bellsouth.net, "b" <b_at_aol.com> wrote:

> 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 - 19:16:29 CST