473,700 Members | 2,564 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?

Please advise good references for Oracle stored procedures also.

thanks!!

Jul 23 '05 #1
11 10750

<jr********@hot mail.com> wrote in message
news:11******** ************@g4 3g2000cwa.googl egroups.com...
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?

Please advise good references for Oracle stored procedures also.

thanks!!

SQL Server Stored procedures use transact sql and run on SQL Server
Oracle Stored Procedures use pl/sql (modeled on ADA) and run on Oracle.

Oracle!=SQL Server

They use two very different concurrent models and the syntax is totally
different.
(eg you don't need to create temp tables in Oracle like in SQL Server)
otn.oracle.com and look under documentation.
Jim
Jul 23 '05 #2
On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax?


No, you just have to obtain Microsoft version of PL/SQL.

--
http://www.mgogala.com

Jul 23 '05 #3
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 ***

Jul 23 '05 #4
HansF wrote:
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.)


Careful withthe diffs between Standard Oracle and
EE or Personal: OLAP is not available on Standard
in 9i. Dunno about 10g but I suspect it's the same.

Jul 23 '05 #5
jr********@hotm ail.com wrote:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?
Concept is the same (performance wise). Everything else is different.
PL/SQL is formal programming language like Java or C/C++ or Delphi.

It has OO support - so you can create classes and subclasses. You have
various APIs. You can talk to TCP servers (telnet, ftp), LDAP servers,
SMTP servers, HTTP servers. In other words, in PL/SQL you have a web
browser, an e-mail client, a TCP client, a LDAP client. Then there are
APIs for flat file I/O, encryption and decryption, IPC (Inter Process
Communication) via database pipes and message queues, data capture
streams, and many more.

PL/SQL is nothing like Transact-SQL. Transact-SQL is a macro language
at best. Which has its uses. But PL/SQL is as much a programming
language as any other formal development language today.

Case in point. The Oracle Application product suite consists of over a
million lines of PL/SQL. Oracle's replication is written in PL/SQL (and
not C).

I have a PL/SQL procedure that maps incoming router traffic to the
source and then inserts it into a table. This procedure is called by a
process that gets the network traffic data from various Cisco routers.
The stored proc is executed in excess of 300 times per second. Its SQL
statements (in the Oracle shared pool) typically shows (indvidually)
1.5 billion (and more) executions over time.

I also use PL/SQL as the application tier as oppose to Java/J2EE -
Oracle is both my app server and database server. This not only scales
better, but is faster to develop, easier to maintain, has less moving
parts and a lot cheaper than the traditional Java app server setup.

PL/SQL is the fastest and most optimal way to deal with data in Oracle.
Period.
Please advise good references for Oracle stored procedures also.


PL/SQL Reference Guide. Application Developer Fundementals Guide.
Application Developer Object Relational Features Guide. PL/SQL Packages
and Types Guide.

The best is however to get your feet wet and start writing PL/SQL code
and getting to grips with the features and fundementals of PL/SQL and
Oracle.

--
Billy

Jul 23 '05 #6
jr********@hotm ail.com wrote:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures
execute in the database server with better performance?

Please advise good references for Oracle stored procedures also.

thanks!!


A far simpler question might be "Is there anything similar between
SQL Server 2000 and Oracle stored procedures. The differences are
at the very core of what constitutes a transaction, the locking
model, the object types available, the concurrency model, and
capabilities within Oracle such as Flashback for which no similar
capability exists in SQL Server ... not 2000 and not Yukon.

My recommendation would be that you purchase Tom Kyte's book "Expert
one-on-one Oracle" and read, with special care, the first three
chapters. Especially the discussion of multiversion read consistency.

If you wish to explore further you can download Oracle for free from
http://otn.oracle.com, purchase the CD Packs for about $70 USD from
http://store.oracle.com, and should visit http://tahiti.oracle.com
for reference materials.

HTH
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)
Jul 23 '05 #7
On Thu, 07 Jul 2005 22:15:23 -0700, Noons interested us by writing:
HansF wrote:
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.)


Careful withthe diffs between Standard Oracle and
EE or Personal: OLAP is not available on Standard
in 9i. Dunno about 10g but I suspect it's the same.


You are right that the OLAP option is only available on Enterprise Edition.

However, my reading of the OLAP manuals indicates that a number of the
analytic functions, such as lead/lag/rollup in SQL statements, are not
explicitly listed as part of the OLAP option and *may* therefore be
available in Standard. (Confirmation from Oracle appreciated!)

On re-reading, I see a potential confusion - the 'most in this category'
was meant to refer to the 'whole pile of things', not 'most at no added
charge'. Thanks for pointing that out.
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard _at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

Jul 23 '05 #8
You don't say if your background is in Oracle or MSSQL, but if it's
Oracle, then these links might be useful:

http://www.microsoft.com/sql/evaluat...re/oracle.mspx
http://www.microsoft.com/resources/d...rt2/c0761.mspx

As other posters have said, the two products have a large number of
very significant differences, so it's often difficult to make very
direct comparisons.

Simon

Jul 23 '05 #9
Simon Hayes wrote:
You don't say if your background is in Oracle or MSSQL, but if it's
Oracle, then these links might be useful:

http://www.microsoft.com/sql/evaluat...re/oracle.mspx
http://www.microsoft.com/resources/d...rt2/c0761.mspx

As other posters have said, the two products have a large number of
very significant differences, so it's often difficult to make very
direct comparisons.

Simon


Just a quick note. The second link appears to refer to Oracle 8i which
has not been sold by Oracle for more than 4 years.

For example this statement:
"Blob type storage - One long or long raw per table, must be at end of
row, data stored on same block(s) with row."

LONG and LONG RAW data types have been deprecated. The BLOB data type,
as early as 9i held 4GB, had no restriction as to the number per table,
did not need to store the information in the same block with the row,
and did they need to be at any specific location in a table: A statement
which in and of itself is wrong as can be clearly seen:

1 create table ms_is_wrong (
2 col1 NUMBER(5),
3 longcol LONG,
4 col3 NUMBER(5),
5* col4 DATE)
SQL> /

Table created.

SQL> desc ms_is_wrong
Name Null? Type
----------------------------------------- -------- ---------
COL1 NUMBER(5)
LONGCOL LONG
COL3 NUMBER(5)
COL4 DATE

Clearly the LONG does not need to be the last column. And clearly
Microsoft has done its user community no favor with this document
as there are a large number of similarly egregious errors.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
8288
by: Steve Holden | last post by:
Has anyone, with any driver whatsoever, managed to retrieve output parameters from a SQL Server stored procedure? I've just been rather embarrassed to find out it's not as easy as it might seem, and people are saying bad things about Python as a result :-( mx.ODBC, which I regard as a highly-capable module, does not support the callproc() API, and suggests use of the ODBC call format. It has caveats in (some of) the documentation...
5
2529
by: Florin | last post by:
Hi, I am experiencing some problems accessing an Oracle database through OLE DB from an MTS application using OpenWithServiceComponents (which is supposed to give me connection pooling). When I connect to a SQL Server database it only takes 3.1 ms to open a connection, while with Oracle it takes 15.5 ms (both DB's running on the same machine). Am I doing something wrong, am I missing something here? I have tried to use the plain Open...
1
6754
by: T.S.Negi | last post by:
Dear All, Please suggest some of the best practices for writing SQL server stored procedures? I'm writing a business function (stored procedure), which calls many-stored procedure one after another. I want this to be best optimized, so that speed can be very good. Suggestion in this regard will be appreciated.
5
4531
by: Jeff | last post by:
I have question about differences in fenced sql procedures and fenced stored procedures. Do fenced sql procedures take up an extra memory segment when executed? Reason I ask is we have several fenced sql procedures that have been excuting o.k. We implemented a fenced stored procedure on a C program and when trying to execute it, we get the "DIA3833C The system memory limit was reached."
1
1557
by: sollento | last post by:
Hi, How can I export SQL Server Stored Procedures to MS-Access? Cheers H
0
2645
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page Every modern database system has a stored procedure language. SQL Server is no different and has a relatively sophisticated and easy to use system. This article will not attempt to go into depth in explaining...
8
14372
by: danbredy | last post by:
Hi, I'm attempting to connect to an Oracle database using SQL Server 2005 Express (OS is Windows XP Professional) and having absolutely no luck. Here is the information SQL Plus gives me about the database when I log in - Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options
1
1810
by: Genken | last post by:
How would i convert or write a simple or complex access query to sql server stored procedures i will include my most complex query i hope somebody can help. Thankyou in advance. SELECT tblRiskCategory.RiskCategoryID, tblRiskCategory.RiskCategory, tblRiskReference.RiskRef, tblRiskReference.RiskDescription, tblCluster.ClusterID, tblCluster.Cluster, tblAPDescription.AP_ID, tblAPDescription.APName, tblAPDescription.APRef, tblBUReference.BUID,...
0
1421
by: ind_123 | last post by:
How I can call an oracle function from SQL SERVER 2000 stored procedure. Anyone?
0
8731
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9081
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8931
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7819
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4660
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3097
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2395
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2032
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.