Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Oracle8i features not found in MS SQL Server 2000

I'm in a stage of having conducted a feasibility study of a database porting project. The project is to port an Oracle 8i DB to MS SQL 2000. I was wondering if you can help us by providing a list of features (or some useful links) that are not available in SQL 2000, but in Oracle 8i. I'm expecting technical details as well.

This is one of the kind of questions where any response is likely to be "wrong", since it won't cover whatever information you need to know. With that as a given, I'll try to answer it to the best of my ability anyway.

Since Oracle 8i doesn't support much of SQL-92, I can't fall back on the cop-out answer of saying that anything that adheres to the standard should port nicely. That being said, should any of the Oracle code happen to conform to the SQL-92 standard, that code should port nicely.

Particular problem child areas are things that do iterative processing within result sets. Oracle's CONNECT BY is a great example of this kind of problem. These kind of constructs are problems for any SQL database. Oracle's implementations of the SQL-92 "TOP N" are also problematic, check the Oracle documentation for how this construct is implemented in the Oracle version you are porting from for details on how to convert it to the TOP N syntax. Any PL/SQL features that access the operating system in any way, shape, or form are also problems, some of which have no generic solution (they have to be dealt with case-by-case).

The way the Oracle 8i handles JOIN operations (which was standard in SQL-89), is also problematic for MS-SQL 2000. You can simulate many of those behaviors using the MS-SQL 6.5 JOIN operators, but you'll get cleaner code and often get better performance by switching to the SQL-92 JOIN syntax. Be very careful to check any application code that compensates for the way that Oracle handles these operations... Those can be major "gotchas" during conversion.

Oracle is a market leader. They've worked hard to expand PL/SQL to support many features that the standard doesn't (or at least didn't) address. These features work against anyone trying to convert an application that has been customized to use Oracle's features to another language, especially if that language is closer to the standard.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.