Oracle Stored Procedures VERSUS SQL Server Stored Procedures

[jrefactors@hotmail.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!!

[Jim Kennedy]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures


<jrefactors@hot mail.com> wrote in message
news:1120793932 .351921.5760@g4 3g2000cwa.googl egroups.com...[color=blue]
> 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!!
>[/color]
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

[Mladen Gogala]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote:
[color=blue]
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?[/color]

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

--

http://www.mgogala.com

[HansF]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors interested us by writing:
[color=blue]
>
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?[/color]

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.
[color=blue]
> Please advise good references for Oracle stored procedures also.
>[/color]

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 ***

[Noons]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

HansF wrote:[color=blue]
> 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.)[/color]

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.

[Billy]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

jrefactors@hotm ail.com wrote:[color=blue]
> 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?[/color]

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.
[color=blue]
> Please advise good references for Oracle stored procedures also.[/color]

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

[DA Morgan]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

jrefactors@hotm ail.com wrote:[color=blue]
> 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!![/color]

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

damorgan@x.wash ington.edu
(replace x with u to respond)

[HansF]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

On Thu, 07 Jul 2005 22:15:23 -0700, Noons interested us by writing:
[color=blue]
> HansF wrote:[color=green]
>> 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.)[/color]
>
> 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.[/color]

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 ***

[Simon Hayes]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

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/evaluation/compare/oracle.mspx

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/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

[DA Morgan]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Simon Hayes wrote:[color=blue]
> 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[/color]

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

damorgan@x.wash ington.edu
(replace x with u to respond)

[Simon Hayes]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

"DA Morgan" <damorgan@psoug .org> wrote in message
news:1120837514 .778684@yasure. ..[color=blue]
> Simon Hayes wrote:[color=green]
>> 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[/color]
>
> 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
> damorgan@x.wash ington.edu
> (replace x with u to respond)[/color]


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/itsolutions/cits/interopmigration/unix/oracleunixtosql/default.mspx

Simon

[DA Morgan]

Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Simon Hayes wrote:[color=blue]
> "DA Morgan" <damorgan@psoug .org> wrote in message
> news:1120837514 .778684@yasure. ..
>[color=green]
>>Simon Hayes wrote:
>>[color=darkred]
>>>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[/color]
>>
>>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
>>damorgan@x.wa shington.edu
>>(replace x with u to respond)[/color]
>
>
>
> 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[/color]

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

damorgan@x.wash ington.edu
(replace x with u to respond)