Posted by HansF on 07/08/05 07:05

On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors interested us by writing:

>
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?

They are completely different languages.

Oracle PL/SQL uses the robust Ada language as it's template.

Not sure what Sybase used as it's template - seems like a mix of Pascal
and Basic. Very reasonable in it's own way as well. IIRC, Microsoft has
not extended the basic too much when they turned the core into SQL Server
- lots of nice add-ons though.

More to the point, because of the differences in database internals, such
as locking strategies, the way you need to approach your code is
significantly different. For example, in SQL Server it's reasonable to
create temp tables on the fly to hold intermediate results, whereas in
ORacle you use a predefined global temp table. In Oracle, readers don't
block writers and writers don't block readers so far less explicit locking
occurs in Oracle in general. And in Oracle, the fastest way to kill your
scalability is to build your SQL statement on the fly for each client -
bind variables are very, very useful.

> Please advise good references for Oracle stored procedures also.
>

All Oracle docco is online, free of charge, at http://docs.oracle.com ...
recommended reading for an app developer is (in order):

Oracle Concepts manual
Oracle Application Developer's Guide - Fundamentals
Oracle SQL Reference
Oracle PL/SQL User's Guide and Reference
Oracle Supplied PL/SQL Packages and Types Reference

Note that Oracle supplies a whole pile of added things in the database,
most at no added charge. Some examples in this category include:
message queueing; text indexing and search; doc indexing and search;
multimedia storage and retrieval; workflow; OLAP; XML. (Yes many also
occur in SQL Server.)

To avoid duplicating what is provided, I recommend selecting from the
remaining manuals and guides for API and examples.

The following books are, IMO, mandatory in your library:

O'Reilly "Mastering Oracle SQL"
- there's a lot that doesn't need PL/SQL
APress "Mastering Oracle PL/SQL"
- bible!
Oracle Press "Effective Oracle by Design"
- a MUST read if you care about performance
- goes a LONG way to explaining the differences in code style

Have fun
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

[Back to original message]