We have a product which was developed based on three-architecture using VB 6.0 for both COM component development and client-side front-end development, and SQL server 7.0 as the back-end tool. Now we are in need of changing the back-end to Oracle 8i. We are using MTS 2.0 for COM components deployment. Please tell me how far it is possible. If it is possible, tell me where are the places I have to do the changes. We are using stored procedures, and DSN-less connection using ADO object OLEDB for SQL Server 7.0 provider (ie. provider = SQLOLEDB).
Converting from SQL Server to Oracle is not an easy thing to do. On one hand, both are relational database systems and apply to a set of standards. On the other hand, each database vendor (not just MS and Oracle) implement things a little differently. As far as your application is concerned, you should be able to connect to both vendor's database systems.
You'll likely have your most difficulties in porting your tables and your database code to Oracle. In porting your tables over, you have to make sure that you are not using non-Oracle datatypes. And porting tables with large objects might require some coding to move the data. After that, you have your SQL statements. Oracle's SQL is different than Microsoft's TransactSQL. The basics are there, but they do things a little differently. And this will affect your stored procedures as well. MS and Oracle handle stored procedures differently.
One of the biggest things that you'll run into is that SQL Server has added things that make a developer's life easier. You'll be asking yourself, "Why can't I do _____ in Oracle?". If you were going the other way, you'd have the same questions. The best thing is to have someone who understands the differences in both products. They can lead your conversion efforts with some efficiency.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
This was first published in May 2001