This section discusses various methods for migrating data from an Oracle database to a
SQL Server database.
The simplest method of migrating between Oracle and SQL Server is to develop a series of SQL language scripts that can be applied to each DBMS. These scripts are then run using the query tools for each DBMS, SQL*Plus with Oracle, ISQL/w for SQL Server.
This process was followed with the sample applications that are part of this document. A set of custom scripts was developed for Oracle, and a second set was developed for SQL Server. Each set of scripts needed to be customized for each respective DBMS. The benefit of this approach is that this process is relatively simple. If changes or modifications need to be made, the appropriate file(s) can be easily altered. The worst part of the process is writing the individual DML statements that are used to load the data.
The data loading scripts can become overwhelming, however, when hundreds or thousands of rows of data must be entered into the target DBMS. Customized DML statements that work with Oracle may not work with SQL Server. If changes must be made to the data, it must often occur in both the Oracle and SQL Server scripts.
Consequently, other approaches must be considered. You must determine if you will be migrating data from a currently existing Oracle application, or if you will be developing a new application that may be installed on either Oracle or SQL Server.
If you are migrating from a currently existing Oracle application, your first inclination may be to use the Oracle export utility. This is not a viable solution, as SQL Server has no facility that can translate the uniquely formatted files created by export.
The "Oracle Gateway to SQL Server" can be used as a possible intermediate step when performing the migration from Oracle to SQL Server. This facility allows the Oracle DBMS to connect to SQL Server. You can use Oracle SQL statements to copy data from Oracle tables to SQL Server tables.
Another approach is to develop an ODBC program that can connect simultaneously to both Oracle and SQL Server. This program can be used to read data from Oracle and write the data to the appropriate tables in SQL Server. You may also consider using third-party applications that can connect to each DBMS and transfer data.
If you are developing an application that can be installed on either Oracle or SQL Server, you can use several approaches to load the data. As was explained above, the simplest method is to use SQL scripts with SQL*Plus and ISQL/w.
You can also use the Oracle SQL*Loader and SQL Server bcp (bulk copy) utilities to load data files. Like SQL*Loader, bcp can load a table from an operating-system file. Unlike SQL*Loader, the bcp utility can also transfer data between a table and file in a format that can be specified by the user. The bcp utility is executed from the command line. You must supply the following information for transferring data to and from SQL Server:
In addition, for each column, you can modify the data type, length, and terminator.
Another approach is to require the development of an ODBC program to load the appropriate data into each DBMS. You could create one or more generic data files that contain the table data. The ODBC program should be written in a generic format using the ODBC Extended SQL extensions. This program should be able to connect to both Oracle and SQL Server.
This type of approach has several advantages:
If you have applications that are written using the Oracle Call Interface (OCI), you may want to consider rewriting them using ODBC. The OCI is specific to the Oracle DBMS and cannot be used with SQL Server.
In most cases, you can replace OCI functions with the appropriate ODBC functions, followed by relevant changes to the supporting program code. The remaining non-OCI program code should require minimal modification. The example shows a comparison of the OCI and ODBC statements required for establishing a connection to an Oracle database.
| Oracle Call Interface | Oracle ODBC |
| rcl = olog(&logon_data_area, &host_data_area, user_name, -1, (text*) 0, -1, (text) 0, -1, OCI_LM_DEF); |
rcl = SQLConnect(hdbc1, (SQLCHAR*) odbc_dsn, (SQLSMALLINT) SQL_NTS, (SQLCHAR*) user_name, (SQLSMALLINT) SQL_NTS, (SQLCHAR*) user_password, (SQLSMALLINT) SQL_NTS); |
The table suggests conversions between Oracle OCI function calls and ODBC functions. These suggested conversions are approximate. There may not be an exact match in the conversion process. Your program code may require additional revision to obtain similar functionality.
| OCI function | ODBC function |
| Obindps | SQLBindParameter |
| Obndra | SQLBindParameter |
| Obndrn | SQLBindParameter |
| Obndrv | SQLBindParameter |
| Obreak | SQLCancel |
| Ocan | SQLCancel, SQLFreeStmt |
| Oclose | SQLFreeStmt |
| Ocof | SQLSetConnectOption |
| Ocom | SQLTransact |
| Ocon | SQLSetConnectOption |
| Odefin | SQLBindCol |
| Odefinps | SQLBindCol |
| Odescr | SQLDescribeCol |
| Oerhms | SQLError |
| Oexec | SQLExecute, SQLExecDirect |
| Oexfet | SQLExecute, SQLExecDirect, and SQLFetch |
| Oexn | SQLExecute, SQLExecDirect |
| Ofen | SQLExtendedFetch |
| Ofetch | SQLFetch |
| Oflng | SQLGetData |
| Ogetpi | SQLGetData |
| Olog | SQLConnect |
| Ologof | SQLDisconnect |
| Oopen | SQLExecute, SQLExecDirect |
| Oparse | SQLPrepare |
| Orol | SQLTransact |
Many applications are written using the Oracle Programmatic Interface (Pro*C, Pro*Cobol, and so on). This interface supports the use of ANSI-standard embedded SQL. It also includes nonstandard Oracle programmatic extensions.
Oracle embedded SQL applications can be migrated to SQL Server using the Microsoft Embedded SQL for C development environment. This environment provides adequate but not optimal control over performance and use of SQL Server features compared to an ODBC application.
Some of the Oracle Pro*C features are not supported in Microsoft's ESQL precompiler. If your Oracle application makes extensive use of these features, a rewrite to ODBC is probably a better migration choice. These features include:
If your Oracle application has been developed in Cobol, it can be moved to Embedded SQL for Cobol from Micro Focus. Again, you may run into some of the same limitations in Cobol as with the Microsoft ESQL for C precompiler.
You should convert your Oracle embedded SQL application to the ODBC environment. This migration process is quite easy and offers many advantages. It is important to note that ODBC does not require the use of a precompiler, as does embedded SQL. Consequently, much of the overhead associated with program development is eliminated.
The table shows the approximate relationship between Embedded SQL statements and ODBC functions.
| Embedded SQL statement | ODBC function |
| CONNECT | SQLConnect |
| PREPARE | SQLPrepare |
| EXECUTE | SQLExecute |
| DECLARE CURSOR and OPEN CURSOR | SQLExecute |
| EXECUTE IMMEDIATE | SQLExecDirect |
| DESCRIBE SELECT LIST | SQLNumResultCols, SQLColAttributes, SQLDescribeCol |
| FETCH | SQLFetch |
| SQLCA.SQLERRD[2] | SQLRowCount |
| CLOSE | SQLFreeStmt |
| COMMIT WORK, ROLLBACK WORK | SQLTransact |
| COMMIT WORK RELEASE, ROLLBACK WORK RELEASE |
SQLDisconnect |
| SQLCA, SQLSTATE | SQLError |
| ALTER, CREATE, DROP, GRANT, REVOKE | SQLExecute, SQLExecDirect |
The most significant change in converting embedded SQL programs to ODBC involves the handling of SQL statement errors. The MODE=ORACLE option is often used when developing embedded SQL programs. When this option is used, the SQL Communications Area (SQLCA) is typically used for error handling operations.
The SQLCA structure provides:
In most cases, you should check the value in the sqlca.sqlcode variable following the execution of each SQL statement. If the value is less than zero, an error has occurred. If the value is greater than zero, the requested statement executed with warnings. The Oracle error message text can be retrieved from the sqlca.sqlerrm.sqlerrmc variable.
In ODBC, a function returns a numeric status code that indicates its success or failure following the requested operation. The status codes are defined as string literals, and include SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_ERROR, and others. It is your responsibility to check these return values following each function call.
An associated SQLSTATE value may be obtained by calling the SQLError function. This function returns the SQLSTATE error code, the native error code (specific to the data source), and the error message text.
An application typically calls this function when a previous call to an ODBC function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO. However, any ODBC function can post zero or more errors each time it is called, so an application may call SQLError after every ODBC function call.
Here are examples of error handling for each environment.
| Oracle Pro*C and EMBEDDED SQL | Oracle ODBC |
| EXEC SQL DECLARE CURSOR C1 CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN; EXEC SQL OPEN C1; if (sqlca.sqlcode) != 0 { /* handle error condition, look at sqlca.sqlerrm.sqlerrmc for error description...*/} |
if (SQLExecDirect(hstmtl, (SQLCHAR*)"SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN", SQL_NTS) != SQL_SUCCESS) { /* handle error condition, use SQLError for SQLSTATE details regarding error...*/} |
If you have developed an application using Oracle Developer 2000 and want to use it with SQL Server, you may want to consider converting it to Microsoft Visual Basic. Visual Basic is a powerful development environment that works well with both SQL Server and Oracle. You may also want to consider such development tools as the Microsoft Developer Studio visual development system, 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 a possible intermediate step when performing the migration from Oracle to SQL Server.
This gateway allows the Oracle DBMS 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 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 only locked 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 PL/SQL program block. Refer to the chapter on Locking and Transactions for discussion on these issues.
Just as Oracle offers its WebServer, SQL Server includes the SQL Server Web Assistant, a tool that you can use to generate standard Hypertext Markup Language (HTML) files from SQL Server data. SQL Server Web Assistant is an integrated part of SQL Server beginning with release 6.5.
The SQL Server Web Assistant generates HTML files by using Transact-SQL queries, stored procedures, and extended stored procedures. HTML files, also known as Web pages, can be viewed by using any HTML browser. HTML files are resources for displaying information on the World Wide Web (WWW) and on internal networks.
The architecture of the SQL Server Web Assistant provides improved performance and scalability for noninteractive queries compared to database access solutions provided by Common Gateway Interface (CGI) applications.
With the SQL Server Web Assistant, you can generate an HTML file on a one-time basis or as a regularly scheduled SQL Server task. An HTML file also can be updated by using a trigger whenever relevant data changes. With this task-scheduling flexibility and the power of Transact-SQL, you can create Web pages to serve a variety of purposes.
For example, you can use the SQL Server Web Assistant to:
You can create a Web page with the SQL Server Web Assistant in a few simple steps:
The SQL Server Web Assistant provides an interface for the sp_makewebtask stored procedure. This interface is available only on Intel-based computers. However, you can run the stored procedure that creates the HTML file from any supported platform. For more information, see Microsoft SQL Server What's New in SQL Server 6.5.
The following are recommended reference guides: