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
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.