Re: A PL/SQL parameter puzzle

Kurt - Thank you for sharing your experience here. Your port was much more extensive than what we're undertaking here - and I agree, XML is implemented by SQL Server and Oracle in a vastly different ways. Thankfully we don't have that issue to face on this particular migration! We expect that the ported system in this case will require some tuning to get past performance issues, but there's no doubt that the port will succeed.

By the way, I ended up resolving the problem from my original post this morning. It turns out that SQL Server implements things like "day", "month", "quarter" as both functions and datepart identifiers. So, since we need to implement those as functions anyway, the solution falls out from there. Consider these:

    dateadd(month,2,some_date_value)
    month(some_date_value)

Since "month" has to be implemented as a function to work in the second case, we'll implement it such that it returns an appropriate value when no parameter is given so that calls to dateadd, datediff, etc. work properly. Public synonyms to these functions will then be created to make then work across the board.

I originally considered this solution to be "clunky" at best - but once I realized that these datepart identifiers were themselves SQL Server functions it ended up being the proper course of action after all.

Craig

On 11/12/04 9:28 AM, in article ZtOdnemZEMPnVQncRVn-rw_at_telcove.net, "KurtisK" <KJKYLE_at_COOLBLUENOSPAM.COM> wrote:

> We performed a very successful port of SQLServer to Oracle. And not just
> simple SQL statements. We ported over 500 stored procs that do XML
> shredding of incoming XML requests into data that is then validated inside
> the procedures and then either used to update tables or just return data.
> Of course we had to do some things differently in Oracle. Some inline
> functions used in our SQL statements caused major performance drags in
> Oracle so we had to work around that issue. Oracle's XML technology is much
> different syntantically speaking, so that all changed. etc, etc.
>
> It was interetesting to find that Oracle performed slower in many scenarios
> whereas our preconception was that it would perform better than SQLServer.
> This of course all depends on what you are doing with the code which Oracle
> is much more flexible and scalable in many ways.
>
> Kurt
Received on Fri Nov 12 2004 - 09:17:21 CST