Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!news.tele.dk!news.tele.dk!small.news.tele.dk!fu-berlin.de!uni-berlin.de!not-for-mail From: Craig Warman Newsgroups: comp.databases.oracle.misc Subject: Re: A PL/SQL parameter puzzle Date: Thu, 11 Nov 2004 21:58:29 -0500 Lines: 70 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Trace: news.uni-berlin.de HxJweMUKkzUm1srmRBz+eg+B+G5o9mzI8DSXwBMJJsiLoHnAyM User-Agent: Microsoft-Entourage/10.1.6.040913.0 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:114195 Hans - Your point is well taken. The locking strategies of SQL Server and Oracle are completely orthogonal to one another, and I agree that that most ports fail due to that reason. This is one case where I do happen to feel that the port to a second database platform will succeed. Here is why. In this particular case, the developers implemented their use of the database strictly as a datastore; they avoided any use of platform-specific locking strategies or functionality (or so they thought, since they used this particular set of SQL Server function calls!). They also developed an abstraction layer between their code and the database. This was done because they knew all along that their system may need to be ported to a new platform - and sure enough, they're faced with it now. Everyone knows that the system doesn't run as fast as it could, but in spite of this 'generic' SQL code implementation, have been in production with 1000's of users across a wide region of the country for several years now. I can't argue with that. With enough processor and memory, you can overcome most performance problems. They have a *lot* of processor and memory. It won't be an issue. So I'd like to get past all that now. It looks like I've got two good suggestions in response to my original question - One from a prior post that suggests using a TYPE definition (I'm looking into that one) and your recommendation to consider the use of constants. So, as a follow-up, given that this is a Perl-based system (I didn't mention that earlier, sorry) are you suggesting perhaps that we define constants at the application level and use those in our calls to the database? Craig On 11/11/04 9:09 PM, in article mXUkd.148032$9b.3839@edtnps84, "HansF" wrote: > Sybrand's response is a bit harsh, but that's not unusual. He, like a > number of others here, have attempted to respond to the 'quick and easy > port from xyz' many times in the past. It behooves you to check the > archives of this group at http://groups.google.com (or other sources). > > Regardless of his choice of words, he is teling you clearly that porting SQL > Server code to Oracle generally fails. The reason is simple - the two > systems have entirely different internal mechanisms to handle things like > locking and concurrency. In fact, Oracle tends to avoid locking things the > same way as SQL Server so that readers do not block writers and vice versa. > > (Just like lipstick on a pig, satisfaction with a port will only come about > when it's applied properly and it takes a skilled artist to do that. ) > > There are a lot of implications, many clearly indicated in Thomas Kyte's > books: "Expert One on One Oracle" and "Effective Oracle By Design". Those > books describe the challenges and the necessary tactics to avoid many of > the issues and come complete with working, testable examples. > > The biggest implication is that a traditional port [in either direction] > will usually have serious performance issues that only show up under load > testing (which frequently means, only when in production). Once in > production, those issues frequently result in catastrophic failure and > system abandonment or rewrite, usually after someone has been fired. > > Now back to your question - consider a global package spec pinned at startup > that defines those yy values as constants (read only variables). A major > yuk, but possibly workable? > > Good luck. > > /Hans