Re: Oracle vs. Sybase

David Wetzel wrote:
> A new voice in the wilderness...

Welcome to the howling.... <G>

> Serious question: I'm an old sybase dba and have recently been handed an
> Oracle project. I was planning on using a lot of pl/sql, but I'm wondering
> what the down side is. The above seems to imply that pl/sql is a thing to
> be avoided. Maybe it's my Sybase background but I figured stored procs to
> be a GOOD thing. At the very least it localizes the business rules and
> cuts out some of the compiling the server has to do. Isn't that true in
> Oracle?

Nope, PL/SQL is not to be avoided nor is it implied. What is to be avoided is translating more or less verbatim Transact/SQL to PL/SQL. They are not the same language. The way one designs and codes for Sybase and its strengths is NOT necessarily the same way that one designs and codes for ORACLE and its strengths. Hence the resulting code, be it Transact or PL/SQL, will NOT be the same.

Stored procedures in ORACLE are excellent, one just doesn't write them using the same design guidelines and logic as one does for Transact. Why is this such a difficult concept to grasp? The fact that FORTRAN uses lots of GO TOs doesn't mean that one has to use them when porting to Pascal, or does it?

I'll try and give another (admiteddly) contrived example: In some applications originally written for Sybase I've seen MOL the following:

SELECT COUNT(COL_B) INTO VAR_A FROM TAB_A WHERE <complex condition_a>;
DELETE FROM TAB_A
WHERE <same complex condition_a as for SELECT above>;

with the intention obviously being to count the rows affected by the DELETE. I don't know if there is a better way of doing it in Sybase, but this is actual code written for it by people that had years of experience.

Now when this mob converted to ORACLE, they left the code exactly as above. Which with PL/SQL is completely unnecessary. The SELECT can be avoided by referencing one of the PL/SQL inbuilt variables which has the count of the rows of the last executed SQL statement. So by simply removing the SELECT and adding an assignment of that PL/SQL variable to VAR_A, after the DELETE, we avoided another parse and execution of SQL and still got the count of rows affected by said DELETE.

Things like this are what I mean by the differences that can be traps in conversions.

> Actually you can change the default block size in Sybase. Most people
> don't and it's not talked of, but some implementations of Sybase (Stratus
> for example) use different block sizes.
>

Allow me to repeat myself: "I'll still venture a few, which I believe apply to SQL Server,
like: from the point of view of the DBA, be able to use a block size larger than 2k;"

What in the above makes you believe that I was referring to Sybase? I know that the block size may change in Sybase, but I was SPECIFICALLY referring to SQL Server as in the MS product. The use of the expression "MS/Sybase" is not mine, it's someone else's. I refer to each of the two products specifically and separately. It appears nowadays "SQL Server" means the MS product and "Sybase" means the SQL Server sold by Sybase. So when I say "SQL Server", I'm talking about MS SQL Server, when I say "Sybase", I'm talking about Sybase's SQL Server. Is it clearer now?

> This one I may have to give you, assuming Oracle gets around the nested
> inner and outer join issue.

Don't give it to me, I don't work for ORACLE and I own only 1 (one) share of it! In fact, most of their consultants hate me with a vengeance, they know that I criticize them when I think they are wrong with the same gusto I criticize Sybase's "defenders of the faith" when I think they are wrong. ;-)  

> [snip]
> Here I have to say you're wrong. Sybase had between from the beginning.
> In fact it's essential for one of it's fundamental system tables. It's
> been there for at least the 9-10 years I've worked with Sybase.

Read again the original message, please. I didn't say "Sybase" don't have the between, I said I couldn't use it as the JOIN condition in MS "SQL Server"! There is a difference...

Cheers and thanx for your feedback.
Nuno Souto
nsouto_at_acay.com.au Received on Thu Jun 26 1997 - 00:00:00 CDT