473,245 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

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 10668

<jr********@hotmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.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********@hotmail.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********@hotmail.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.washington.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.washington.edu
(replace x with u to respond)
Jul 23 '05 #10
"DA Morgan" <da******@psoug.org> wrote in message
news:1120837514.778684@yasure...
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.washington.edu
(replace x with u to respond)

Thanks for the information - I had another look at the MS docs for Oracle
migration, and it appears that a more useful (and more extensive) document
would be this one, which does indeed say that BLOB has now replaced LONG
(see chapter 6, table 6.5):

http://www.microsoft.com/technet/its...l/default.mspx

Simon
Jul 23 '05 #11
Simon Hayes wrote:
"DA Morgan" <da******@psoug.org> wrote in message
news:1120837514.778684@yasure...
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.washington.edu
(replace x with u to respond)


Thanks for the information - I had another look at the MS docs for Oracle
migration, and it appears that a more useful (and more extensive) document
would be this one, which does indeed say that BLOB has now replaced LONG
(see chapter 6, table 6.5):

http://www.microsoft.com/technet/its...l/default.mspx

Simon


Better but still a long way from best practice. For example:
"Step 3: Fetch Strategy
Cursors are effective for row processing and batch processing."

The discussion seems to focus on technologies that are shared by both
Oracle and SQL Server such as cursors whereas any good PL/SQL developer
would take one look at a CURSOR LOOP and cut it from the code. In Oracle
9i and 10g best practice is to use bulk collection and FORALL.

The goal of the document seems to be get it to compile as close to the
original as possible and nothing more which will lead to poor
performance and scalability: Perhaps Microsoft's goal.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)
Jul 23 '05 #12

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

Similar topics

5
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,...
5
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...
1
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...
5
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...
1
by: sollento | last post by:
Hi, How can I export SQL Server Stored Procedures to MS-Access? Cheers H
0
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...
8
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...
1
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...
0
by: ind_123 | last post by:
How I can call an oracle function from SQL SERVER 2000 stored procedure. Anyone?
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.