| Oracle Migration Workbench FAQ Release 1.3.0.0.0 for Windows NT and Windows 95/98 Part Number Z26938-01 |
|
This FAQ provides information about the Oracle Migration Workbench (Migration Workbench) Release 1.3.0.0.0 for Windows NT and Windows 95/98. Each of these sections also provides specific information about the MS SQL Server, and Sybase Adaptive Server (Sybase), where applicable. Information about MS Access is also available in this FAQ. The new release of the Oracle Migration Workbench (Migration Workbench) for migrating an MS Access databases to an Oracle8 or Oracle8i database is planned to be available in September from the following Web site:
http://technet.oracle.com/tech/migration/
For a list of questions organized by plugin (MS SQL Server, Sybase, and MS Access), refer to the following categories:
The following are the most recently answered questions:
How do I migrate the MS Access Memo columns in tables?
Does the Migration Workbench support multi-character field delimiters?
Why does the Migration Workbench hang when I try to load the source master database from Sybase?
Does the Migration Workbench support Unix?
Can I maintain case-sensitivity of schema object names when migrating?
How do I deal with case-sensitivity queries?
Can I migrate subqueries in the SELECT clause of MS SQL Server and Sybase?
How do I configure a Data Source Name (DSN) using ODBC?
Does Oracle have implicit date conversion from a character string?
Can the Migration Workbench migrate multiple databases?
What is the difference between Oracle Transparent Gateway (OTG) and the Migration Workbench?
Does the Migration Workbench support bitwise operations?
How do I run BCP from a client computer?
Can you use DLL statements in an Oracle stored procedure?
What does the error "Hostdef does not exist" mean?
How does the Migration Workbench handle complex case statements?
How do I avoid problems with cyclic procedures?
Does the Migration Workbench support MS Access 2000 migration to Oracle?
What software do I need to migrate MS Access to Oracle?
Does the Migration Workbench support global variables?
How does the Migration Workbench implement the migration of global variables?
How does the Migration Workbench handle the global variable @@TRANCOUNT?
Yes. You can download the Migration Workbench new release, patches, upgrades, support documentation, and updated FAQ from the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/tech/migration/workbench
There are several options you have available to obtain help regarding resolving issues using the Migration Workbench. You have access to the Migration Workbench documentation. This consists of online help, plugin specific reference guide, Oracle Migration Workbench FAQ.
Choose Help -> Online Support from the Migration Workbench menu to obtain an updated list support facilities.
You can download the documentation from the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/tech/migration/workbench
The Migration Workbench runs on Windows NT. However, you can use it to migrate from your source database to an Oracle8i database, regardless of the platform either database resides. In order to do this successfully, you must have an ODBC connection to your source database and a SQL*Net connection to your Oracle database. However, if the Migration Workbench is remote to the source and destination databases, you should use the offline data loading capability when migrating the data.
No. MS Access 2000 is not supported by the Migration Workbench. However, MS Access 2000 provides a capability to save your database in MS Access 97 format as long as your database does not contain any MS Access 2000 specific features. You can then migrate your MS Access 97 database to Oracle. In order to migrate the database, you need to have DAO 3.5 installed on your system. This is available with MS Access 97. For more information on migrating MS Access to Oracle, see the What software do I need to migrate MS Access to Oracle? topic.
No. The Migration Workbench does not support MS SQL Server 6.0. The Capture wizard checks the version of the source database to ensure that it is MS SQL Server 6.5 or MS SQL Server 7.0. There are no plans to support MS SQL Server 6.0.
You must create a user using Oracle DBA Studio, Oracle Enterprise Manager, or Oracle SQL*Plus in the Oracle database where you want to create the Oracle Migration Workbench Repository. The first time you log in to this user account, you are asked if you would like to create the Oracle Migration Workbench Repository. The user name, password, and service that you enter in the Oracle Migration Workbench Repository Login dialog box define where you want to create the repository. You can create the repository in an Oracle8 or Oracle8i server.
You must drop the user in the Oracle database, then re-create the user in the Oracle database where you wish to store your repository. The first time you login to the newly created user account, you are asked if you would like to create the repository.
These messages can appear when loading the source database into the Migration Workbench or when you are creating the Oracle Model. If you were loading the source database, see the Incomplete Oracle Migration Workbench Repository topic for more information. If you were creating the Oracle Model, see the Incomplete Source Database topic for more information.
If you were loading the source database when you received the error messages, it may be because you have an incomplete repository. You obtain an incomplete repository when the Migration Workbench is unable to create all of the required tables. This is caused when the tablespace where the migration schema resides is created with incorrect or missing options. It may also occur if there are any other problems, such as space limitations, with the tablespace.
You verify a complete repository within the Migration Workbench by ensuring that there are 120 tables listed per the four plugins. Implement the following:
sqlplus <migration users>/<password>@<database service name> select table_name from user_table;
To resolve an incomplete repository within the Migration Workbench:
You should monitor the Oracle Alert log for your database on an ongoing basis. This log file contains all errors generated by your Oracle database and indicates any tablespace problems. You can locate the Oracle Alert log at:
%ORACLE_HOME%\admin\<oracle_sid>\bdump\alert<oracle_sid>.log
If you were creating the Oracle Model when you received the error message, it may be because you have an incomplete source database. This can occur if the SQL Server database you are attempting to migrate was imported from another SQL Server database and the master database was not imported. Therefore, the master databases are out of sync.
The master database stores all the security information for a SQL server. This security information, such as the ownership of objects, is mapped to the individual databases within the SQL server. In order to maintain the security information, you must move the master database also. You can resolve an incomplete source database by importing the correct master database into the correct SQL server.
After you install the Migration Workbench, but before you run it you must set up your configuration file, tnsnames.ora. You can do this manually or through the Net8 Configuration Assistant. Launch the Net8 Configuration Assistant, then follow the instructions outlined in the wizard and the online help.
Click here for general issues about the Migration Workbench General Issues.
Click here for MS SQL Server and Sybase General Issues.
Click here for MS Access General Issues.
No. The Migration Workbench does not support the migration of individual schema objects.
Related Topics
Schema Objects within the MS SQL Server 6.5 Reference Guide
Schema Objects within the MS SQL Server 7.0 Reference Guide
Schema Objects within the Sybase Adaptive Server 11 Reference Guide
Yes. You can rename tablespaces and users in the Oracle Model. Choose Object->Rename from the menu or click the right mouse button, then select Rename. Type in the new name of the tablespace or user.
Oracle stores all schema values in upper-case; therefore, it is not recommended that you maintain case-sensitivity. However, you can preserve case-sensitivity schema object names by using the Migration Workbench. Choose Tools->Options, then click the Create ANSI-compliant names option from the General tab. Selecting this option creates all schema values using quotes. This ensures case-sensitivity of your tables and columns.
Oracle evaluates equality in a case-sensitive manner. This means that you can use uppercase values and uppercase fields for comparisons. You can use UPPER functions to simulate case-insensitivity. However, you need to apply this to every SQL statement. Oracle8i has functional indexes so you can deal with indexed fields.
Yes. You can select the databases you want to migrate through one of the steps in the Capture wizard. Choose Action->Capture Source Database to access the Capture wizard.
If a column is an Oracle reserved word, an underscore (_) is appended when the you create the object within Oracle. You can signal the Migration Workbench to use ANSI names for objects by choosing Tools->Options, then selecting Create ANSI-compliant names from the General page. This means that all object names are surrounded by double quotes when you use the Migration wizard to create them in the destination Oracle database.
If you are using words that are not official T-SQL reserved words, but they have meaning in T-SQL syntax, you must enable the parser to recognize these words. Choose the Allow 'Reserved Words' in table names option from the Parse Options tab within the Stored Procedures. You can enable this for a specific stored procedure or all stored procedures.
Related Topics
Schema Object Names within MS SQL Server 6.5
Schema Object Names within MS SQL Server 7.0
Schema Object Names within Sybase Adaptive Server 11
You can locate error messages within this FAQ. Refer to the following questions for further information:
What does the error "Hostdef does not exist" mean?
What should I do if I encounter the error "JET/DAO Error 3050: Couldn't lock file"?
What is the meaning of error "ORA-01400: cannot insert null"?
What is the meaning of error "ORA-01426: numeric overflow" in the Log window?
Why do I receive the "ORA-02298: parent key not found" error message?
You have been disconnected to your server-side processes. This can occur for several reasons. For example, you may have a problem that is associated with a data move. Locate the last table that was moved before the error occurred. Attempt to migrate the table again. If you disconnect again, report the problem to infomwb@ie.oracle.com.
If you are doing a one time migration to Oracle, use the Migration Workbench. This is because the Migration Workbench migrates all the table data. There is no way of migrating just the differences between several migrations. However, if you plan on having links to other heterogeneous databases, use the Oracle Transparent Gateway.
The Migration Workbench is the latest migration technology offering from Oracle. It is written in Java and has a graphical user interface (GUI) front end that is intuitive and easy to use. It currently supports migrations from MS SQL Server 6.5, MS SQL Server 7.0, Sybase Adaptive Server 11, and MS Access. One of the biggest advantages of the Migration Workbench over the legacy script-based kits is that other migration capabilities can avail of the migration information stored in its repository. For example, the stored procedure converter can access the Oracle Migration Workbench Repository to determine what schema objects were renamed during the migration. In summary, the Migration Workbench is a migration technology tool with a standard look and feel regardless of the database you are migrating. You can download it from the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/tech/migration/workbench
The Oracle Migration Companion CD-ROM is a compilation of all the script-based migration toolkits, white papers, and Migration Technology Initiative (MTI) information produced by DMS up to April 1998. It also contains copies of migration toolkits for several database migrations. These toolkits are script-based. They only have minimal support and no development support.
This error occurs when some of the data in your table does not satisfy the data integrity constraints defined on that table. This implies that the data integrity constraints were defined after some or all of the data was entered into the table.This also occurs if you have an incomplete source database.
No. You must use explicit date conversion (TO_DATE). It is possible with the command:
alter session set alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss' SQLselect sysdate from dual; SYSDATE
You can also set this in the environment or the registry.
END allthree;
Yes. However, migration to Oracle7 is not officially supported by the Migration Workbench. Although Blob and Clob datatype mappings do not work because LOBs were not present in Oracle 7, you may be able to use LONG and LONG RAW. If your tables do not contain multiple text or binary columns, you can migrate your MS SQL Server or Sybase database to Oracle 7. Before capturing the source database, you must modify the data type mappings. If the size of the text is less than 2000 characters in Oracle 7 or 4000 characters in Oracle 8, you should map TEXT to VARCHAR2. Otherwise, if the text is greater than 4000 characters, map TEXT to LONG. You should also modify the following to LONG RAW:
It is possible to have only one LONG or LONG RAW column in an Oracle 7 table. Therefore, your source table must only contain one of these data types. You must switch temporary tables off in the parser.
Related Topics
Temporary Tables General Issues
Duplicate object names are appended with an underscore (_) followed by a number.
Yes. You can edit the text for any of the above schema objects, then parse them. You can parse a stored procedure by highlighting it, then choose Object->Parse.
Related Topics
Trigger and Stored Procedure within MS SQL Server 6.5
Trigger and Stored Procedure within MS SQL Server 7.0
Trigger and Stored Procedure within Sybase Adaptive Server 11
Yes. If you have an object with a space in the name, convert the space to an underscore (_) by putting it in square brackets. The Migration Workbench can then migrate the objects.
Yes. The Migration Workbench supports the use of square brackets in syntax for object names within columns and tables.
No. The date and time precision in MS SQL Server and Sybase is 1/300th of a second. In Oracle, the precision is one second. The Migration Workbench currently migrates DATE columns in MS SQL Server and Sybase to full DATE columns in Oracle, such as YYYY-MM-DD HH24:MI:SS. It is necessary to explicitly move the data in this way to ensure that precision is maintained. The DATE data type in Oracle does not support milliseconds. This can result in a loss of precision. When the end-user application accesses the migrated DATE column in Oracle, you should ensure that it accesses the correct format. You can do this by setting up the init.ora 'nls_date_format' parameter or executing an 'alter session set nls_date_format = '<date_format>' ' command.#
Related Topics
DATETIME Data Types within MS SQL Server 6.5
DATETIME Data Types within MS SQL Server 7.0
DATETIME Data Types within Sybase Adaptive Server 11
It is possible that your master database is not in sync with the database you are migrating. SQL Server and Sybase have their own master database that stores all the security information. This security information, such as the ownership of objects, is mapped to the individual databases within SQL Server or Sybase. In order to ensure that this security information is not lost, you must migrate the master database. However, if the SQL Server database or Sybase database you are attempting to migrate was imported from another SQL Server database or Sybase database, it is possible the master databases are out of sync. To resolve this situation, use the sp_changedbowner stored procedure command in SQL Server 6.5, SQL Server 7.0, or Sybase to assign a valid login for the database user.
There is a known bug with JDK1.1.7 that can cause the Migration Workbench to hang when you are loading the source master database from Sybase. Therefore, you should upgrade to JDK 1.1.8.
After the Migration Workbench has modified your MS Access database to work with the data residing in the Oracle8i Lite database you can still migrate accented characters within the MS Access application. In order for the Migration Workbench to successfully migrate accented characters, you must change the DbCharEncoding setting within the POlite.ini file to NATIVE prior to migrating. This allows you to correctly display the accented characters through an MS Access ODBC link table.
In order for your MSAccess application to correctly retrieve and display accented characters it is necessary to do the following:
In order to migrate from MS Access to Oracle you must have the following installed on the same system as the Migration Workbench:
No. You may receive the error message JET/DAO Error 3110: Couldn't read definitions; no read definitions permission for table 'xxx'." This is because the Migration Workbench does not support the migration of MS Access security settings, such as user name and password. By default, the Migration Workbench uses the name of the MS Access .mdb file as the user name for the destination Oracle user. The password for an Oracle user created in this way is always Oracle. If you are not sure if the MS Access database is secured and you are having difficulty migrating, you can do the following:
No. The Migration Workbench does not permit migration of replica MS Access databases.
Yes. In order to migrate multiple MS Access databases to a single user in Oracle, all of your MS Access databases must have the same name. The reason for this is that the Migration Workbench uses the name of the MS Access .mdb file as the user name for the destination Oracle user. When renaming your MS Access databases, ensure that they reside in different directories.
Having renamed your MS Access databases, you must now use the Capture wizard to add these databases for a simultaneous migration. Once the MS Access databases have been captured, they appear individually in the Source Model with the same name. You can identify each database by viewing its path in the Source Model property sheet. When you view the Oracle Model, you see that there is only one user. This user contains all schema objects from the multiple Source Model databases.
You can have an MS Access application database that contains linked tables to another MS Access database. To migrate multiple MS Access databases containing attached tables to a single Oracle user:
Related Topics
MS Access Architecture within the MS Access Reference Guide
No. Oracle does not support the concept of hyperlinks. Therefore, the Migration Workbench simply brings over the raw contents of the column.
Jet requires a primary key on tables in Oracle in order to support dynasets against those tables. If a remote table does not have a primary key, Jet only opens a non-updateable snapshot on the table. It is possible to define updateable tables in MS Access that do not have a primary key. When these tables are migrated to Oracle, they do not have a primary key and MS Access is unable to update them. If you need the migrated tables to be updateable, you should ensure that either all updateable MS Access tables have primary keys before you migrate or that you define a primary key once the tables are migrated to Oracle. Any updateable tables that do not have primary keys are flagged with a warning in the Log window.
Related Topics
MS Jet Error Messages within the MS Access Reference Guide
When DAO opens an MS Access database, it automatically generates a .ldb file. This error indicates that the .ldb file is read-only and cannot be updated. To overcome this error, you should make the .ldb file writable by altering the properties of the file and then reselect the MS Access database for migration.
Related Topics
MS Jet Error Messages within the MS Access Reference Guide
To configure a Data Source Name (DSN) using ODBC, you must select a MS Access database during the setup of the DSN.
To select a MS Access database:
Related Topics
ODBC Driver Selection within the MS Access Reference Guide
You must define a SQL Server or Sybase System 11 ODBC data source so that the Migration Workbench can capture the data dictionary and extract the data from the SQL Server or Sybase database you want to migrate.
You must define an MS Access ODBC data source so that the Migration Workbench can extract the data from the source database.
You must define an Oracle ODBC data source if you wish to modify your MS Access database so that you can continue using your MS Access forms and reports after your data has been migrated to an Oracle database.
If this message appears, you should reconfigure your MS Access ODBC driver in the Control panel by selecting a valid database.
Click here for General Issues about data types.
Click here for issues about MS SQL Server and Sybase Data Types.
Non-Oracle data types are mapped to Oracle data types that encapsulate similar type characteristics. You can view or edit the data type mappings from one of the following:
Related Topics
Data Types within the SQL Server 6.5 Reference Guide
Data Types within the SQL Server 7 Reference Guide
Data Types within the Sybase Adaptive Server 11 Reference Guide
Once you have created the Oracle Model, you can modify column types from the individual table you created or all tables. You do this from the General tab of the property sheet.
The Migration Workbench does not support the mapping of nchar, nvarchar, ntext, or Memo data types from MS SQL Server, Sybase Adaptive Server, or MS Access to the NCHAR, NVARCHAR2, or NCLOB data types in Oracle. Instead, the default mapping for these data types is to CHAR, VARCHAR2, and CLOB in Oracle because these Oracle data types support Unicode and multi-byte character sets.
The Migration Workbench automatically migrates TIMESTAMP columns by creating a NUMBER column where an UPDATE/INSERT trigger is defined. A SEQUENCE is also defined from where the trigger retrieves the next integer value, such as last value +1, whenever it fires.
The MS SQL Server or Sybase DATETIME data type is mapped to the DATE data type in Oracle. The DATE data type in Oracle does not support milliseconds. This can result in a loss of precision.
The Migration Workbench converts any column that contains a VARCHAR field, with a length greater than 4000, to a CLOB column in Oracle. CLOB columns are a very efficient method of storing big text columns.
Click here for Data Migration General Issues.
Click here for issues about MS SQL Server and Sybase Data Migration.
Click here for issues about MS Access Data Migration.
Yes. This means that you can select a table in the Migration Workbench and migrate the data for that table only. You can do this via the Migrate Table Data menu item in the Object Menu or via the right mouse button. Please note that this menu item is only enabled once you have used the Migration wizard to create the users and tables in the destination Oracle database.
Yes. By default, the Migration wizard first creates the users and their tables, then loads the data, and finally creates all constraints. This avoids problems. It is possible to migrate your database to Oracle in stages. For example, you can first create the users and their tables, then create the constraints, and finally load the data. However, this may cause problems due to referential integrity. Therefore, the default implementation is recommended.
No. The Migration Workbench does not use SQL*Loader control files when loading the data from the source database to the destination Oracle database. However, you can choose the Object->Generate SQL*Loader Scripts option to create the SQL*Loader control files in the %ORACLE_HOME%\Omwb\sqlloader_scripts\<timestamp> directory.
Yes. If you are using the offline data loading method, then choose the Object -> Generate SQL*Loader Scripts option within the Migration Workbench, you can save the file to your system. You can then update the file at any stage.
You can overcome this problem by migrating to an Oracle8i release 8.1.5 database and setting the init<ORACLE_SID>.ora 'compatible' parameter as follows:
compatible=8.1.0.
This error means that a double value in the source database is outside the allowable range for Oracle. In this case, the Migration Workbench migrates all other columns in the row as normal and migrates a null value for the column that contains the double value. The error message in the Log window indicates the table and the row number affected.
Oracle8 or Oracle8i, Release 8.1 or higher allows multi-character field delimiters. However, Oracle8 or Oracle8i, Release 8.0 or lower does not allow multi-character field delimiters. If you attempt to use multi-character field delimiters in this case, you receive an error message. However, if you do not have any carriage returns in your data, you can use a single character delimiter, such as (|).
You need to edit the .ctl file to resemble the following:
load data infile 'TBL_ADDRESS.dat' into table sa.TBL_ADDRESS fields terminated by "|" (ADDR_IDN , BANK_IDN , CO_IDN , COUNTRY_CDE , ADDR_LINE_1 , ADDR_LINE_2 , ADDR_LINE_3 , ADDR_LINE_4 , ADDR_CITY , ADDR_STATE , ADDR_POSTAL_CDE , ADDR_PHONE , ADDR_FAX , LAST_CHANGE_DTE "TO_DATE(substr(:LAST_CHANGE_DTE,1,19),'yyyy-mm-dd HH24:mi:ss')", LAST_CHANGE_BY , TIMESTAMP )
You also need to edit the file as follows:
iinside.dbo.tbl_address out TBL_ADDRESS.dat -c -t "|" -r "|\n" -Usa -Pscssql037
Yes. The Migration Workbench uses MS SQL Server BCP or Sybase BCP in conjunction with SQL*Loader to provide an offline data loading capability for large tables. To create the BCP data extraction script and the SQL*Loader control files for all tables:
For illustration purposes, this section refers to the MS SQL Server 6.5 plugin. All data extraction scripts are stored in the following directory:
<script directory> = %ORACLE_HOME%\Omwb\sqlloader_scripts
The base directory for the SQL*Loader script output for MS SQL Server 6.5 is <script directory>\SQLServer6\.
The Migration Workbench creates a directory under this base directory that represents the date and time at you generated the SQL*Loader scripts. For example, a sub-directory called 1-10-1999_17-58-16 indicates that the scripts were generated at 17:58 p.m. on 1st October, 1999.
A BCP extraction script called bcp_extract.bat is created in the
<script directory>\SQLServer6\1-10-1999_17-58-16 directory.
As part of the Generate SQL*Loader Script command, a sub-directory called Oracle is created in the <script directory>\SQLServer6\1-10-1999_17-58-16 directory. The Oracle directory contains SQL*Loader control files and a SQL*Loader script called sql_load_script.bat. The SQL*Loader control files expect the data files that are created to be located in this directory also. Therefore, you should copy the bcp_extract.bat file into the Oracle directory before you execute it.
To use these scripts to execute the manual data and extraction:
bcp pubs_ro.dbo.employee out EMPLOYEE.dat -c -t "<ec>" -r "<er>" -Usa -Psa -Sientsrv9
You need to run the Generate SQL Scripts within the Migration Workbench to generate all the BCP and SQL*Loader scripts required to move your data. You can also do the following:
bcp pubs_ro.dbo.pub_info out PUB_INFO.dat -c -t "<ec" -r "<er" -Usa -Psa -S<database name>
whereas, -S is the server name where the MS SQL Server database resides.
For example, if your MS SQL Server database is running on server ientsrv9, you would replace -S<database name> with -Sientsrv9.This allows BCP to work client server.
To run BCP from a client computer:
No. Performance tests have not been carried out on the data migration process of the Migration Workbench. However, it is known that your setup affects performance. For example, if Oracle and MS SQL Server or Sybase are not located on the same server, you must take the network bandwidth into account. Presently, data migration involves reading the data from the source MS SQL Server or Sybase database via a JDBC/ODBC bridge and writing that data to Oracle via JDBC. It is a straightforward data pump. However, the Migration Workbench takes advantage of array inserts and 'commit points' to improve performance. You can use the BCP scripts in conjunction with SQL*Loader to provide offline data loading for non-binary data.
In order to continue using your MS Access front end with the data that you have migrated to Oracle, the Migration Workbench must make some modifications to the MS Access database. Modifying the MS Access database involves renaming the local tables. However, during the data move phase of the migration, the Migration Workbench has a reference to the original table names and expects these names to exist in the MS Access database. Therefore, you should modify the MS Access database after you have successfully migrated your data to Oracle.
Related Topic
Modifying your MS Access Database within the MS Access Reference Guide
Yes. The Migration Workbench uses SQL*Loader to provide an offline data loading capability to improve the migration of large tables from MS Access. However, you must manually create the data files from within the MS Access Integrated Development Environment (IDE) as part of this process. This is explained in the steps below.
The base directory for the SQL*Loader script output for MS Access is %ORACLE_HOME%\Omwb\sqlloader_scripts\MSAccess<timestamp>. The default Log directory is %ORACLE_HOME%\Omwb\sqlloader_scripts\. The Migration Workbench creates a directory under this base directory that represents the date and time that you generated the SQL*Loader scripts. For example, a sub-directory called 1-10-1999_17-58-16 indicates that the scripts were generated at 17:58 p.m. on 1st October, 1999.
To manually create the data files from within the MS Access Integrated Development Environment (IDE):
The Migration Workbench has not been tested for NLS support. However, there have been many successful migrations with non-English characters. If you are migrating data or schema object names containing non-English characters using the Migration Workbench, you should check to ensure everything migrated successfully.
For more information about parsing, see the following:
Error Handling with Stored Procedures topic within the Oracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide.
The ALL_OBJECTS table in an Oracle database contains information about all objects that reside in the database. Other references to MS SQL Server data dictionary tables have to be manually converted to the Oracle equivalent.
SQL statements such as EXEC ("exec_string") are assumed to be dynamic SQL and are sent to an EXEC_SQL PL/SQL stored procedure for execution. Since the contents of the string, that is "exec_string", are not parsed, manual intervention may be required. Other EXEC statements such as EXEC (exec_string) are converted to PL/SQL procedure calls.
Yes. You can use LIKE in most instances as long as UNIX-style regular expressions such as square brackets are not used. However, it is not possible for you to use the following construct outside a SQL statement:
IF (expression) LIKE (expression)
No. Oracle does not allow you to completely contain subqueries in the select clause of another query. Oracle has stricter requirements for explicit naming and the location of the subquery. For example, MS SQL Server allows the following syntax, but Oracle does not:
select TABLE1.FIELD1, (select count(*) from TABLE2) from TABLE1 where <some criteria
Instead, Oracle allows the following syntax:
select TABLE1.FIELD1, TABLEALIAS.COUNTALIAS from TABLE1, (select count(*) as COUNTALIAS from TABLE2) TABLEALIAS where <some criteria
No. Oracle does not support full UNIX-style regular expression handling.
No. LOAD and DUMP commands are not supported in stored procedures. Most MS SQL Server and Sybase DDL commands are ignored and a warning is written to the Log window.
If the cyclic procedures you reference can be placed in a package, you can compile them since the package consists of a declaration and a body section. The declaration and body is similar to the C programming language header .h and implementation .c files. An example of packaging is as follows:
function a(myin INTEGER) return integer; function b(myin INTEGER) return integer; function c(myin INTEGER) return integer;
end; create or replace package body allthree as function a(myin INTEGER) RETURN INTEGER asmyint INTEGER:=myin; BEGIN if (myin<3) then myint:=allthree.b(myint+1); end if; return myint;END a; function b(myin INTEGER) RETURN INTEGER asmyint INTEGER:=myin; BEGIN if (myin<3) then myint:=allthree.c(myint+1); end if; return myint;END b; function c(myin INTEGER) RETURN INTEGER asmyint INTEGER:=myin; BEGIN if (myin<3) then myint:=allthree.a(myint+1); end if; return myint;END c;
Click here for Temporary Tables General Issues.
Click here for issues about MS SQL Server and Sybase Temporary Tables.
The Migration Workbench uses the temporary tables feature of Oracle8i by default. If you are migrating to an Oracle8 Server release 8.0.5 database or Oracle 7, you should change the parse options for stored procedures, triggers, and views. For more information about migrating to Oracle 7, see the Can I migrate from MS SQL Server or Sybase to an Oracle7 database using the Migration Workbench? question in this FAQ.
To change the parse options for stored procedures, triggers, and views, deselect the Generate Oracle8i temp tables check box in the Parse Options page of the property sheets for these schema objects. When the parser encounters SQL statements such as CREATE #TABLE in a stored procedure, a temporary table is explicitly created. When the parser encounters SQL statements such as INSERT INTO #TABLE in a trigger or stored procedure, a temporary table is implicitly created. An implicit creation is required when a CREATE TABLE statement cannot be found.
These creation (DDL) statements are associated with stored procedures and triggers and are executed by the Migration wizard prior to the creation of the stored procedures or triggers. Clashes can arise when the parser creates multiple copies of the same DDL statement. The clashes are written to the Log window and you can ignore them.
In order for the database to use the temporary tables feature of Oracle8i, you should set the init<ORACLE_SID>.ora 'compatible' parameter as follows:
compatible=8.1.0.
SELECT statements, failing; temporary tables, issues
SELECT statements for temporary tables (#TABLES) may fail when a SESSID column is added. For example, the following SQL statement
select SESSID, * from a_table
does not work in an Oracle database. However, the following SQL statement does work in Oracle:
select SESSID, tablea.* from a_table
The Migration Workbench creates a single cursor variable in an OUT argument in Oracle and uses that variable for all the converted result set SELECT statements. This causes only the results of the last SELECT statement that was opened to be returned in the cursor variable. The results of all other SELECT statements are lost. There are two known workarounds for this situation. You can create a separate cursor variable argument for each result set SELECT statement and to open each cursor variable using a separate SELECT statement. Otherwise, you can use Oracle packages. The scenario below describes using Oracle packages.
Before you implement the workaround you have:
create procedure test as declare @x varchar(10) select @x = "[Customer]" select @x /*result set*/ select * from ln_display where ptid >1 /*result set with multiple rows*/
Prior to creating the Oracle Model, comment out all the result sets. This ensures that the Migration Workbench does not attempt to parse them.
To implement the workaround using Oracle packages:
The following exists after you implement the workaround:
create procedure test as declare @x varchar(10) select @x = "[Customer]" result_set.put('select '' '|| test.x ||' '' from dual') result_set.put('select * from ln_display where ptid >1')
PACKAGE Result_set IS TYPE varchar2array IS table of varchar2(2000) index by binary_integer; pointer integer :=1; store_counter integer :=1; rset_store varchar2array; procedure put(data_to_save in varchar2); procedure get (data_stored out varchar2); END;
The PACKAGE BODY result_set is as follows:
procedure put(data_to_save in varchar2) is status integer :=0; begin rset_store(store_counter) := data_to_save; store_counter := store_counter + 1; status := 1; exception when others then status := 0; end put; procedure get (data_stored in out varchar2) is status integer :=0; begin if pointer < store_counter then data_stored := rset_store(pointer); pointer := pointer + 1; status := 1; else data_stored := '~'; status := 0; end if; exception when others then status := 0; end get; END;
No. The Migration Workbench currently uses packages and strong REF CURSORS.
The Migration Workbench parser adds an extra argument of type REF CURSOR for result sets and dynasets. This type is understood and can be manipulated by both PL/SQL and Oracle JDBC. Oracle8 release 8.0.5 ODBC drivers support REF CURSORs. This means that the additional argument must be explicitly handled by the application and the client application code must be changed. However, some third-party vendors such as Intersolv supply ODBC drivers for Oracle that support REF CURSORs and can implicitly make use of REF CURSORs for using result sets/dynasets. Therefore, no change is required in the client application code. The Oracle8i ODBC Driver release 8.1.5.4.0 provides this functionality also.
This is illustrated in the following examples of an MS SQL Server or Sybase stored procedure and its equivalent Oracle package and stored procedure as generated by the Migration Workbench parser.
MS SQL Server and Sybase Stored Procedure
CREATE PROCEDURE byroyalty AS select au_id from titleauthor GO
Oracle8iPackage and Stored Procedure
PACKAGE BYROYALTYPkg AS TYPE RT1 IS RECORD (au_id titleauthor.au_id%TYPE );TYPE RCT1 IS REF CURSOR RETURN RT1; END;
PROCEDURE byroyalty( RC1 IN OUT byroyaltyPkg.RCT1) AS StoO_selcnt INTEGER; StoO_error INTEGER; StoO_rowcnt INTEGER; StoO_errmsg VARCHAR2(255); StoO_sqlstatus INTEGER; BEGINOPEN RC1 FOR SELECT au_id FROM titleauthor;END byroyalty;
The following example illustrates the typical ODBC code used by Intersolv to call the above MS SQL Server or Sybase stored procedure. This code also works for the above Oracle8i package and stored procedure. Note that error handling must be added in a real application:
SQLPrepare(...,'{call byroyalty()}',...)SQLExecute() SQLBindCol() SQLFetch()
Comments:
The following examples illustrate how you call the above MS SQL Server or Sybase stored procedure with result sets/dynasets in Visual Basic using DAO and RDO on top of ODBC. This code works for Oracle8i packages and stored procedures if you use an Intersolv ODBC driver or the Oracle8i ODBC driver release 8.1.5.4.0 to understand Oracle REF CURSORs.
DAO
Private Sub Command2_Click() Dim sSql As StringsSql = "{call byroyalty()}" 'In Oracle ODBC driver use refcusor argument to get result set Set rCustomers = dbsServer.OpenRecordset(sSql, dbOpenDynamic) Text4 = rCustomers.Fields(0) theend:End Sub
Please note, this example assumes that a DAO connection has been set up already.
RDO
Private Sub Command1_Click()StrSql = "{call byroyalty}" 'in oracle odbc driver uses refcusor argument to get result set Set Ps = connx1.CreatePreparedStatement("PsTest", StrSql) Set Rs = Ps.OpenResultSet(rdOpenStatic) Text3 = Rs!au_id Rs.CloseEnd Sub
The Migration Workbench works first by capturing the source database. This involves loading the entire data dictionary of the source database into the Source Model and creating its Oracle equivalent called the Oracle Model. Once the capture is complete, you can run the Migration wizard. This forces you to create all users and their tables. Once the users and tables have been created, you can create the stored procedures.
You should capture the entire database. You can then use the Migration wizard to migrate only the stored procedures by doing the following:
The parser does not handle some DDL commands in stored procedures. For example, DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, DROP PROCEDURE, and CREATE INDEX. You can use dynamic PL/SQL to do some of these commands.
Yes. You can use DLL statements by using the dbms_sql package.
Yes. The Migration Workbench has limited support for bitwise operations. However, you must often intervene manually. Some PL/SQL functions were written to simulate BITNAND.
Yes. You can do this via the Parse menu item in the Object menu or via the right mouse button when you have selected a particular stored procedure.
MS SQL Server and Sybase databases are mapped to tablespaces instead of to users. Therefore, procedures in one database that call procedures in another database must be edited after migration. For example, there are two databases called HR and FINANCE, owned by the user SA. There is a procedure called get_sales_employees in the HR database and a procedure called get_quarter_bonuses in the FINANCE database. If get_quarter_bonuses makes a call to get_sales_employees, the syntax is as follows:
hr..get_sales_employees
The Migration Workbench maps individual databases to separate tablespaces and creates both stored procedures (get_sales_employees and get_quarter_bonuses) under the database owner, which is often 'sa'. Therefore, the above statement is converted to the following:
hr.get_sales_employees
In Oracle, this requires that the get_sales_employees is owned by a user called SA.
For additional information, see the Parsing topic.
The Migration Workbench converts a SQL Server inner join to an Oracle join as illustrated in the following code example:
select * from a inner join b on a.col1=b.col2; select * from a , b where a.col1=b.col2;
The Migration Workbench converts left joins and right joins in SQL Server to Oracle as indicated below:
select * from a left join b on a.col1=b.col2; remember the table to the right of left join and add (+) to it's columns. select * from a, b where a.col1=b.col2(+);
select * from a right join b on a.col1=b.col2; remember the table to the left of right join and add (+) to it's columns. select * from a, b where a.col1(+)=b.col2;
The Migration Workbench does not convert full outer joins to Oracle.
There are several ways to express a full outer join within Oracle. For example, in the following query the predicate a.col1 (+) = b.col1 (+) is pseudo-Oracle notation for a full outer join although this predicate is not currently supported in Oracle:
select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2 from a,b where a.col1 (+) = b.col1 (+);
The most efficient way of executing this query is to use a UNION ALL of a left outer join and a right outer join, with an additional predicate, as illustrated below.
select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2 from a,b where a.col1=b.col1(+) union all select a.col2 acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2 from a,b where a.col1(+)=b.col1 and a.col1 is null;
The MS SQL Server outer joins are now supported by the Oracle ODBC driver. The following example illustrates how the Migration Workbench migrates MS SQL Server outer joins to ODBC escape sequences. The Oracle ODBC Driver release 8.1.5.3.0 and later releases support ODBC escape sequences.
Original SQL Server Statement:
SELECT A.A,B.B FROM A LEFT OUTER JOIN B ON A.A = B.B WHERE ((A.C=3));
ODBC Statement:
SELECT A.A,B.B FROM {oj A LEFT OUTER JOIN B ON A.A = B.B } WHERE ((A.C=3));
The Migration Workbench converts case to decode. However, you must convert each condition in the case statement to a function call in decode. The Migration Workbench automatically converts the following syntax:
< <= = <!= !< !, AND, OR, NOT, IS NULL, LIKE
In complex case statements you may want to manually alter the PL/SQL code. To do this, you must first comment out the case statement in the source database procedure, then edit it within Oracle. The Migration Workbench does not convert commented code.
To follow is an example of how to convert case to decode for syntax that the Migration Workbench does not convert automatically:
CASE N0 WHEN 1 THEN n1 ELSE n2 END)from emp becomes-------------------------------SELECT SUM(DECODE(N0, 1, n1 , n2)) INTO GSGetAuftragnr.myvar FROM emp;and select @myvar=sum( CASE WHEN N0 = C0 AND N0 <0 THEN ((N0 - C0) / N0) * 100 ELSE 0 END) from emp becomes -------------------------------SELECT SUM( DECODE(1, DECODE(1, DECODE(greatest(N0,C0),N0,1,0)/*expr !<|= expr*/ , DECODE(N0,0,0,1)/*expr <|!= expr*/ ,0)/*expr AND expr*/ ,( ( N0 - C0 ) / N0 ) * 100,0)) INTO GSGetAuftragnr.myvar FROM emp;
Yes. The Migration Workbench currently provides support for the following global variables:
@@ERROR
@@FETCH_STATUS
@@IDENTITY
@@ROWCOUNT
@@SQLSTATUS
@@TRANCOUNT
@@VERSION
All other global variables are treated as normal variables within the Oracle database. In these cases, the following warning is inserted into the stored procedure:
[SPCONV-ERR (<NN>)]: Parsing [(<NAME>)] ('<STRING>': Global Variable treated as variable
Related Topics
@@Rowcount and @@Error Variables within MS SQL Server 6.5
@@Rowcount and @@Error Variables within MS SQL Server 7.0
@@Rowcount and @@Error Variables within Sybase Adaptive Server 11
Special Global Variables within MS SQL Server 6.5
Special Global Variables within MS SQL Server 7.0
Special Global Variables within Sybase Adaptive Server 11
For stored procedure specific global variables, such as @@ROWCOUNT, the Migration Workbench creates an equivalent function in the Oracle database. The new function is then called by your stored procedure.
Currently, the Migration Workbench includes @@IDENTITY and @@TRANCOUNT into the invoker-rights package. However, you can add additional global variables after migration. For variables such as @@TRANCOUNT and @@IDENTITY, the Migration Workbench does the following:
Grant execute to public on the invoker-rights package. This allows any user within the Oracle database to query and update the defined variables.
A NUMBER column with an associated sequence and trigger is created in the Oracle database for each IDENTITY column in the MS SQL Server or Sybase database. Each time a row is inserted, the trigger queries the sequence for the next value and inserts that value into the IDENTITY column. Additionally, this value is inserted into the omwb_emulation user you created as part of the @@IDENTITY global variable. This allows the T/SQL @@IDENTITY global variable to be emulated within the Oracle Model.
@@SERVERNAME is treated as a normal variable. The Oracle equivalent may be based on the name of the database where you are connected.
@@SPID is treated as a normal variable. The Oracle equivalent may be based on the v$sessions table. Note that a user may not have a dedicated server process in Oracle.
You can set the emulation of the @@TRANCOUNT global variable on or off by choosing the Object -> Parse option within the Migration Workbench. When you have selected the parser option, each reference to @@TRANCOUNT within the SQL Server stored procedure is replaced with a reference to the
omwb_emulation.globalPkg.trancount packaged variable within the Oracle database. However, if you do not selected the Emulate @@TRANCOUNT parser option, the @@TRANCOUNT is treated as a normal variable.
The following example compares the SQL Server statement with the equivalent Oracle statement when you have the Emulated @@TRANCOUNT option selected in the parse option.
SQL Server Statement
IF @@trancount > 100
Oracle Statement
IF omwb_emulation.globalPkg.trancount > 100
SQL Server statements that affect the @@TRANCOUNT value, such as BEGIN TRANSACTION and COMMIT TRAN, are translated so that they can update the omwb_emulation.globalPkg.trancount variable in the same way as within the source TRANSACT SQL database. For example, in a Transact SQL database, the BEGIN TRANSACTION statement increments the global variable @@TRANCOUNT by 1. As you can see, this statement is translated to achieve identical functional capabilities within the Oracle database:
/* Emulating @@TRANCOUNT functionality in Oracle model */ omwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount+1;
If @@TRANCOUNT is 1, the Transact SQL statement COMMIT TRANSACTION commits the work to the database and sets @@TRANCOUNT to 0.
If @@TRANCOUNT is greater than 1, the COMMIT TRANSACTION decrements @@TRANCOUNT only by 1.
As you can see, this statement is translated to achieve identical functional capabilities within the Oracle database:
/* Emulating @@TRANCOUNT functionality in Oracle model */ IF omwb_emulation.globalPkg.trancount = 1 THENCOMMIT WORK;END IF; IF omwb_emulation.globalPkg.trancount > 0 THENomwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount-1;END IF;
@@TRANSTATE is treated as a normal variable. There is no Oracle equivalent. Transactions in Oracle are either complete or they fail. Errors in Oracle are handled via exceptions and SQLCODE. For more information, see the PL/SQL User's Guide and Reference, Release 2 (8.1.6) (Part Number: A77069-01).
The parser in the MS SQL Server 7.0 Plugin incorrectly converts MS SQL Server T-SQL stored procedures that contain
SELECT @one=COL1, @two=COL2 FROM DELETED
to the following in PL/SQL:
SELECT :OLD.COL1, :OLD.COL2INTO one, two FROM DUAL;SELECT @one=COL1, @two=COL2 FROM DELETED, fred SELECT COL1, COL2INTO one, two FROM fred; StoO_rowcnt := SQL%ROWCOUNT;
You must manually correct PL/SQL stored procedures that are converted in this way. You rewrite the code as follows:
SELECT :OLD.COL1, :OLD.COL2INTO one, two FROM fred; StoO_rowcnt := SQL%ROWCOUNT;
For additional information, see the Parsing topic.
If a name resolution cannot be performed by the parser, an asterisk (*) appears in package definitions. You must replace this with the relevant name and type definitions.
For additional information, see the Parsing topic.
Yes. Every MS SQL Server or Sybase user is mapped to its associated login and it is the login that is created in the Oracle database. The Migration Workbench automatically creates Oracle roles for each MS SQL Server or Sybase group and then assigns that role to each MS SQL Server or Sybase login. That is, a user is created in Oracle for the login, a role is created for the group, and the user is assigned the role.
Every privilege that an MS SQL Server or Sybase user has is also given to its equivalent Oracle user. Consider the following example:
The Migration Workbench does the following:
In this example, loginB has a private synonym called EMP. This synonym is really loginA.emp.
Related Topics
Role within the MS SQL Server 6.5 Reference Guide
Role within the MS SQL Server 7.0 Reference Guide
Role within the Sybase Adaptive Server 11 Reference Guide
Privilege within the MS SQL Server 6.5 Reference Guide
Privilege within the MS SQL Server 7.0 Reference Guide
Privilege within the Sybase Adaptive Server 11 Reference Guide
customizing, storage options; tables, customizing options
The storage options for tables and indexes can be modified before running the Migration wizard. In the Oracle Model, select the Tables container and go to the Default Storage Options tab of the Tables property sheet. Make your modifications and click Apply. This sets the options for all tables in the Oracle Model. If you would like to modify the default creation options for a particular table, expand the Tables container in the Navigator Pane, select the table you wish to modify, and make your modifications in the Override Default Creation Options tab of the property sheet for that table.
Yes. Table defaults containing dates and bit-wise operators require manual editing after migration to Oracle.
MS SQL Server is one server with many databases, while Oracle is one instance with many tablespaces. Database names are mapped to tablespaces. If you want the schema to have the same name as your MS SQL Server database, then you can rename the user in the Oracle Model. Optionally, you can rename the user to the same name as the database name.
Both BLOB and CLOB columns are automatically handled by the Oracle ODBC driver. By default, all MS Access Memo fields get mapped to CLOB's in Oracle. You can use the MS Access front end against the Oracle database. Unlike LONG columns from Oracle 8.x onwards you can have any number of LOB columns in a table. You can have either internal LOBs, meaning they are inside the database, or external LOBs, meaning they are in a file on the file system but under the database's transactional control. The advantages of LOBs are that you can replicate them or index them using the context option.
Rules are migrated as table check constraints. This means that every table column that has a rule associated with it has a check constraint in the destination Oracle database. Patterns such as LIKE[a-y] are translated using TRANSLATE.
Rules are converted into equivalent Oracle check constraints. The getdate() function is semantically equivalent to SYSDATE in Oracle. However, Oracle does not support the use of SYSDATE in check constraint definitions. Therefore, a database trigger must be created in Oracle to implement rules that use the getdate() function.
Related Topics
Rules within the MS SQL Server 6.5 Reference Guide
Rules within the MS SQL Server 7.0 Reference Guide
Rules within the Sybase Adaptive Server 11 Reference Guide
The Migration Workbench maps MS SQL Server and Sybase user-defined types to their base types. The Migration Workbench finds out what the base type is, then maps that to the equivalent Oracle database using the datatype mappings.
Related Topics
User-defined Data Types within the MS SQL Server 6.5 Reference Guide
User-defined Data Types within the MS SQL Server 7.0 Reference Guide
User-defined Data Types within the Sybase Adaptive Server 11 Reference Guide
You receive this error if you do not obey the foreign key constraint. When using the Migration Workbench, you should load the data for the parent table before the data for a referenced table in a foreign key relationship. By loading the reference table data first you create orphaned child records. Oracle requires that the column specified in the reference table be either a primary key or unique index. You should make sure that the table has no orphaned child records before issuing an ALTER TABLE ENABLE CONSTRAINT command.
Related Topics
Table Design Considerations within the MS SQL Server 6.5 Reference Guide
Table Design Considerations within the MS SQL Server 7.0 Reference Guide
Table Design Considerations within the Sybase Adaptive Server 11 Reference Guide
You can use JDBC, Oracle ODBC drivers, Oracle OLEDB, and third-party ODBC and OLEDB drivers to access client applications after you have migrated the source database to Oracle. You can download the latest version of the Oracle drivers from the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/software/index.htm
The following documents provide additional information about how to extend your client application:
You can find these documents on the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/tech/migration
The Migration Workbench supports the following:
The Migration Workbench runs on Windows NT 4.0 (Service Pack 5), Windows 95/98, Windows 2000, and Solaris.
All errors are logged to the Log window and the columns of the Log window can be sorted by clicking on the column header. During the creation phase of the migration, the Migration wizard creates three log files that record all successful CREATE and ALTER statements, the equivalent DROP and ALTER statements, and all SQL statements that the Migration Workbench was unable to execute. The location of these log files is governed by the Log File directory setting in the Logging page of the Options dialog. The default location is %ORACLE_HOME%\omwb\log. If the Migration Workbench fails to respond, check the contents of the Error.log file to see if there are any errors recorded. This file is located in %ORACLE_HOME%\Omwb\log\Error.log. The contents of this file can be sent to the Migration Workbench development team at infomwb@ie.oracle.com. You can also generate reports from the Migration Workbench. This provides you with a formatted list of your errors.
You should send any questions by e-mail to infomwb@ie.oracle.com. You can also access the discussion forum for the Migration Workbench on the Oracle Technology Network (OTN) Web site at:
http://technet.oracle.com/support/bboard/discussions.htm?Forum&10
If you migrate a database that is not Year 2000-compliant, the Migration Workbench cannot make the migrated data become Year 2000-compliant. For example, if a two-digit column is used to represent the year in a table, this is migrated to a two-digit column in Oracle. It is not possible to interpret non-DATE type columns to see if you are using them as DATE columns.
|
|
![]() Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|