Re: sql server stored procedure to oracle

ping wrote:

> Hi Actualy migrating stored procedures from sql server 2000 to oracle 9i
>
> The main differance betwwen SQL server & Oracle stored procedure is that
> Oracle's SP can't return a result set
>
> What is the best way to create an oracle object that could return a row set,
> I don't even know what kind of object can do this
>
> exemple of a simple sql server sp I need to convert to an oracle object:
>
> CREATE PROCEDURE getlist _at_ID int
> AS
>
> BEGIN
> SELECT DISTINCT name
> FROM list
> WHERE (id = _at_ID)
> ORDER BY name
> END
> GO
>
> Thank's for you help
>
> David

I'm not going to restate the corrections in your statements already made by Tom and Sybrand both of whom are correct.

But I do want to address a major misunderstanding you have that goes far beyond your false statement.

There is a huge amount that is different between SQL Server 2000 and Oracle. And it starts with the following bulleted points.

• Multiversion read consistency
• Reads don't block writes
• Writes don't block reads
• Infinite row level locks
• No lock escallation
• Sequences
• Many trigger types not available in SQL Server
• Many index types not available in SQL Server
• No need to use temp tables (in fact rarely even desirable)

If you don't code with these in mind; with a thorough knowledge of Oracle concepts and architecture, you are dooming your code to, at best, being unscalable and slow and at worst corrupting data.

I would strongly urge you to stop your assuming because it is going to cause you a lot of problems and start by going to http://tahiti.oracle.com and reviewing the architecture and concepts documentation. Then purchas a copy of Tom Kyte's book "Expert one-on-one Oracle" and read it cover-to-cover ... especially the first three chapters.

If you are going to make a mess please don't blame Oracle for it.

Daniel Morgan Received on Tue Jan 14 2003 - 18:47:20 CET