473,840 Members | 1,566 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
11 10766
"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.wash ington.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.wa shington.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.wash ington.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
8313
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
2545
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
6762
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
4539
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
1566
by: sollento | last post by:
Hi, How can I export SQL Server Stored Procedures to MS-Access? Cheers H
0
2659
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
14381
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
1820
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
1423
by: ind_123 | last post by:
How I can call an oracle function from SQL SERVER 2000 stored procedure. Anyone?
0
9699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10922
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10301
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
7023
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
5685
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5874
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
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
4076
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3138
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.