If you have developed an application using Oracle Developer 2000 and want to use it with SQL Server, consider converting it to Microsoft Visual Basic. Visual Basic is a powerful development system that works well with both databases. You might also consider other development tools in Microsoft Visual Studio, or PowerBuilder, SQL Windows, and others.
If you are unable to immediately migrate from Developer 2000, consider the Oracle Gateway to SQL Server. It can be used as an intermediate step when migrating from Oracle to SQL Server. This gateway allows the Oracle RDBMS to connect to SQL Server. All requests for SQL Server data are automatically translated by the gateway. From the perspective of the Developer 2000 application, this connection is transparent. SQL Server data appears as Oracle data. Very few changes need to be made to the application program code.
Another intermediate step is to use the Developer 2000 application directly with SQL Server. Developer 2000 can directly access SQL Server using the Oracle Open Client Adapter (OCA). The OCA is ODBC Level 1 compliant and has limited support for ODBC Level 2 functions.
The OCA establishes a connection with the SQL Server ODBC driver. When connecting the Developer 2000 tools to SQL Server, you must specify an ODBC data source name as part of the database connection string. When you exit the Developer 2000 application, the OCA connection to the ODBC data source is disconnected.
The syntax for the logon connect string is demonstrated in the following example. In this example, the user logs on to the SQL Server STUDENT_ADMIN account. The name of the SQL Server ODBC data source is STUDENT_DATA:
STUDENT_ADMIN/STUDENT_ADMIN@ODBC:STUDENT_DATA
Using an ODBC driver does not ensure that a Developer 2000 application will work correctly with SQL Server. The application program code must be modified to work with a non-Oracle data source. For example, the column security property is Oracle-specific and does not work with SQL Server.
You must change the key mode that is used to identify each row of data. When using Oracle as the data source, a ROWID is used to identify each row. When using SQL Server, you must work with unique primary key values to ensure unique row values.
The locking mode also must be changed. When using Oracle, Developer 2000 attempts to lock a row of data immediately following any change to that row. When using SQL Server, the locking mode should be set to delayed so that the record is locked only when it is written to the database.
They are many other issues that must be resolved, including the potential for a deadlock situation if multiple inserts on a table access the same page of data in a PL/SQL program block. For more information, see “Transactions, Locking, and Concurrency” earlier in this chapter.