4
Microsoft SQL Server and Sybase Adaptive Server Questions

This chapter contains frequently asked questions about using the Oracle Migration Workbench to migrate from a Microsoft SQL Server 6.5, 7.0, 2000, or Sybase Adaptive Server 11, 12 database to an Oracle Server. It contains the following sections:

• Pre-Installation
  
  
• Data Migration

Pre-Installation

This section contains Microsoft SQL Server and Sybase Adaptive Server pre-installation questions.

Does the Migration Workbench support Microsoft SQL Server 6.0? If not, are there any plans to support this version of Microsoft SQL Server?

No. The Migration Workbench does not support Microsoft SQL Server 6.0. The Capture Wizard checks the version of the source database to ensure that it is Microsoft SQL Server 6.5 or Microsoft SQL Server 7.0. There are no plans to support Microsoft SQL Server 6.0.

Can I migrate from Microsoft SQL Server or Sybase Adaptive Server to an Oracle7 database using the Migration Workbench?

Yes. However, the Migration Workbench does not officially support migration to Oracle7. To migrate from Microsoft SQL Server or Sybase Adaptive Server to an Oracle7 database, the tables of the source database must not contain multiple text or binary columns.

LOBs are not available in Oracle7 so BLOB and CLOB datatype mappings do not work in migrations to Oracle7. However, you might be able to use LONG and LONG RAW datatypes.

Before capturing the source database, you must modify the datatype mappings. If the size of the text is less than 2000 characters in Oracle7 or 4000 characters in Oracle8, you should map the TEXT datatype to the VARCHAR2 datatype. Otherwise, if the text is greater than 4000 characters, map the TEXT datatype to the LONG datatype. You should also map the following datatypes to the LONG RAW datatype:

• IMAGE
  
  
• BINARY
  
  
• VARBINARY

It is possible to have only one LONG or LONG RAW column in an Oracle7 table. Therefore, the source table must only contain one of these data types. You must switch temporary tables off in the parser.

What is the meaning of the "Owner name is NULL. Check the MASTER database is correctly recovered" message?

If you receive either of these messages, the master database might not be in sync with the database you are migrating. SQL Server and Sybase have their own master database which stores all of the security information. This security information, including the ownership of objects, is mapped to the individual databases within SQL Server and Sybase. In order to ensure that this security information is not lost, you must migrate the master database.

Why must I define a SQL Server or Sybase System 11 ODBC data source?

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 Microsoft SQL Server or Sybase Adaptive Server database you want to migrate.

Why does a SELECT statement on a temporary table fail when I have migrated a stored procedure to a destination Oracle database?

A SELECT statement on a temporary table (#TABLES) might fail when a SESSID column is added. For example, the following SQL statement does not work in a destination Oracle database:

select SESSID, * from a_table

However, the following SQL statement does work in an Oracle database:

select SESSID, tablea.* from a_table

Can you migrate roles and privileges using the Migration Workbench?

Yes. Every Microsoft SQL Server or Sybase Adaptive Server user is mapped to its associated login and it is the login that is created in the destination Oracle database. The Migration Workbench automatically creates Oracle roles for each Microsoft SQL Server or Sybase Adaptive Server group and then assigns that role to each Microsoft SQL Server or Sybase Adaptive Server login. That is, a user is created in the Oracle Server for the login, a role is created for the group, and the user is assigned the role.

Every privilege that a Microsoft SQL Server or Sybase Adaptive Server user has is also given to its equivalent Oracle user. Consider the following example:

• LoginA is associated with userA in the PUBS database.
  
  
• LoginB is associated with userB in the PUBS database.
  
  
• The userA owns the EMP table and grants userB select permission on that table.

In this example, the Migration Workbench does the following:

• Creates two users: loginA and loginB in the Oracle Server.
  
  
• Assigns loginA ownership to the table EMP.
  
  
• Grants loginB select access on the table.
  
  
• Creates private synonyms for every object in another schema where there is user privilege.

In this example, loginB has a private synonym called EMP. This synonym is really loginA.emp.

How does the Migration Workbench map the Microsoft SQL Server database?

Microsoft SQL Server is one server with many databases, while an Oracle Server is one instance with many tablespaces. Database names are mapped to tablespaces. If you want the schema to have the same name as the Microsoft 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.

How are Microsoft SQL Server and Sybase Adaptive Server user-defined types handled?

The Migration Workbench maps Microsoft SQL Server and Sybase Adaptive Server user-defined types to their base types. The Migration Workbench finds out what the base type is, then maps that to the equivalent destination Oracle database using the data type mappings.

What happens if I receive the following error message: "Failed to load Source Model [INTERSOLV] [IDBC SQL Server] [SQL Server] WARNING! Some characters could not be converted into client's character set. Unconverted bytes were changed to question marks (?)" ?

This error may occurr because the correct ODBC translator has not been chosen when the Sybase Adative Server ODBC driver was configured.

To resolve this issue select the correct ODBC translator:

1. Choose Start > Settings > Control Panel > ODBC Data Sources > System DSN
   
   
2. Double -click Sybase DSN
   
   
3. Choose Advanced
   
   
4. Select the required ODBC translator from the list and begin the capture again.

Data Migration

This section contains Microsoft SQL Server and Sybase Adaptive Server data migration questions.

Does the Migration Workbench support offline data loading in Microsoft SQL Server or Sybase Adaptive Server data migration?

Yes. The Migration Workbench uses Microsoft SQL Server BCP or Sybase Adaptive Server BCP in conjunction with SQL*Loader to provide offline data loading for large tables.

To create the BCP data extraction script and the SQL*Loader control files for all tables:

1. Click the Tables folder in the Oracle Model.
   
   
2. Choose the Object > Generate SQL*Loader Scripts option.

   Note: You can also generate the scripts for a specific table by highlighting that table in the Oracle Model, then following the steps outlined for all tables.

For illustration purposes, this section refers to the Microsoft SQL Server 6.5 plug-in. 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 Microsoft SQL Server 6.5 is script_directory\SQLServer6\.

The Migration Workbench creates a directory in the 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.

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 extraction:

1. Add the server name and password for the server where the Microsoft SQL Server database is located to the bcp_extract.bat file:
   
   

   bcp pubs_ro.dbo.employee out EMPLOYEE.dat -c -t "<ec>" -r "<er>" -Usa -Psa 
   -Sientsrv9
   

The following table describes the syntax of the preceding example:

• Run the bcp_extract.bat file to generate the data files. This extracts the data from the tables into data files called table_name.dat.
  
  
• Move the data files into the script_directory\SQLServer6\1-10-1999_17-58-16\Oracle directory.
  
  
• If the destination Oracle database does not reside on the same system as the Migration Workbench, you should FTP the entire
  script_directory\SQLServer6\1-10-1999_17-58-16\Oracle directory to the system where the destination Oracle database is located.
  
  
• Execute the sql_load_script.bat file to insert the data from the data files into the equivalent Oracle tables.

  Note: The scripted data move does not support BLOB migration. You can remove the \n character if it exists in CLOBs.

How do I run BCP from a client system?

You must generate all of the BCP and SQL*Loader scripts that are required to move the data by doing one of the following:

• Choose Object > Generate SQL*Loader Scripts from within the Migration Workbench.
  
  
• Enter the following lines where database name is the server name where the Microsoft SQL Server database is located:
  
  

  bcp pubs_ro.dbo.pub_info out PUB_INFO.dat -c -t "ec" -r "er" -Usa -Psa 
  -Sdatabase name
  
  

For example, if the Microsoft SQL Server database is running on server ientsrv9, replace -Sdatabase name with -Sientsrv9. This allows BCP to run the client server.

To run BCP from a client system:

1. Create a migration user in the destination Oracle database.
   
   
2. Log into the Migration Workbench as the migration user.
   
   
3. Choose Action>Capture Source Database to run the Capture Wizard and map the database.
   
   
4. From the Oracle Model, choose Object>Generate SQL*Loader Script.

Are there any guidelines for how long it takes to convert high volumes of data from Microsoft SQL Server or Sybase Adaptive Server to an Oracle Server using the Migration Workbench?

No. Performance tests have not been carried out on the data migration process of the Migration Workbench. However, it is known that the setup affects performance. For example, if the Oracle Server and Microsoft SQL Server or Sybase Adaptive Server are not located on the same server, network bandwidth has an affect on performance.

Presently, data migration involves reading the data from the source Microsoft SQL Server or Sybase Adaptive Server database through a JDBC/ODBC bridge and writing that data to an Oracle database through 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.

Data Types

The following questions are about SQL Server and Sybase Adaptive Server datatypes.

How are TIMESTAMP data-types migrated?

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, for example last value +1, whenever it fires.

What is the Microsoft SQL Server or Sybase Adaptive Server DATETIME data type mapped to within an Oracle database?

The Microsoft SQL Server or Sybase Adaptive Server DATETIME data type is mapped to the DATE data type in Oracle. Unlike the DATETIME data type in Microsoft SQL Server or Sybase Adaptive Server, the DATE data type in Oracle does not support milliseconds. This can result in a loss of precision.

How does the Migration Workbench handle Microsoft SQL Server 7.0 VARCHAR data types greater than 4000?

The Migration Workbench converts any column that contains a VARCHAR field with a length greater than 4000, to a CLOB column in an Oracle database. The CLOB column provides a very efficient method of storing big text columns.

Parsing

The following questions are about SQL Server and Sybase Adaptive Server parsing.

What is the equivalent of the Microsoft SQL Server or Sybase Adaptive Server SYSOBJECTS table in an Oracle database?

The ALL_OBJECTS table in an Oracle database contains information on all objects that reside in the database and that exist in the SYSOBJECTS table.

Can I edit a stored procedure, trigger, view, or check constraint in the Source Model?

Yes. You can edit the text for any of these schema objects and then parse them. To parse a stored procedure, highlight it and choose Object>Parse.

How are Microsoft SQL Server and Sybase Adaptive Server EXEC statements parsed?

The Migration Workbench assumes that SQL statements, such as EXEC (exec_string) are dynamic SQL and are sent to an EXEC_SQL PL/SQL stored procedure for execution.

Because the contents of the exec_string string are not parsed, you might need to manually edit this string. Other EXEC statements such as EXEC (exec_string) are converted to PL/SQL procedure calls.

Can I use double brackets (()) in T/SQL statements?

No. You cannot use double brackets (()) in T/SQL statements because the parser does not convert them. The extra brackets are not required. The following is an example of valid and invalid T/SQL statements:

SELECT @TIENE = (SELECT COUNT (*) 
FROM PRE_VEHICULOS)

SELECT @TIENE = ((SELECT COUNT (*) 
FROM PRE_VEHICULOS))

Does the Migration Workbench parser support cast operations?

No. The the Migration Workbench parser does not support cast operations. However, instead of using a CAST statement, you can use a CONVERT statement, for example:

convert (varchar(20), @un_calor_
iva)

cast (@un_valor_iva as varchar(2))

Can I use the LIKE clause in SQL statements?

Yes. You can use LIKE in most instances where UNIX-style regular expressions such as square brackets [] are not used. However, you cannot use the following construct outside a SQL statement:

IF (expression) LIKE (expression)

Can I migrate subqueries in the SELECT clause of Microsoft SQL Server and Sybase Adaptive Server?

No. An Oracle Server does not allow you to completely contain subqueries in the SELECT clause of another query. An Oracle Server has stricter requirements for explicit naming and the location of the subquery than Microsoft SQL Server and Sybase Adaptive Server. For example, Microsoft SQL Server allows the following syntax, but an Oracle Server does not:

select TABLE1.FIELD1, (select count(*) from TABLE2)
from TABLE1
where <some criteria

Instead, an Oracle Server allows the following syntax:

select TABLE1.FIELD1, TABLEALIAS.COUNTALIAS
from TABLE1, (select count(*) as COUNTALIAS from TABLE2) TABLEALIAS
where <some criteria

Does an Oracle Server support full UNIX-style regular expression handling?

No. An Oracle Server does not support full UNIX-style regular expression handling.

Are DUMP TRANSACTION and DROP INDEX SQL commands supported?

No. LOAD and DUMP commands are not supported in stored procedures. The Migration Workbench ignores most Microsoft SQL Server and Sybase Adaptive Server DDL commands and writes a warning to the Log window within the Migration Workbench.

How do I avoid problems with cyclic procedures?

If you place the cyclic procedures that you reference in a package, you can compile the cycle procedure because packages consists of a declaration section and a body section. The declaration and body is similar to the C programming language header .h files and implementation .c files. The following lines are an example of packaging:

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
as

myint INTEGER:=myin;
BEGIN
if (myin<3)
then
myint:=allthree.b(myint+1);
end if;
return myint;

END a;
function b(myin INTEGER)
RETURN INTEGER
as 

myint INTEGER:=myin;
BEGIN
if (myin<3)
then
myint:=allthree.c(myint+1);
end if;
return myint;

END b;
function c(myin INTEGER)
RETURN INTEGER
as 

myint INTEGER:=myin;
BEGIN
if (myin<3)
then
myint:=allthree.a(myint+1);
end if;
return myint;

END c;

Does the Migration Workbench support the conversion of T/SQL stored procedures that return multiple result sets?

The Migration Workbench creates a single cursor variable in an OUT argument in the Oracle Server 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.

To prevent the loss of SELECT statement results, create a separate cursor variable argument for each result set SELECT statement and open each cursor variable using a separate SELECT statement. Alternatively, you can use Oracle packages.

Before you can use Oracle packages to prevent the loss of SELECT statement results, you must comment out all result sets to ensures that the Migration Workbench does not attempt to parse them:

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*/

To implement the work-around using Oracle packages:

1. Create a complete Oracle package in the new destination Oracle database containing a get and put procedure.

   Note: The put procedure is used to store the SQL statement result set in an array. The get procedure is used to retrieve the SQL statement result set from the array and execute it within the front end application. It is called inside the application after calling the stored procedure.

2. Replace all result sets, which you had previously commented out, with reference to the put procedure.
   
   
3. Migrate to the destination Oracle database.

   Note: You must modify the front end application in order to use the get procedure to retrieve the result set.

The following exists after you implement the work around:

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')

Complete Oracle Package

The following lines are an example of an implementation of the result_set package.

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;

How are result sets and dynasets returned to the calling program?

The Migration Workbench parser adds an extra argument of type REF CURSOR for result sets and dynasets. The REF CURSOR type is understood and can be manipulated by both PL/SQL and Oracle JDBC.

ODBC drivers support REF CURSORs. This means that the extra 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 Oracle9i ODBC driver provides this ability also.

This is illustrated in the following examples of a Microsoft SQL Server or Sybase Adaptive Server stored procedure and its equivalent Oracle package and stored procedure as generated by the Migration Workbench parser.

Example 4-1 Microsoft SQL Server and Sybase Adaptive Server Stored Procedure

CREATE PROCEDURE byroyalty 
AS 
select au_id from titleauthor 
GO

Example 4-2 Oracle9i Package and Stored Procedure WEAK REF CURSOR

The following is an example of the Oracle9i package and stored procedure WEAK REF CURSOR as produced by the Migration Workbench.

CREATE OR REPLACE  ACKAGE "OMWB_EMULATION"."GLOBALPKG"  AUTHID 
    CURRENT_USER AS 
 identity  INTEGER; 
 trancount INTEGER; 
 TYPE RCT1 IS REF CURSOR;/*new weak cursor definition added*/ 
END globalPkg; /*Only one package required not one package per ref cursor type*/ 
  
TYPE RCT1 IS REF CURSOR RETURN RT1; END; PROCEDURE byroyalty( RC1 IN OUT
"OMWB_EMULATION"."GLOBALPKG".RCT1) AS 
StoO_selcnt INTEGER; StoO_error INTEGER; StoO_rowcnt INTEGER; StoO_errmsg 
VARCHAR2(255); 
StoO_sqlstatus INTEGER; BEGIN 

OPEN RC1 FOR 
SELECT au_id FROM titleauthor; 

END byroyalty; 

Example 4-3 Oracle9i Package and Stored Procedure STRONG REF CURSOR

The following is an example of the Oracle9i package and stored procedure STRONG REF CURSOR which the customer can use by altering the code manually.

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; BEGIN 

OPEN RC1 FOR 
SELECT au_id FROM titleauthor; 

END byroyalty; 

Example 4-4 Typical ODBC Code Used by Intersolv

The following example illustrates the typical ODBC code used by Intersolv to call the preceding Microsoft SQL Server or Sybase Adaptive Server stored procedure. This code also works for the preceding Oracle9i/Oracle8i package and stored procedure.

You must add error handling to a real application:

SQLPrepare(...,'{call byroyalty()}',...) 

SQLExecute() 
SQLBindCol() 
SQLFetch()

The following table describes the typical ODBC code used by Intersolv to call the stored procedure

SQLPrepare(...,'{call 
byroyalty()}',...)

SQLExecute()executes

SQLBindCol()assigns

SQLFetch()

The following examples illustrate how you call the preceding Microsoft SQL Server or Sybase Adaptive Server 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.

Example 4-5 DAO

The following examples illustrate how to call the Microsoft SQL Server or Sybase Adaptive Server stored procedure using DAO.

Private Sub Command2_Click() 
Dim sSql As String

sSql = "{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

Example 4-6 RDO

The following examples illustrate how to call the Microsoft SQL Server or Sybase Adaptive Server stored procedure using 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.Close 

End Sub

Can I use the Migration Workbench to migrate only stored procedures?

The Migration Workbench works first by capturing the source database. It loads the entire data dictionary of the source database into the Source Model and creates the Oracle Server equivalent called the Oracle Model. After the capture is complete, you can run the Migration Wizard. This forces you to create all users and their tables. After the users and tables are 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:

• In step 2, choose Next to create the users and tables. If you are recreating the users and tables, choose Yes.
  
  
• In step 3, choose No to migrate the table data to an Oracle database.
  
  
• In step 4, move all the schema objects to the Available list except for Packages and Procedures.

How are DDL commands handled?

The parser does not handle some DDL commands in stored procedures, for example the DROP INDEX, CREATE VIEW, DROP VIEW, GRANT, DROP PROCEDURE, and CREATE INDEX commands. You can use dynamic PL/SQL to emulate some of these commands.

Can you use DLL commands in an Oracle stored procedure?

Yes. You can use DLL commands by using the dbms_sql package.

Does the Migration Workbench support bitwise operations?

Yes. The Migration Workbench has limited support for bitwise operations. However, you might have to manually edit the stored procedures. Some PL/SQL functions were written to simulate BITNAND operations.

Can I parse a specific stored procedure?

Yes. You can do this by selecting a specific stored procedure and choosing the Object > Parse option within the Migration Workbench.

How are cross-database procedure calls converted?

Microsoft SQL Server and Sybase Adaptive Server 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:

sa..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 previous statement is converted to the following:

hr.get_sales_employees

In the Oracle Server, the user called SA must own get_sales_employees.

How does the Migration Workbench migrate Microsoft SQL Server outer joins to Oracle outer joins?

The Migration Workbench does not convert full outer joins to an Oracle database.

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 an Oracle database, for example:

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;

How can I convert full outer joins from a Microsoft SQL Server database to an Oracle database?

There are several ways to express a full outer join within an Oracle database. For example, in the following query the predicate a.col1 (+) = b.col1 (+) is similar to the way Oracle notates a full outer join although this predicate is not currently supported in an Oracle database:

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, for example:

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;

How do Microsoft SQL Server outer joins compare to ODBC?

The Microsoft SQL Server outer joins are supported by the Oracle ODBC driver. The following example illustrates how the Migration Workbench migrates Microsoft SQL Server outer joins to ODBC escape sequences. The Oracle ODBC driver release 9.0.1 or later supports ODBC escape sequences.

Example 4-7 Original SQL Server Statement

SELECT A.A,B.B 
FROM A LEFT OUTER JOIN B ON A.A = B.B 
WHERE ((A.C=3));

Example 4-8 Oracle ODBC Statement

SELECT A.A,B.B 
FROM {oj A LEFT OUTER JOIN B ON A.A = B.B } 
WHERE ((A.C=3));

How does the Migration Workbench handle complex case statements?

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 might 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 the Oracle database. The Migration Workbench does not convert commented code.

The following 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;

Global Variables

The following questions are about SQL Server and Sybase Adaptive Server global variables.

Does the Migration Workbench support global variables?

Yes. The Migration Workbench currently supports the following global variables:

• @@ERROR
  
  
• @@FETCH_STATUS
  
  
• @@IDENTITY
  
  
• @@ROWCOUNT
  
  
• @@SQLSTATUS
  
  
• @@TRANCOUNT
  
  
• @@VERSION

How does the Migration Workbench implement the migration of global variables?

The Migration Workbench creates equivalent functions in the destination Oracle database for global variables that are specific to stored procedures, for example the @@ROWCOUNT variable. The equivalent function is then called by the stored procedure.

Currently, the Migration Workbench includes the @@IDENTITY and @@TRANCOUNT variables into the omwb_emulation package. However, you can add additional global variables after migration. For variables such as @@TRANCOUNT and @@IDENTITY, the Migration Workbench does the following:

1. The Migration Workbench creates a new user called omwb_emulation with the password oracle in the destination Oracle database.
   
   
2. Inserts an invoker-rights package into the database within the new user schema.

   Note: Unlike definer-rights routines, invoker-rights routines are not bound to a particular schema. A variety of users can run them. For more information, see the PL/SQL User's Guide and Reference.

3. Executes the database privilege Grant execute to public on the invoker-rights package. This allows any user within the destination Oracle database to query and update the defined variables.

How are IDENTITY columns mapped to an Oracle database?

A NUMBER column with an associated sequence and trigger is created in the destination Oracle database for each IDENTITY column in the Microsoft SQL Server or Sybase Adaptive Server 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 Migration Workbench to emulate the T/SQL @@IDENTITY global variable within the Oracle Model.

How does the Migration Workbench handle the @@SERVERNAME global variable?

The global variable @@SERVERNAME is treated as a normal variable within an Oracle Server. The Oracle Server equivalent is based on the name of the database where you are connected.

How does the Migration Workbench handle the @@SPID global variable?

The global variable @@SPID is treated as a normal variable within the Oracle Server. The Oracle equivalent might be based on the v$sessions table. Note that a user might not have a dedicated server process in the Oracle Server.

How does the Migration Workbench handle the @@TRANCOUNT global variable?

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 the @@TRANCOUNT variable within the SQL Server stored procedure is replaced with a reference to the omwb_emulation.globalPkg.trancount packaged variable within the destination Oracle database. However, if you do not select the Emulate @@TRANCOUNT variable parser option, the @@TRANCOUNT variable is treated as a normal variable.

The following example compares the SQL Server statement with the equivalent Oracle Server statement when you select the Emulated @@TRANCOUNT variable option in the parse option.

SQL Server Statement

IF @@trancount > 100

Oracle Statement

IF omwb_emulation.globalPkg.trancount > 100

Note: You access the emulated variable through the typical user.package.variable format.

The SQL Server statements that affect the value of the @@TRANCOUNT variable, for example BEGIN TRANSACTION and COMMIT TRAN, are translated so 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 value of the global variable @@TRANCOUNT by 1. This statement is translated to achieve identical functional capabilities within the destination Oracle database, for example:

/* Emulating @@TRANCOUNT functionality in Oracle model */ 
omwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount+1; 

If the value of the @@TRANCOUNT variable is 1, the Transact SQL statement COMMIT TRANSACTION commits the work to the database and sets @@TRANCOUNT to 0.

If the value of the @@TRANCOUNT variable is greater than 1, the COMMIT TRANSACTION decrements the value of the @@TRANCOUNT variable only by 1.

This statement is translated to achieve identical functional capabilities within the destination Oracle database:

/* Emulating @@TRANCOUNT functionality in Oracle model */ 
IF omwb_emulation.globalPkg.trancount = 1 THEN 

COMMIT WORK; 

END IF;
IF omwb_emulation.globalPkg.trancount > 0 THEN 

 omwb_emulation.globalPkg.trancount:=omwb_emulation.globalPkg.trancount-1; 

END IF;

How does the Migration Workbench handle the @@TRANSTATE global variable?

The @@TRANSTATE global variable is treated as a normal variable. There is no Oracle Server equivalent. Transactions in an Oracle Server are either complete or they fail. Errors in an Oracle Server are handled through exceptions and SQLCODE.

Triggers and Rules

The following questions are about SQL Server and Sybase Adaptive Server triggers and rules.

Are there any issues with the use of deleted and inserted pseudo columns from Microsoft SQL Server?

The parser in the Microsoft SQL Server 7.0 plug-in incorrectly converts Microsoft SQL Server T-SQL stored procedures that contain the following:

SELECT @one=COL1, @two=COL2 FROM DELETED 

The parser converts stored procedures to the following in PL/SQL:

SELECT :OLD.COL1, :OLD.COL2

INTO one, two FROM DUAL;


SELECT @one=COL1, @two=COL2 FROM DELETED, fred
SELECT COL1, COL2 

INTO one, two FROM fred;
StoO_rowcnt := SQL%ROWCOUNT;

You must manually correct PL/SQL stored procedures that are converted in this way. Rewrite the code as follows:

SELECT :OLD.COL1, :OLD.COL2

 INTO one, two FROM fred;
 StoO_rowcnt := SQL%ROWCOUNT;

How are rules handled?

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.

How are rules with the getDate() function converted?

Rules are converted to equivalent Oracle Server check constraints. The getdate() function is semantically equivalent to SYSDATE in an Oracle Server. However, Oracle Servers do not support the use of SYSDATE in check constraint definitions. Therefore, a database trigger must be created in the Oracle Server to implement rules that use the getdate() function.