Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: A PL/SQL parameter puzzle

From: HansF <>
Date: Fri, 12 Nov 2004 02:09:22 GMT
Message-ID: <mXUkd.148032$9b.3839@edtnps84>

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 (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. <g>)

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 Received on Thu Nov 11 2004 - 20:09:22 CST

Original text of this message