Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

From: HansF <News.Hans_at_telus.net>
Date: Fri, 08 Jul 2005 04:05:10 GMT
Message-Id: <pan.2005.07.08.05.04.25.64771@telus.net>


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"

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 ***
Received on Thu Jul 07 2005 - 23:05:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US