2
Microsoft SQL Server, Sybase Adaptive Server, and Oracle Compared

This chapter contains information comparing the Microsoft SQL Server and Sybase Adaptive Server database and the Oracle database. It includes the following sections:

Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.

The schema migration topics discussed here include the following:

Schema Object Similarities

There are many similarities between schema objects in Oracle and schema objects in Microsoft SQL Server and Sybase Adaptive Server. However, some schema objects differ between these databases, as shown in the following table:

Table 2-1 Schema Objects in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Oracle	Microsoft SQL Server and Sybase Adaptive Server
Database	Database
Schema	Database and database owner (DBO)
Tablespace	Database
User	User
Role	Group/Role
Table	Table
Temporary tables	Temporary tables
Cluster	N/A
Column-level check constraint	Column-level check constraint
Column default	Column default
Unique key	Unique key or identity property for a column
Primary key	Primary key
Foreign key	Foreign key
Index	Non-unique index
PL/SQL Procedure	Transact-SQL (T-SQL) stored procedure
PL/SQL Function	T-SQL stored procedure
Packages	N/A
AFTER triggers	Triggers
BEFORE triggers	Complex rules
Triggers for each row	N/A
Synonyms	N/A
Sequences	Identity property for a column
Snapshot	N/A
View	View

Schema Object Names

Reserved words differ between Oracle and Microsoft SQL Server and Sybase Adaptive Server. Many Oracle reserved words are valid object or column names in Microsoft SQL Server and Sybase Adaptive Server. For example, DATE is a reserved word in Oracle, but it is not a reserved word in Microsoft SQL Server and Sybase Adaptive Server. Therefore, no column is allowed to have the name DATE in Oracle, but a column can be named DATE in Microsoft SQL Server and Sybase Adaptive Server. Use of reserved words as schema object names makes it impossible to use the same names across databases.

You should choose a schema object name that is unique by case and by at least one other characteristic, and ensure that the object name is not a reserved word from either database.

For a list of reserved words in Oracle, see the Oracle9i SQL Reference, Release 1 (9.0.1).

Table Design Considerations

This section discusses the many table design issues that you need to consider when converting Microsoft SQL Server and Sybase Adaptive Server databases to Oracle. These issues are discussed under the following headings:

Data Types

This section outlines conversion considerations for the following data types:

DATETIME Data Types

The date/time precision in Microsoft SQL Server and Sybase Adaptive Server is 1/300th of a second. Oracle9i has a new data type TIMESTAMP which has a

precision of 1/100000000th of a second. Oracle also has a DATE data type that stores date and time values accurate to one second. The Migration Workbench has a default mapping to the DATE data type.

For applications that require finer date/time precision than seconds, the TIMESTAMP data type should be selected for the datatype mapping of date data types in Microsoft SQL Server and Sybase Adaptive Server. The databases store point-in-time values for DATE and TIME data types.

As an alternative, if an Microsoft SQL Server and Sybase Adaptive Server application uses the DATETIME column to provide unique IDs instead of point-in-time values, replace the DATETIME column with a SEQUENCE in the Oracle schema definition.

In the following examples, the original design does not allow the DATETIME precision to exceed seconds in the Oracle table. This example assumes that the DATETIME column is used to provide unique IDs. If millisecond precision is not required, the table design outlined in the following example is sufficient:

Original Table Design

Microsoft SQL Server and Sybase Adaptive Server:

CREATE TABLE example_table
(datetime_column    datetime        not null,
text_column         text            null,
varchar_column      varchar(10)     null)

Oracle:

CREATE TABLE example_table
(datetime_column    date            not null,
text_column         long            null,
varchar_column      varchar2(10)    null)

The following design allows the value of the sequence to be inserted into the integer_column. This allows you to order the rows in the table beyond the allowed precision of one second for DATE data type fields in Oracle. If you include this column in the Microsoft SQL Server and Sybase Adaptive Server table, you can keep the same table design for the Oracle database.

Revised Table Design

Microsoft SQL Server and Sybase Adaptive Server:

CREATE TABLE example_table
(datetime_column    datetime        not null,
integer_column      int             null,
text_column         text            null,
varchar_column      varchar(10)     null)

Oracle:

CREATE TABLE example_table
(datetime_column    date            not null,
integer_column      number          null,
text_column         long            null,
varchar_column      varchar2(10)    null)

For the Microsoft SQL Server and Sybase Adaptive Server database, the value in the integer_column is always NULL. For Oracle, the value for the field integer_column is updated with the next value of the sequence.

Create the sequence by issuing the following command:

CREATE SEQUENCE datetime_seq

Values generated for this sequence start at 1 and are incremented by 1.

Many applications do not use DATETIME values as UNIQUE IDs, but still require the date/time precision to be higher than secondS. For example, the timestamp of a scientific application may have to be expressed in milliseconds, microseconds, and nanoseconds. The precision of the Microsoft SQL Server and Sybase Adaptive Server DATETIME data type is 1/300th of a second; the precision of the Oracle DATE data type is one second. The Oracle TIMESTAMP data type has a precision to 1/100000000th of a second. However, the precision recorded is dependent on the operating system.

IMAGE and TEXT Data Types (Binary Large Objects)

The physical and logical storage methods for IMAGE and TEXT data differ from Oracle to Microsoft SQL Server and Sybase Adaptive Server. In Microsoft SQL Server and Sybase Adaptive Server, a pointer to the IMAGE or TEXT data is stored with the rows in the table while the IMAGE or TEXT data is stored separately. This arrangement allows multiple columns of IMAGE or TEXT data per table. In Oracle, IMAGE data may be stored in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBS and CLOBS may or may not be stored in the row depending on their size.

If the Microsoft SQL Server and Sybase Adaptive Server TEXT column is such that the data never exceeds 4000 bytes, convert the column to an Oracle VARCHAR2 data type column instead of a CLOB column. An Oracle table can define multiple VARCHAR2 columns. This size of TEXT data is suitable for most applications.

Microsoft SQL Server and Sybase Adaptive Server User-Defined Data Types

This Microsoft SQL Server and Sybase Adaptive Server T-SQL-specific enhancement to SQL allows users to define and name their own data types to supplement the system data types. A user-defined data type can be used as the data type for any column in the database. Defaults and rules (check constraints) can be bound to these user-defined data types, which are applied automatically to the individual columns of these user-defined data types.

While migrating to Oracle PL/SQL, you must determine the base data type for each user-defined data type, to find the equivalent PL/SQL data type.

Note:

User-defined data types make the data definition language code and procedural SQL code less portable across different database servers.

Entity Integrity Constraints

You can define a primary key for a table in Microsoft SQL Server and Sybase Adaptive Server. Primary keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle provides declarative referential integrity. A primary key can be defined as part of a CREATE TABLE or an ALTER TABLE statement. Oracle internally creates a unique index to enforce the integrity.

Referential Integrity Constraints

You can define a foreign key for a table in Microsoft SQL Server and Sybase Adaptive Server. Foreign keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle provides declarative referential integrity. A CREATE TABLE or ALTER TABLE statement can add foreign keys to the table definition. For information about referential integrity constraints, see the Oracle9i Database Concepts, Release 1 (9.0.1).

Unique Key Constraints

You can define a unique key for a table in Microsoft SQL Server and Sybase Adaptive Server. Unique keys can be defined in a CREATE TABLE statement or an ALTER TABLE statement.

Oracle defines unique keys as part of CREATE TABLE or ALTER TABLE statements. Oracle internally creates unique indexes to enforce these constraints.

Unique keys map one-to-one from Microsoft SQL Server and Sybase Adaptive Server to Oracle.

Check Constraints

Check constraints can be defined in a CREATE TABLE statement or an ALTER TABLE statement in Microsoft SQL Server and Sybase Adaptive Server. Multiple check constraints can be defined on a table. A table-level check constraint can reference any column in the constrained table. A column can have only one check constraint. A column-level check constraint can reference only the constrained column. These check constraints support complex regular expressions.

Oracle defines check constraints as part of the CREATE TABLE or ALTER TABLE statements. A check constraint is defined at the TABLE level and not at the COLUMN level. Therefore, it can reference any column in the table. Oracle, however, does not support complex regular expressions.

SQL Server Rule:

create rule phone_rule
as
@phone_number like
"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"

This rule passes all the phone numbers that resemble the following:
(650)506-7000

This rule failes all the phone numbers that resemble the following:

650-506-7000
650-GET-HELP

There are a few ways to implement this INTEGRITY constraint in Oracle:

Simulate the behavior of phone-rule in a check constraint using a combination of SUBSTR, TRANSLATE, and LIKE clauses
Write a trigger and use PL/SQL

Table-level check constraints from Microsoft SQL Server and Sybase Adaptive Server databases map one-to-one with Oracle check constraints. You can implement the column-level check constraints from the Microsoft SQL Server and Sybase Adaptive Server database to Oracle table-level check constraints. While converting the regular expressions, convert all simple regular expressions to check constraints in Oracle. Microsoft SQL Server and Sybase Adaptive Server check constraints with complex regular expressions can be either reworked as check constraints including a combination of simple regular expressions, or you can write Oracle database triggers to achieve the same functionality.

Data Types

This chapter provides detailed descriptions of the differences in data types used by Microsoft SQL Server and Sybase Adaptive Server and Oracle databases. Specifically, this chapter contains the following information:

A table showing the base Microsoft SQL Server and Sybase Adaptive Server data types available and how they are mapped to Oracle data types
Recommendations based on the information listed in the table

Data Types Table

Table 2-2 Data Types in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Description	Oracle	Comments
INTEGER	Four-byte integer, 31 bits, and a sign. May be abbreviated as "INT" (this abbreviation was required prior to version 5).	NUMBER(10)	It is possible to place a table constraint on columns of this type (as an option) to force values between -2^31 and2^31. Or, place appropriate constraints such as: STATE_NO between 1 and 50
SMALLINT	Two-byte integer, 15 bits, and a sign.	NUMBER(6)	It is possible to place a table constraint on columns of this type (optionally) to force values between -2^15 and 2^15. Or, place appropriate constraints such as: STATE_NO between 1 and 50
TINYINT	One byte integer, 8 bits and no sign. Holds whole numbers between 0 and 255.	NUMBER(3)	You may add a check constraint of (x between 0 and 255) where x is column name.
REAL	Floating point number. Storage is four bytes and has a binary precision of 24 bits, a 7-digit precision. Data can range from -3.40E+38 to 3.40E+38. In Sybase the range of values and the actual representation is platform dependent. This can result in incorrect interpretation if data is moved between platforms. REAL numbers are stored in 4 bytes and can represent about 6 decimal digits with reasonable accuracy. Sybase REALs are mapped to the ANSI equivalent in Oracle.	FLOAT	The ANSI data type conversion to Oracle for REAL is FLOAT(63). By default, the Oracle Migration Workbench maps REAL to FLOAT(24) that stores up to 8 significant decimal digits in Oracle. The Oracle NUMBER data type is used to store both fixed and floating-point numbers in a format that is compatible with decimal arithmetic. You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this data type as the Oracle NUMBER type is more precise and portable than REAL. Floating-point numbers can be specified in Oracle in the following format: FLOAT[(b)]. Where [(b)] is the binary precision b and can range from 1 to 126. [(b)] defaults to 126. To check what a particular binary precision is in terms of decimal precision, multiply [(b)] by 0.30103 and round up to the next whole number.
FLOAT	A floating point number. This column has 15-digit precision.	FLOAT	The ANSI data type conversion to Oracle for FLOAT(p) is FLOAT(p). The ANSI data type conversion to Oracle for DOUBLE PRECISION is FLOAT(126). By default, the Oracle Migration Workbench maps FLOAT to FLOAT(53), that stores up to 16 significant decimal digits in Oracle. The Oracle NUMBER data type is used to store both fixed and floating-point numbers in a format compatible with decimal arithmetic.You get different answers when performing operations on this type due to the fact that the Oracle NUMBER type is much more precise and portable than FLOAT, but it does not have the same range. The NUMBER data type data can range from -9.99.99E+125 to 9.99.99E+125 (38 nines followed by 88 zeros). NOTE: If you try to migrate floating point data greater than or equal to 1.0E+126 then Migration Workbench will fail to insert this data in the Oracle database and1 will return an error.This also applies to negative values less than or equal to -1.0E+126.
			Floating-point numbers can be specified in Oracle using FLOAT[(b)], where [(b)] is the binary precision [(b)] and can range from 1 to 126. [(b)] defaults to 126.To check what a particular binary precision is in terms of decimal precision multiply [(b)] by 0.30103 and round up to the next whole number. If they are outside of the range, large floating-point numbers will overflow, and small floating-point numbers will underflow.
BIT	A Boolean 0 or 1 stored as one bit of a byte. Up to 8-bit columns from a table may be stored in a single byte, even if not contiguous. Bit data cannot be NULL, except for Microsoft SQL Server 7.0, where null is allowed by the BIT data type.	NUMBER(1)	In Oracle, a bit is stored in a number(1) (or char). In Oracle, it is possible to store bits in a char or varchar field (packed) and supply PL/SQL functions to set / unset / retrieve / query on them.
CHAR(n)	Fixed-length string of exactly n 8-bit characters, blank padded. Synonym for CHARACTER. 0 < n < 256 for Microsoft SQL Server and Sybase Adaptive Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	CHAR(n)	Pro*C client programs must use mode=ansi to have characters interpreted correctly for string comparison, mode=oracle otherwise.
VARCHAR(n)	Varying-length character string. 0 < n < 256 for Microsoft SQL Server and Sybase Adaptive Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	VARCHAR2(n)
TEXT	Character string of 8-bit bytes allocated in increments of 2k pages. "TEXT" is stored as a linked-list of 2024-byte pages, blank padded. TEXT columns can hold up to (231-1) characters.	CLOB	The CLOB field can hold up to 4GB.
IMAGE	Binary string of 8-bit bytes. Holds up to (231-1) bytes of binary data.	BLOB	The BLOB field can hold up to 4GB.
BINARY(n)	Fixed length binary string of exactly n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server and Sybase Adaptive Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
VARBINARY(n)	Varying length binary string of up to n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server and Sybase Adaptive Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
DATETIME	Date and time are stored as two 4-byte integers. The date portion is represented as a count of the number of days offset from a baseline date (1/1/1900) and is stored in the first integer. Permitted values are legal dates between 1st January, 1753 AD and 31st December, 9999 AD. Permitted values in the time portion are legal times in the range 0 to 25920000. Accuracy is to the nearest 3.33 milliseconds with rounding downward. Columns of type DATETIME have a default value of 1/1/1900.	DATE	The precision of DATE in Oracle and DATETIME in Microsoft SQL Server and Sybase Adaptive Server is different. The DATETIME data type has higher precision than the DATE data type. This may have some implications if the DATETIME column is supposed to be UNIQUE. In Microsoft SQL Server and Sybase Adaptive Server, the column of type DATETIME can contain UNIQUE values because the DATETIME precision in Microsoft SQL Server and Sybase Adaptive Server is to the hundredth of a second. In Oracle, however, these values may not be UNIQUE as the date precision is to the second. You can replace a DATETIME column with two columns, one with data type DATE and another with a sequence, in order to get the UNIQUE combination. It is preferable to store hundredths of seconds in the second column. The Oracle TIMESTAMP data type can also be used. It has a precision of 1/10000000th of a second.
SMALL-DATETIME	Date and time stored as two 2-byte integers. Date ranges from 1/1/1900 to 6/6/2079. Time is the count of the number of minutes since midnight.	DATE	With optional check constraint to validate the smaller range.
MONEY	A monetary value represented as an integer portion and a decimal fraction, and stored as two 4-byte integers. Accuracy is to the nearest 1/10,000. When inputting Data of this type it should be preceded by a dollar sign ($). In the absence of the "$" sign, Microsoft SQL Server and Sybase Adaptive Server create the value as a float. Monetary data values can range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.	NUMBER(19,4)	Microsoft SQL Server and Sybase Adaptive Server input MONEY data types as a numeric data type with a preceding dollar sign ($) as in the following example, select * from table_x where y > $5.00 You must remove the "$" sign from queries. Oracle is more general and works in international environments where the use of the "$" sign cannot be assumed. Support for other currency symbols and ISO standards through NLS is available in Oracle.
NCHAR(n)	Fixed-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Note: Microsoft SQL Server storage size is two times n. The Oracle Migration Workbench maps columns sizes using byte semantics, and the size of Microsoft SQL Server NCHAR data types appear in the Oracle Migration Workbench Source Model with "Size" specifying the number of bytes, as opposed to the number of Unicode characters. Thus, a SQL Server column NCHAR(1000) will appear in the Source Model as NCHAR(2000).	CHAR(n*2)
NVARCHAR(n)	Fixed-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Note: Microsoft SQL Server storage size is two times n. The Oracle Migration Workbench maps columns sizes using byte semantics, and the size of Microsoft SQL Server NCHAR data types appear in the Oracle Migration Workbench Source Model with "Size" specifying the number of bytes, as opposed to the number of Unicode characters. Thus, a SQL Server column NCHAR(1000) will appear in the Source Model as NCHAR(2000).	VARCHAR(n*2)
SMALLMONEY	Same as MONEY above except monetary data values from -214,748.3648 to +214,748.3647, with accuracy to one ten-thousandth of a monetary unit. Storage size is 4 bytes.	NUMBER(10,4)	Since the range is -214,748.3648 to 214,748.364, NUMBER(10,4) suffices for this field.
TIMESTAMP	TIMESTAMP is defined as VARBINARY(8) with NULL allowed. Every time a row containing a TIMESTAMP column is updated or inserted, the TIMESTAMP column is automatically incremented by the system. A TIMESTAMP column may not be updated by users.	NUMBER	You must place triggers on columns of this type to maintain them. In Oracle you can have multiple triggers of the same type without having to integrate them all into one big trigger. You may want to supply triggers to prevent updates of this column to enforce full compatibility.
SYSNAME	VARCHAR(30) in Microsoft SQL Server and Sybase Adaptive Server. NVARCHAR(128) in Microsoft SQL Server 7.0.	VARCHAR2(30) and VARCHAR2(128) respectively.

TEXT and IMAGE data types in Microsoft SQL Server and Sybase Adaptive Server follow the rules listed below:

The column of these data types cannot be indexed.
The column cannot be a primary key.
The column cannot be used in the GROUP BY, ORDER BY, HAVING, and DISTINCT clauses.
IMAGE and TEXT data types can be referred to in the WHERE clause with the LIKE construct.
IMAGE and TEXT data types can also be used with the SUBSTR and LENGTH functions.

In Microsoft SQL Server and Sybase Adaptive Server only columns with variable-length data types can store NULL values. When you create a column that allows NULLs with a fixed-length data type, the column is automatically converted to a system variable-length data type, as illustrated in Table 2-3. These variable-length data types are reserved system data types, and users cannot use them to create columns

Table 2-3 Data Type Conversion for NULL Values

Fixed-Length Data Type	Variable-Length Data Type
CHAR	VARCHAR
NCHAR	NVARCHAR
BINARY	VARBINARY
DATETIME, SMALLDATETIME	DATETIMN
FLOAT	FLOATN
INT, SMALLINT, TINYINT	INTN
DECIMAL	DECIMALN
NUMERIC	NUMERICN
MONEY, SMALLMONEY	MONEYN

Note:

The Oracle Migration Workbench Source Model will display table system data types for each column.

Recommendations

In addition to the data types listed in Table 2-2, users can define their own data types in Microsoft SQL Server and Sybase Adaptive Server databases. These user-defined data types translate to the base data types that are provided by the server. They do not allow users to store additional types of data, but can be useful in implementing standard data types for an entire application.

You can map data types from Microsoft SQL Server and Sybase Adaptive Server to Oracle with the equivalent data types listed in the above table. The Migration Workbench converts user-defined data types to their base type. You can defined how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.

Data Storage Concepts

This section provides a detailed description of the conceptual differences in data storage for the Microsoft SQL Server and Sybase Adaptive Server and Oracle databases.

Specifically, it contains the following information:

A table comparing the data storage concepts of Microsoft SQL Server and Sybase Adaptive Server, and Oracle databases
Recommendations based on the information listed in the table

Data Storage Concepts Table

Table 2-4 Data Storage Concepts in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
Database Devices: A database device is mapped to the specified physical disk files.	Datafiles: One or more datafiles are created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace. The combined storage capacity of a the tablespaces in a database is the total storage capacity of the database. Once created, a datafile cannot change in size. This limitation does not exist in Oracle.
Page: Many pages constitute a database device. Each page contains a certain number of bytes.	Data Block: One data block corresponds to a specific number of bytes, of physical database space, on the disk. The size of the data block can be specified when creating the database. A database uses and allocates free database space in Oracle data blocks.
Extent: Eight pages make one extent. Space is allocated to all the databases in increments of one extent at a time.	Extent: An extent is a specific number of contiguous data blocks, obtained in a single allocation.
N/A	Segments: A segment is a set of extents allocated for a certain logical structure. The extents of a segment may or may not be contiguous on disk, and may or may not span the datafiles.
Segments (corresponds to Oracle Tablespace): A segment is the name given to one or more database devices. Segment names are used in CREATE TABLE and CREATE INDEX constructs to place these objects on specific database devices. Segments can be extended to include additional devices as and when needed by using the SP_EXTENDSEGMENT system procedure. The following segments are created along with the database: System segment Stores the system tables. Log segment Stores the transaction log. Default segment All other database objects are stored on this segment unless specified otherwise. Segments are subsets of database devices.	Tablespace (corresponds to Microsoft SQL Server and Sybase Adaptive Server Segments): A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. A database typically has one system tablespace and one or more user tablespaces. Tablespace Extent: An extent is a specific number of contiguous data blocks within the same tablespace. Tablespace Segments: A segment is a set of extents allocated for a certain logical database object. All the segments assigned to one object must be in the same tablespace. The segments get the extents allocated to them as and when needed. There are four different types of segments as follows: Data segment Each table has a data segment. All of the table's data is stored in the extents of its data segments. The tables in Oracle can be stored as clusters as well. A cluster is a group of two or more tables that are stored together. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.
	Tablespace Segments (Cont): Index segment Each index has an index segment that stores all of its data. Rollback segment One or more rollback segments are created by the DBA for a database to temporarily store "undo" information. This is the information about all the transactions that are not yet committed. This information is used to generate read-consistent database information during database recovery to rollback uncommitted transactions for users.
	Temporary segment Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.
Log Devices: These are logical devices assigned to store the log. The database device to store the logs can be specified while creating the database.	Redo Log Files: Each database has a set of two or more redo log files. All changes made to the database are recorded in the redo log. Redo log files are critical in protecting a database against failures. Oracle allows mirrored redo log files so that two or more copies of these files can be maintained. This protects the redo log files against failure of the hardware the log file reside on.
Database Devices: A database device contains the database objects. A logical device does not necessarily refer to any particular physical disk or file in the file system. The database and logs are stored on database devices. Each database device must be initialized before being used for database storage. Initialization of the database device initializes the device for storage and registers the device with the server. After initialization, the device can be: Allocated to the free space available to a database Allocated to store specific user objects Used to store the transaction log of a database Labeled as default device to create and alter database objects The SP_HELPDEVICES system procedure displays all the devices that are registered with the server. Use the DROP DEVICE DEVICE_NAME command to drop the device. The system administrator (SA) should restart the server after dropping the device. A device can be labeled as a default device so that the new databases need not specify the device at the time of creation. Use the SP_DISKDEFAULT system procedure to label the device as a default device.	N/A
Dump Devices These are logical devices. A database dump is stored on these devices. The DUMP DATABASE command uses the dump device to dump the database.	N/A
N/A	Control Files: Each database has a control file. This file records the physical structure of the database. It contains the following information: database name names and locations of a database's datafiles and redo log files time stamp of database creation It is possible to have mirrored control files. Each time an instance of an Oracle database is started, its control file is used to identify the database, the physical structure of the data, and the redo log files that must be opened for the database operation to proceed. The control file is also used for recovery if necessary. The control files hold information similar to the master database in Microsoft SQL Server and Sybase Adaptive Server.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Database Devices:

A database device is mapped to the specified physical disk files.

Datafiles:

One or more datafiles are created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace. The combined storage capacity of a the tablespaces in a database is the total storage capacity of the database. Once created, a datafile cannot change in size. This limitation does not exist in Oracle.

Page:

Many pages constitute a database device. Each page contains a certain number of bytes.

Data Block:

One data block corresponds to a specific number of bytes, of physical database space, on the disk. The size of the data block can be specified when creating the database. A database uses and allocates free database space in Oracle data blocks.

Extent:

Eight pages make one extent. Space is allocated to all the databases in increments of one extent at a time.

Extent:

An extent is a specific number of contiguous data blocks, obtained in a single allocation.

N/A

Segments:

A segment is a set of extents allocated for a certain logical structure. The extents of a segment may or may not be contiguous on disk, and may or may not span the datafiles.

Segments (corresponds to Oracle Tablespace):

A segment is the name given to one or more database devices. Segment names are used in CREATE TABLE and CREATE INDEX constructs to place these objects on specific database devices. Segments can be extended to include additional devices as and when needed by using the SP_EXTENDSEGMENT system procedure.

The following segments are created along with the database:

System segment
Stores the system tables.
Log segment
Stores the transaction log.
Default segment
All other database objects are stored on this segment unless specified otherwise.

Segments are subsets of database devices.

Tablespace (corresponds to Microsoft SQL Server and Sybase Adaptive Server Segments):

A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. A database typically has one system tablespace and one or more user tablespaces.

Tablespace Extent:

An extent is a specific number of contiguous data blocks within the same tablespace.

Tablespace Segments:

A segment is a set of extents allocated for a certain logical database object. All the segments assigned to one object must be in the same tablespace. The segments get the extents allocated to them as and when needed.

There are four different types of segments as follows:

Data segment
Each table has a data segment. All of the table's data is stored in the extents of its data segments. The tables in Oracle can be stored as clusters as well. A cluster is a group of two or more tables that are stored together. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

Tablespace Segments (Cont):

Index segment
Each index has an index segment that stores all of its data.
Rollback segment
One or more rollback segments are created by the DBA for a database to temporarily store "undo" information. This is the information about all the transactions that are not yet committed. This information is used to generate read-consistent database information during database recovery to rollback uncommitted transactions for users.

Temporary segment
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.

Log Devices:

These are logical devices assigned to store the log. The database device to store the logs can be specified while creating the database.

Redo Log Files:

Each database has a set of two or more redo log files. All changes made to the database are recorded in the redo log. Redo log files are critical in protecting a database against failures. Oracle allows mirrored redo log files so that two or more copies of these files can be maintained. This protects the redo log files against failure of the hardware the log file reside on.

Database Devices:

A database device contains the database objects. A logical device does not necessarily refer to any particular physical disk or file in the file system.

The database and logs are stored on database devices. Each database device must be initialized before being used for database storage. Initialization of the database device initializes the device for storage and registers the device with the server. After initialization, the device can be:

Allocated to the free space available to a database
Allocated to store specific user objects
Used to store the transaction log of a database
Labeled as default device to create and alter database objects

The SP_HELPDEVICES system procedure displays all the devices that are registered with the server. Use the DROP DEVICE DEVICE_NAME command to drop the device. The system administrator (SA) should restart the server after dropping the device.

A device can be labeled as a default device so that the new databases need not specify the device at the time of creation. Use the SP_DISKDEFAULT system procedure to label the device as a default device.

N/A

Dump Devices

These are logical devices. A database dump is stored on these devices. The DUMP DATABASE command uses the dump device to dump the database.

N/A

Control Files:

Each database has a control file. This file records the physical structure of the database. It contains the following information:

database name
names and locations of a database's datafiles and redo log files
time stamp of database creation

It is possible to have mirrored control files. Each time an instance of an Oracle database is started, its control file is used to identify the database, the physical structure of the data, and the redo log files that must be opened for the database operation to proceed. The control file is also used for recovery if necessary. The control files hold information similar to the master database in Microsoft SQL Server and Sybase Adaptive Server.

Recommendations:

The conceptual differences in the storage structures do not affect the conversion process directly. However, the physical storage structures need to be in place before conversion of the database begins.

Oracle, Microsoft SQL Server and Sybase Adaptive Server all have a way to control the physical placement of a database object. In Microsoft SQL Server and Sybase Adaptive Server, you use the ON SEGMENT clause and in Oracle you use the TABLESPACE clause.

An attempt should be made to preserve as much of the storage information as possible when converting from Microsoft SQL Server and Sybase Adaptive Server to Oracle. The decisions that were made when defining the storage of the database objects for Microsoft SQL Server and Sybase Adaptive Server should also apply for Oracle. Especially important are initial object sizes and physical object placement.

Data Manipulation Language

This section uses tables to compare the syntax and description of Data Manipulation Language (DML) elements in the Microsoft SQL Server and Sybase Adaptive Server, and Oracle databases. Each table is followed by a recommendations section based on the information in the tables. The following topics are presented in this section:

Connecting to the Database

The statement illustrated in the following table connects a user to a database.

Table 2-5 Connecting to the Database in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
Syntax: USE database_name	Syntax: CONNECT user_name/password SET role
Description: A default database is assigned to each user. This database is made current when the user logs on to the server. A user executes the USE DATABASE_NAME command to switch to another database.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Syntax:

USE database_name

Syntax:

 CONNECT user_name/password
 SET role

Description:

A default database is assigned to each user. This database is made current when the user logs on to the server. A user executes the USE DATABASE_NAME command to switch to another database.

Recommendations:

This concept of connecting to a database is conceptually different in the Microsoft SQL Server and Sybase Adaptive Server, and Oracle databases. An Microsoft SQL Server and Sybase Adaptive Server user can log on to the server and switch to another database residing on the server, provided the user has privileges to access that database. An Oracle Server controls only one database, so here the concept of a user switching databases on a server does not exist. Instead, in Oracle a user executes the SET ROLE command to change roles or re-issues a CONNECT command using a different user_name.

SELECT Statement

The statement in the following table retrieves rows from one or more tables or views.

Table 2-6 SELECT Statements in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
Syntax: SELECT [ALL \| DISTINCT] {select_list} [INTO [owner.]table] [FROM [owner.]{table \| view}[alias] [HOLDLOCK] [,[owner.]{table \| view }[alias] [HOLDLOCK]]...] [WHERE condition] [GROUP BY [ALL] aggregate_ free_expression [, aggregate_ free_expression]...] [HAVING search_condition] [UNION [ALL] SELECT...] [ORDER BY {[[owner.]{table \| view }.]column \| select_list_ number \| expression} [ASC \| DESC] [,{[[owner.]{table \| view }.]column \| select_list_ number \| expression} [ASC \| DESC]...] [COMPUTE row_ aggregate(column) [,row_aggregate(column)...] [BY column [, column...]]] [FOR BROWSE] The individual element in the select list is as follows: [alias = ] {* \| [owner.]{table \| view}.* \| SELECT ... \| {[owner.]table.column \| constant_literal \| expression} [alias]}	Syntax: SELECT [ALL \| DISTINCT] {select_list} FROM [user.]{table \| view } [@dblink] [alias] [, [user.] {table \| view3} [@dblink] [alias]... [WHERE condition] [CONNECT BY condition [START WITH condition]] [GROUP BY aggregate_free_ expression [,aggregate_free_ expression]...] [HAVING search_ condition] [ {UNION [ALL] \| INTERSECT \| MINUS} SELECT ...] [ORDER BY {expression \| position} [ASC \| DESC]...] [FOR UPDATE [OF [[user.]{table \| view}.]column [,[[user.]{table \| view}.]column... ] [noWAIT] ] The individual element in the select list is as follows: { * \| [owner.]{table \| view \| snapshot \| synonym}.* \| {[owner.]table.column \| constant_literal \| expression } alias]}
Description: DISTINCT eliminates the duplicate rows. The INTO clause and the items that follow it in the command syntax are optional, because Microsoft SQL Server and Sybase Adaptive Server allow SELECT statements without FROM clauses as can be seen in the following example: SELECT getdate() SELECT...INTO allows you to insert the results of the SELECT statement into a table. SELECT_LIST can contain a SELECT statement in the place of a column specification as follows: SELECT d.empno, d.deptname, empname = (SELECT ename FROM emp WHERE enum = d.empno) FROM dept d WHERE deptid = 10 The above example also shows the format for the column alias. ALIAS = selected_column COMPUTE attaches computed values at the end of the query. These are called row_aggregates. Outer joins are implemented as follows: WHERE tab1.col1 *= tab2.col1;	Description: DISTINCT eliminates the duplicate rows. The INSERT INTO <table> SELECT FROM.... construct allows you to insert the results of the SELECT statement into a table. COLUMN ALIAS is defined by putting the alias directly after the selected COLUMN. If you use TABLE ALIAS, the TABLE must always be referenced using the ALIAS. You can also retrieve data from SYNONYMS. EXPRESSION could be a column name, a literal, a mathematical computation, a function, several functions combined, or one of several PSEUDO-COLUMNS. Outer joins are implemented as follows: WHERE tab1.col1 = tab2.col1 (+); Where all values from TAB1 are returned even if TAB2 does not have a match or WHERE tab1.col1 (+) = tab2.col1; where all values from TAB2 are returned even if TAB1 does not have a match. If a GROUP BY clause is used, all non-aggregate select columns must be in a GROUP BY clause. The FOR UPDATE clause locks the rows selected by the query. Other users cannot lock these row until you end the transaction. This clause is not a direct equivalent of the FOR BROWSE mode in Microsoft SQL Server and Sybase Adaptive Server.
where all values from TAB1 are returned even if TAB2 does not have a match or WHERE tab1.col1 =* tab2.col1; where all values from TAB2 are returned even if TAB1 does not have a match. If a GROUP BY clause is used, all non-aggregate select columns are needed. FOR BROWSE keywords are used to get into browse mode. This mode supports the ability to perform updates while viewing data in an OLTP environment. It is used in front-end applications using DB-Library and a host programming language. Data consistency is maintained using the TIMESTAMP field in a multi-user environment. The selected rows are not locked; other users can view the same rows during the transaction. A user can update a row if the TIMESTAMP for the row is unchanged since the time of selection.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Syntax:

SELECT [ALL | DISTINCT] 
{select_list}
 [INTO [owner.]table]
 [FROM [owner.]{table | 
view}[alias] [HOLDLOCK]
 [,[owner.]{table | view 
}[alias]
 [HOLDLOCK]]...]
 [WHERE condition]
 [GROUP BY [ALL] aggregate_
free_expression [, aggregate_
free_expression]...]
 [HAVING search_condition]
 [UNION [ALL] SELECT...]
 [ORDER BY {[[owner.]{table | 
view }.]column | select_list_
number | expression}
 [ASC | DESC]
 [,{[[owner.]{table | view 
}.]column | select_list_
number | expression}
 [ASC | DESC]...]
 [COMPUTE row_
aggregate(column) 
 [,row_aggregate(column)...]
 [BY column [, column...]]]
 [FOR BROWSE]
 The individual element in 
the select list is as 
follows:  
 [alias = ]
 {* | [owner.]{table | 
view}.* | SELECT ... | 
{[owner.]table.column | 
constant_literal | 
expression}
 [alias]}

Syntax:

SELECT [ALL | DISTINCT] {select_list}
FROM [user.]{table | view } [@dblink] 
[alias]
[, [user.] {table | view3} [@dblink] 
[alias]...
                [WHERE condition]
 [CONNECT BY condition [START WITH 
condition]]
         [GROUP BY aggregate_free_
expression
                 [,aggregate_free_
expression]...]
                   [HAVING search_
condition]
 [ {UNION [ALL] | INTERSECT | MINUS} 
SELECT ...]
 [ORDER BY {expression | position} [ASC 
| DESC]...]
 [FOR UPDATE [OF [[user.]{table | 
view}.]column
              [,[[user.]{table | 
view}.]column... ]
                [noWAIT] ]
The individual element in the select 
list is as follows:  
{ * | [owner.]{table | view | snapshot | 
synonym}.* | {[owner.]table.column | 
constant_literal | expression }
alias]}

Description:

DISTINCT eliminates the duplicate rows.

The INTO clause and the items that follow it in the command syntax are optional, because Microsoft SQL Server and Sybase Adaptive Server allow SELECT statements without FROM clauses as can be seen in the following example:

SELECT getdate()

SELECT...INTO allows you to insert the results of the SELECT statement into a table.

SELECT_LIST can contain a SELECT statement in the place of a column specification as follows:

SELECT d.empno, d.deptname, 
empname = (SELECT ename FROM emp
           WHERE enum = d.empno)
FROM dept d
WHERE deptid = 10

The above example also shows the format for the column alias.

  ALIAS = selected_column

COMPUTE attaches computed values at the end of the query. These are called row_aggregates.

Outer joins are implemented as follows:

   WHERE tab1.col1 *= tab2.col1;

Description:

DISTINCT eliminates the duplicate rows.

The INSERT INTO <table> SELECT FROM.... construct allows you to insert the results of the SELECT statement into a table.

COLUMN ALIAS is defined by putting the alias directly after the selected COLUMN.

If you use TABLE ALIAS, the TABLE must always be referenced using the ALIAS.

You can also retrieve data from SYNONYMS.

EXPRESSION could be a column name, a literal, a mathematical computation, a function, several functions combined, or one of several PSEUDO-COLUMNS.

Outer joins are implemented as follows:

WHERE tab1.col1 = tab2.col1 (+);

Where all values from TAB1 are returned even if TAB2 does not have a match or

WHERE tab1.col1 (+) = tab2.col1;

where all values from TAB2 are returned even if TAB1 does not have a match.

If a GROUP BY clause is used, all non-aggregate select columns must be in a GROUP BY clause.

The FOR UPDATE clause locks the rows selected by the query. Other users cannot lock these row until you end the transaction. This clause is not a direct equivalent of the FOR BROWSE mode in Microsoft SQL Server and Sybase Adaptive Server.

where all values from TAB1 are returned even if TAB2 does not have a match or

   WHERE tab1.col1 =* tab2.col1;

where all values from TAB2 are returned even if TAB1 does not have a match.

If a GROUP BY clause is used, all non-aggregate select columns are needed.

FOR BROWSE keywords are used to get into browse mode. This mode supports the ability to perform updates while viewing data in an OLTP environment. It is used in front-end applications using DB-Library and a host programming language. Data consistency is maintained using the TIMESTAMP field in a multi-user environment. The selected rows are not locked; other users can view the same rows during the transaction. A user can update a row if the TIMESTAMP for the row is unchanged since the time of selection.

SELECT Statements without FROM Clauses:

Microsoft SQL Server and Sybase Adaptive Server support SELECT statements that do not have a FROM clause. This can be seen in the following example

SELECT getdate()

Oracle does not support SELECTs without FROM clauses. However, Oracle provides the DUAL table which always contains one row. Use the DUAL table to convert constructs such as the one above.

Translate the above query to:

SELECT sysdate FROM dual;

SELECT INTO Statement:

The Microsoft SQL Server and Sybase Adaptive Server SELECT INTO statement can insert rows into a table. This construct, which is part SELECT and part INSERT, is not supported by ANSI. Replace these statements with INSERT...SELECT statements in Oracle.

If the Microsoft SQL Server and Sybase Adaptive Server construct is similar to the following:

SELECT col1, col2, col3 
INTO target_table
FROM source_table
WHERE where_clause

you should convert it to the following for Oracle:

INSERT into target_table
SELECT col1, col2, col3
FROM source_table
WHERE where_clause

Subqueries in Place of Columns:

In Microsoft SQL Server and Sybase Adaptive Server, a SELECT statement may appear anywhere that a column specification appears. Oracle does not support this non-ANSI extension to ANSI SQL. Change the subquery in the SELECT list either by using a DECODE statement or by dividing the query into two different queries.

Use the following sales table as a basis for the examples below:

Year	Quantity	Amount
1993	1	1.3
1993	2	1.4
1993	3	3
1993	4	2.3

Microsoft SQL Server and Sybase Adaptive Server:

If you want to select the year, q1 amount, q2 amount, q3 amount, and q4 as a row, Microsoft SQL Server and Sybase Adaptive Server accept the following query:

SELECT distinct year,
 q1 = (SELECT amt FROM sales 
       WHERE qtr=1 AND year = s.year),
 q2 = (SELECT amt FROM sales 
       WHERE qtr=2 AND year = s.year),
 q3 = (SELECT amt FROM sales 
       WHERE qtr=3 AND year = s.year), 
 q4 = (SELECT amt FROM sales
       WHERE qtr=4 AND year = s.year)
FROM sales s

Oracle:

In this example, replace the SELECT statements with DECODE so that the query functions as normal. The DECODE function is much faster than Microsoft SQL Server and Sybase Adaptive Server subqueries. Translate the above query to the following for Oracle:

SELECT year, 
DECODE( qtr, 1, amt, 0 ) q1,
DECODE( qtr, 2, amt, 0 ) q2,
DECODE( qtr, 3, amt, 0 ) q3,
DECODE( qtr, 4, amt, 0 ) q4
FROM sales s;

If you cannot convert the query using the above method, create views and base the query on the views rather than on the original tables.

For example, consider the following query in Microsoft SQL Server and Sybase Adaptive Server:

SELECT name,
sumlength = (SELECT sum(length) FROM syscolumns WHERE id = t.id),
count_indexes = (SELECT count(*) FROM sysindexes WHERE id = t.id)
FROM sysobjects t

This query returns the sum of the lengths of the columns of a table and the number of indexes on that table. This is best handled in Oracle by using some views.

Convert this to the following in Oracle:

CREATE view V1 ( sumlength, oid ) as 
SELECT sum(length), id FROM syscolumns
GROUP BY  id

CREATE view V2 ( count_indexes, oid ) AS 
SELECT count(*), id FROM sysindexes
GROUP BY  id

SELECT name, sumlength, count_indexes 
FROM sysobjects t, v1, v2
WHERE t.id = v1.oid
AND t.id = v2.oid

Comparing Subqueries to Subqueries:

Microsoft SQL Server and Sybase Adaptive Server also allow a SELECT statement in the WHERE clause. For example, consider the following statement from Microsoft SQL Server and Sybase Adaptive Server:

SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 100
  AND(SELECT security_code 
       FROM employee_security 
       WHERE empno = emp.empno) = 
      (SELECT security_code 
       FROM security_master 
       WHERE sec_level = dept.sec_level)

Convert this to the ANSI-standard statement below for Oracle:

SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 100
  AND EXISTS (SELECT security_code
              FROM employee_security es
              WHERE es.empno = emp.empno
                AND es.security_code =
                    (SELECT security_code 
                     FROM security_master 
                     WHERE sec_level =
                           dept.sec_level));

Column Aliases:

Convert column aliases from the following Microsoft SQL Server and Sybase Adaptive Server syntax:

SELECT employees=col1 FROM tab1e

to the following Oracle syntax:

SELECT col1 employees FROM tab1e

Note:

Microsoft SQL Server and Sybase Adaptive Server also support Oracle-style column aliases.

Table Aliases:

Remove table aliases (also known as correlation names) unless they are used everywhere.

Compute:

Replace the COMPUTE clause with another SELECT. Attach the two sets of results using the UNION clause.

Outer JOIN Syntax:

Convert the outer JOIN syntax from the Microsoft SQL Server and Sybase Adaptive Server syntax to the Oracle syntax.

In addition to these, there are many implications due to the differences in the implementation of the special clauses such as GROUP BY, functions, joins. These are discussed later in this chapter.

SELECT with GROUP BY Statement

Table 2-7 compares the SELECT with GROUP BY statement in Oracle to the same statement in Microsoft SQL Server and Sybase Adaptive Server.

Table 2-7 SELECT with GROUP BY Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server/Server	Oracle
Syntax: See the SELECT Statement section.	Syntax: See the SELECT Statement section.
Description: Non-aggregate SELECT columns must be in a GROUP BY clause.	Description: All non-aggregate SELECT columns must be in a GROUP BY clause.

Microsoft SQL Server/Server

Oracle

Syntax:

See the SELECT Statement section.

Syntax:

See the SELECT Statement section.

Description:

Non-aggregate SELECT columns must be in a GROUP BY clause.

Description:

All non-aggregate SELECT columns must be in a GROUP BY clause.

INSERT Statement

The statements illustrated in the following table add one or more rows to the table or view.

Table 2-8 INSERT Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
Syntax: INSERT [INTO] [[database.]owner.] {table \| view}[(column [, column]...)]{VALUES (expression [,expression]...) \| query}	Syntax: INSERT INTO [user.]{table \| view}[@dblink][(column [, column]...)]{VALUES (expression [, expression]...) \| query...};
Description: INTO is optional. Inserts are allowed in a view provided only one of the base tables is undergoing change.	Description: INTO is required. Inserts can only be done on single table views.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Syntax:

 INSERT [INTO] 
[[database.]owner.] {table | 
view}[(column [, 
column]...)]{VALUES 
(expression [,expression]...) 
| query}

Syntax:

INSERT INTO [user.]{table | 
view}[@dblink][(column [, 
column]...)]{VALUES (expression [, 
expression]...) | query...};

Description:

INTO is optional.

Inserts are allowed in a view provided only one of the base tables is undergoing change.

Description:

INTO is required.

Inserts can only be done on single table views.

Recommendations:

INSERT statements in Microsoft SQL Server and Sybase Adaptive Server must be changed to include an INTO clause if it is not specified in the original statement.

The values supplied in the VALUES clause in either database may contain functions. The Microsoft SQL Server-specific functions must be replaced with the equivalent Oracle constructs.

Note:

Oracle lets you create functions that directly match most Microsoft SQL Server and Sybase Adaptive Server functions.

Convert inserts that are inserting into multi-table views in Microsoft SQL Server and Sybase Adaptive Server to insert directly into the underlying tables in Oracle.

UPDATE Statement

The statement illustrated in the following table updates the data in a table or the data in a table referenced by a view.

Table 2-9 UPDATE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server	Oracle
Syntax: UPDATE [[database.]owner.] {table \| view} SET [[[database.]owner.] {table. \| view.}] column = expression \| NULL \| (select_statement) [, column = expression \| NULL \| (select_statement)]... [FROM [[database.]owner.]table \| view [, [[database.]owner.]table \| view]... [WHERE condition]	Syntax: UPDATE [user.]{table \| view} [@dblink] SET [[ user.] {table. \| view.}] { column = expression \| NULL \| (select_ statement) [, column = expression \| NULL \| (select_statement)...] \| (column [, column]...) = (select_statement)} [WHERE {condition \| CURRENT OF cursor}]
Description: The FROM clause is used to get the data from one or more tables into the table that is being updated or to qualify the rows that are being updated. Updates through multi-table views can modify only columns in one of the underlying tables.	Description: A single subquery may be used to update a set of columns. This subquery must select the same number of columns (with compatible data types) as are used in the list of columns in the SET clause. The CURRENT OF cursor clause causes the UPDATE statement to effect only the single row currently in the cursor as a result of the last FETCH. The cursor SELECT statement must have included in the FOR UPDATE clause. Updates can only be done on single table views.

Microsoft SQL Server

Oracle

Syntax:

UPDATE [[database.]owner.] {table | 
view}
SET [[[database.]owner.] {table. | 
view.}]
column = expression | NULL | 
(select_statement)
[, column = expression | NULL | 
(select_statement)]...
[FROM [[database.]owner.]table | 
view
[, [[database.]owner.]table | 
view]...
[WHERE condition]

Syntax:

UPDATE [user.]{table | view} [@dblink]
SET [[ user.] {table. | view.}]
{ column = expression | NULL | (select_
statement) 
[, column = expression | NULL |
(select_statement)...] | 
(column [, column]...) = (select_statement)}
[WHERE {condition | CURRENT OF cursor}]

Description:

The FROM clause is used to get the data from one or more tables into the table that is being updated or to qualify the rows that are being updated.

Updates through multi-table views can modify only columns in one of the underlying tables.

Description:

A single subquery may be used to update a set of columns. This subquery must select the same number of columns (with compatible data types) as are used in the list of columns in the SET clause.

The CURRENT OF cursor clause causes the UPDATE statement to effect only the single row currently in the cursor as a result of the last FETCH. The cursor SELECT statement must have included in the FOR UPDATE clause.

Updates can only be done on single table views.

Recommendations:

There are two ways to convert UPDATE statements with FROM clauses as indicated below.

Method 1 - Convert UPDATE statements with FROM clauses:

Use the subquery in the SET clause if columns are being updated to values coming from a different table.

Convert the following in Microsoft SQL Server and Sybase Adaptive Server:

update titles
SET pub_id = publishers.pub_id
FROM titles, publishers
WHERE titles.title LIKE 'C%' 
AND publishers.pub_name = 'new age'

to the following in Oracle:

UPDATE titles

SET pub_id = 
( SELECT a.pub_id
  FROM publishers a
  WHERE publishers.pub_name = 'new age'
)
WHERE titles.title like 'C%'

Method 2 - Convert UPDATE statements with FROM clauses:

Use the subquery in the WHERE clause for all other UPDATE...FROM statements.

Convert the following in Microsoft SQL Server and Sybase Adaptive Server:

UPDATE shipping_parts
SET qty = 0
FROM shipping_parts sp, suppliers s
WHERE sp.supplier_num = s.supplier_num
  AND s.location = "USA"

to the following in Oracle:

UPDATE shipping_parts
SET qty = 0
WHERE supplier_num IN (
SELECT supplier_num 
FROM suppliers WHERE location = 'USA')

DELETE Statement

The statement illustrated in the following table removes rows from tables and rows from tables referenced in views.

Table 2-10 DELETE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
Syntax: DELETE [FROM] [[database.]owner.]{table \| view} [FROM [[database.]owner.]{table \| view} [, [[database.]owner.]{table \| view}]...] [WHERE where_clause]	Syntax: DELETE [FROM] [user.]{table \| view} [@dblink] [alias] [WHERE where_clause]
Description: The first FROM in DELETE FROM is optional. The second FROM clause is an Microsoft SQL Server and Sybase Adaptive Server extension that allows the user to make deletions based on the data in other tables. A subquery in the WHERE clause serves the same purpose. Deletes can only be performed through single table views.	Description: FROM is optional. ALIAS can be specified for the table name as a correlation name, which can be used in the condition. Deletes can only be performed through single table views

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Syntax:

DELETE [FROM] 
[[database.]owner.]{table | 
view}
[FROM 
[[database.]owner.]{table | 
view}
[, [[database.]owner.]{table 
| view}]...]
[WHERE where_clause]

Syntax:

DELETE [FROM] [user.]{table | view} 
[@dblink]
[alias]

[WHERE where_clause]

Description:

The first FROM in DELETE FROM is optional.

The second FROM clause is an Microsoft SQL Server and Sybase Adaptive Server extension that allows the user to make deletions based on the data in other tables. A subquery in the WHERE clause serves the same purpose.

Deletes can only be performed through single table views.

Description:

FROM is optional.

ALIAS can be specified for the table name as a correlation name, which can be used in the condition.

Deletes can only be performed through single table views

Remove Second FROM Clause:

Remove the second FROM clause from the DELETE statements.

Convert the following Microsoft SQL Server and Sybase Adaptive Server query:

DELETE 
FROM sales
FROM sales, titles
WHERE sales.title_id = titles.title_id 
AND titles.type = 'business'

to the following in Oracle:

DELETE 
FROM sales
WHERE title_id in
( SELECT title_id 
   FROM titles
   WHERE type = 'business' 
)

Remove the second FROM even if the WHERE contains a multi-column JOIN.

Convert the following Microsoft SQL Server and Sybase Adaptive Server query:

DELETE 
FROM sales
FROM sales, table_x
WHERE sales.a = table_x.a
   AND sales.b = table_x.b
   AND table_x.c = 'd'

to the following in Oracle:

DELETE 
FROM sales
WHERE ( a, b ) in
 ( SELECT a, b 
   FROM table_x
   WHERE c = 'd' )

Operators

Comparison Operators

The following table compares the operators used in the Microsoft SQL Server and Sybase Adaptive Server, and Oracle databases. Comparison operators are used in WHERE clauses and COLUMN check constraints/rules to compare values

Table 2-11 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Operator	Same in All Three Databases	Microsoft SQL Server and Sybase Adaptive Server Only	Oracle Only
Equal to	=
Not equal to	!= <>		^=
Less than	<
Greater than	>
Less than or equal to	<=	!>
Greater than or equal to	>=	!<
Greater than or equal to x and less than or equal to y	BETWEEN x AND y
Less than x or greater than y	NOT BETWEEN x AND y
Pattern Matches a followed by 0 or more characters a followed by exactly 1 character a followed by any character between x and z a followed by any character except those between x and z a followed by %	LIKE 'a%' LIKE 'a_'	LIKE'a[x-z]' LIKE'a[^x-z]'	LIKE 'a\%' ESCAPE '\'
Does not match pattern	NOT LIKE
No value exists	IS NULL
A value exists	IS NOT NULL
At least one row returned by query	EXISTS (query)
No rows returned by query	NOT EXISTS (query)
Equal to a member of set	IN =ANY		= SOME
Not equal to a member of set	NOT IN != ANY <> ANY		!= SOME <> SOME
Less than a member of set	< ANY		< SOME
Greater than a member of set	> ANY		> SOME
Less than or equal to a member of set	<= ANY	!> ANY	<= SOME
Greater than or equal to a member of set	>= ANY	!< ANY	>= SOME
Equal to every member of set	=ALL
Not equal to every member of set	!= ALL <> ALL
Less than every member of set	< ALL
Greater than every member of set	> ALL
Less than or equal to every member of set	<= ALL	!> ALL
Greater than or equal to every member of set	>= ALL	!< ALL

Recommendations:

Convert all !< and !> to >= and <=

Convert the following in Microsoft SQL Server and Sybase Adaptive Server:
```
WHERE col1 !< 100
```
to this for Oracle:
```
WHERE col1 >= 100
```

Convert like comparisons which use [ ] and [^]

SELECT title
FROM titles
WHERE title like "[A-F]%"

Method 1 - Eliminating use of [ ]:

Use this method with the SUBSTR () function if possible.

SELECT title
from titles
where substr (titles,1,1) in
      ('A', 'B', 'C', 'D', 'E', 'F')

Method 2 - Eliminating use of [ ]:

The second method uses the % construct.

SELECT title
FROM titles
WHERE (title like 'A%' 
   OR  title like 'B%'
   OR  title like 'C%'
   OR  title like 'D%'
   OR  title like 'E%'
   OR  title like 'F%')

Change NULL constructs:

The following table shows that in Oracle, NULL is never equal to NULL. Change the all = NULL constructs to IS NULL to retain the same functionality.

Table 2-12 Changing NULL Constructs

NULL Construct	Microsoft SQL Server and Sybase Adaptive Server	Oracle
where col1 = NULL	depends on the data	FALSE
where col1 != NULL	depends on the data	TRUE
where col1 IS NULL	depends on the data	depends on the data
where col1 IS NOT NULL	depends on the data	depends on the data
where NULL = NULL	TRUE	FALSE

If you have the following in Microsoft SQL Server and Sybase Adaptive Server:

WHERE col1 = NULL

Convert it as follows for Oracle:

WHERE col1 IS NULL

Arithmetic Operators

Table 2-13 Arithmetic Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Operator	Same in All Three Databases	Microsoft SQL Server and Sybase Adaptive Server Only	Oracle Only
Add	+
Subtract	-
Multiply	*
Divide	/
Modulo	v	%	mod(x, y)

Recommendations:

Replace any Modulo functions in Microsoft SQL Server and Sybase Adaptive Server with the mod() function in Oracle.

String Operators

Table 2-14 String Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Operator	Same in All Three Databases	Microsoft SQL Server and Sybase Adaptive Server Only	Oracle Only
Concatenate	s	+	\|\|
Identify Literal	'this is a string'	"this is also a string"

Recommendations:

Replace all addition of strings with the || construct.

Replace all double quotes string identifiers with single quote identifiers.

In Microsoft SQL Server and Sybase Adaptive Server, an empty string ('') is interpreted as a single space in INSERT or assignment statements on VARCHAR data. In concatenating VARCHAR, CHAR, or TEXT data, the empty string is interpreted as a single space. The empty string is never evaluated as NULL. You must bear this in mind when converting the application.

Set Operators

Table 2-15 Set Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Operator	Same in All Three Databases	Oracle Only
Distinct row from either query	UNION
All rows from both queries	UNION ALL
All distinct rows in both queries	d	INTERSECT
All distinct rows in the first query but not in the second query	d	MINUS

Bit Operators

Table 2-16 Bit Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Operator	Same in All Three Databases	Microsoft SQL Server and Sybase Adaptive Server Only	Oracle Only
bit and		&
bit or		\|
bit exclusive or		^
bit not		~

Recommendations:

Oracle enables you to write the procedures to perform bitwise operations.

If you have the following Microsoft SQL Server and Sybase Adaptive Server construct:

X | Y :(Bitwise OR)

You could write a procedure called dbms_bits.or (x,y) and convert the above construct to the following in Oracle:

dbms_bits.or(x,y)

Built-In Functions

Character Functions

Table 2-17 Character Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

*Microsoft SQL Server and Sybase Adaptive Server*	Oracle	Description
ascii(char)	ascii(char)	Returns the ASCII equivalent of the character.
char(integer_expression)	chr(integer_expression)	Converts the decimal code for an ASCII character to the corresponding character.
charindex(specified_exp, char_string)	instr(specified_exp, char_string, 1, 1)	Returns the position where the specified_exp first occurs in the char_string.
convert(data type, expression, [format])	to_char, to_number, to_date, to_label, chartorowid, rowtochar, hextochar, chartohex	Converts one data type to another using the optional format. The majority of the functionality can be matched. Refer to Oracle9i SQL Reference, Release 1 (9.0.1) for more information.
datalength(expression)	g	Computes the length allocated to an expression, giving the result in bytes.
difference(character_exp, character_exp)	d	Returns the numeric difference of the SOUNDEX values of the two strings.
isnull(variable, new_value)	nvl(variable, new_value)	If the value of the variable is NULL, the new_value is returned.
lower(char_exp)	lower(char_exp)	Converts uppercase characters to lowercase characters.
ltrim(char_exp)	ltrim(char_exp)	Truncates trailing spaces from the left end of char_exp.
patindex(pattern,	column_name)	Returns the position of the pattern in the column value. The pattern can have wild characters. This function also works on TEXT and BINARY data types.
replicate(char_exp, n)	rpad(char_exp, length(char_exp)*n, '')	Produces a string with char_exp repeated n times.
reverse(char_string)		Reverses the given char_string.
right(char_exp, n)	substr(char_exp, (length(char_exp)	Returns the part of the string starting at the position given by n from the right and extending up to the end of the string.
rtrim(char_exp)	rtrim(char_exp)	Truncates the trailing spaces from the right end of char_exp.
soundex(exp)	soundex(exp)	Returns phonetically similar expressions to the specified exp.
space(int_exp)	rpad(' ', int_exp-1, '')	Generates a string with int_exp spaces.
str(float_exp, length)	to_char(float_exp)stuff(char_exp, start, length, replace_str)substr(char_exp, 1, start) \|\|replace_str \|\|substr(char_exp, start+length)	Replaces a substring within char_exp with replace_str.
substring(char_exp, start, length) Works on IMAGE and TEXT data types	substr(char_exp, start, length) Does not work with LONG and LONG_RAW data types	Replaces a substring within char_exp with replace_str.
textptr(column_name)	d	Returns a pointer as a varbinary(16) data type for a named IMAGE or TEXT column.
textvalid("column_name", text_pointer)	h	Returns 1 if the specified text_pointer is valid for the specified column_name. The column must be of type TEXT or IMAGE.
upper(char_exp)	upper(char_exp)	Converts lowercase characters to uppercase characters.

Miscellaneous Functions

Table 2-18 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle	Description
datalength(expression)	lengthb	Computes the length allocated to an expression, giving the result in bytes.
isnull(variable, new_value)	nvl(variable, new_value)	If the value of the variable is NULL, the new_value is returned.

Note:

The above functions tables list all the Microsoft SQL Server and Sybase Adaptive Server character manipulation functions. They do not list all the Oracle functions. There are many more Oracle character manipulation functions that you can use.

Defining Functions in Oracle:

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and function of Microsoft SQL Server and Sybase Adaptive Server functions.

Date Functions

Table 2-19 Date Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle	Description
dateadd(dd, int_ exp,datetime_var)	date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of days to the date contained in datetime_var.
dateadd(mm, int_ exp,datetime_var)	add_months(date, int_ exp) or date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of months to the date contained in datetime_var.
dateadd(yy, int_ exp,datetime_var)	date+int_exp requires conversion of int_exp to a number of days	Adds the int_exp number of years to the date contained in datetime_var.
datediff(dd, datetime1,datetime2)	date2-date1	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of days.
datediff(mm, datetime1,datetime2)	months_between ( date2, date1)	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of months.
datediff(yy, datetime1,datetime2)	(date2-date1) /365.254	Returns the difference between the dates specified by the datetime1 and datetime2 variables. This difference is calculated in the number of years.
datename (datepart, date)	to_char(date, format)	Returns the specified part of the date as an integer. The Microsoft SQL Server and Sybase Adaptive Server DATETIME has a higher precision than Oracle DATE. For this reason, it is not always possible to find an equivalent format string in Oracle to match the datepart in Microsoft SQL Server and Sybase Adaptive Server. See the Data Types section of this chapter for more information about conversion of the DATETIME data type.
datepart(datepart, date)	to_char(date, format)	Returns the specified part of the date as a character string (name). The Microsoft SQL Server and Sybase Adaptive Server DATETIME has a higher precision than Oracle DATE'. For this reason, it is not always possible to find an equivalent format string in Oracle to match the datepart in Microsoft SQL Server and Sybase Adaptive Server.
getdate()	sysdate	Returns the system date.

Recommendations:

The above table lists all the Microsoft SQL Server and Sybase Adaptive Server date manipulation functions. It does not list all the Oracle date functions. There are many more Oracle date manipulation functions that you can use.

It is recommended that you convert most date manipulation functions to "+" or "-" in Oracle.

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and functionality of all Microsoft SQL Server and Sybase Adaptive Server functions. This is a useful feature, where users can call a PL/SQL function from a SQL statement's SELECT LIST, WHERE clause, ORDER BY clause, and HAVING clause. With the parallel query option, Oracle executes the PL/SQL function in parallel with the SQL statement. Hence, users create parallel logic.

Mathematical Functions

Table 2-20 Mathematical Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

*Microsoft SQL Server and Sybase Adaptive Server*	Oracle
abs(n)	abs(n)
acos(n)	acos(n)
asin(n)
atan(n)	atan(n)
atn2(n,m)
ceiling(n)	ceil(n)
cos(n)	cos(n)
cot(n)
degrees(n)
exp(n)	exp(n)
floor(n)	floor(n)
log(n)	ln(n)
log10(n)	log(base,number)
pi()
power(m,n)	power(m,n)
radians(n)
rand(n)
round(n[,m])	round(n[,m])
sign(n)	sign(n)
sin(n)	sin(n)
sqrt(n)	sqrt(n)
tan(n)	tan(n)

Recommendations:

The above table lists all the Microsoft SQL Server and Sybase Adaptive Server number manipulation functions. It does not list all the Oracle mathematical functions. There are many more Oracle number manipulation functions that you can use.

Oracle adds the ability to define functions. With this feature you can create Oracle functions that match the name and functionality of all Microsoft SQL Server and Sybase Adaptive Server functions. This is the most flexible approach. Users can write their own functions and execute them seamlessly from a SQL statement.

Oracle functions listed in the table work in SQL as well as PL/SQL.

Locking Concepts and Data Concurrency Issues

Locking

Locking serves as a control mechanism for concurrency. Locking is a necessity in a multi-user environment because more than one user at a time may be working with the same data.

Table 2-21 Locking in Oracle and Microsoft SQL Server and Sybase Adaptive Server

*Microsoft SQL Server and Sybase Adaptive Server*	Oracle
Microsoft SQL Server and Sybase Adaptive Server locking is fully automatic and does not require intervention by users. Microsoft SQL Server and Sybase Adaptive Server apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place. For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, Microsoft SQL Server and Sybase Adaptive Server reads block the modifications to the data. Update locks: Update locks are held at the page level. They are placed during the initial stages of an update operation when the pages are being read. Update locks can co-exist with shared locks. If the pages are changed later, the update locks are escalated to exclusive locks.	Oracle locking is fully automatic and does not require intervention by users. Oracle features the following categories of locks: Data locks (DML locks) to protect data.The "table locks" lock the entire table and "row locks" lock individual rows. Dictionary locks (DDL locks) to protect the structure of objects. Internal locks to protect internal structures, such as files. DML operations can acquire data locks at two different levels; one for specific rows and one for entire tables. Row-level locks: An exclusive lock is acquired for an individual row on behalf of a transaction when the row is modified by a DML statement. If a transaction obtains a row level lock, it also acquires a table (dictionary) lock for the corresponding table. This prevents conflicting DDL (DROP TABLE, ALTER TABLE) operations that would override data modifications in an on-going transaction.
Intent locks: Microsoft SQL Server and Sybase Adaptive Server locking is fully automatic and does not require intervention by users. Microsoft SQL Server and Sybase Adaptive Server apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place. For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, Microsoft SQL Server and Sybase Adaptive Server reads block the modifications to the data. Extent locks: Extent locks lock a group of eight database pages while they are being allocated or freed. These locks are held during a CREATE or DROP statement, or during an INSERT that requires new data or index pages. A list of active locks for the current server can be seen with SP_LOCK system procedure.	Table-level data locks can be held in any of the following modes: Row share table lock (RW): This indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. This prevents other transactions from obtaining exclusive write access to the same table by using the LOCK TABLE table IN EXCLUSIVE MODE statement. Apart from that, all the queries, inserts, deletes, and updates are allowed in that table. Row exclusive table lock (RX): This generally indicates that the transaction holding the lock has made one or more updates to the rows in the table. Queries, inserts, deletes, updates are allowed in that table. Share lock (SL): Share row exclusive lock(SRX) Exclusive lock (X): The dynamic performance table V$LOCK keeps the information about locks.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Microsoft SQL Server and Sybase Adaptive Server locking is fully automatic and does not require intervention by users.

Microsoft SQL Server and Sybase Adaptive Server apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place.

For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, Microsoft SQL Server and Sybase Adaptive Server reads block the modifications to the data.

Update locks:

Update locks are held at the page level. They are placed during the initial stages of an update operation when the pages are being read. Update locks can co-exist with shared locks. If the pages are changed later, the update locks are escalated to exclusive locks.

Oracle locking is fully automatic and does not require intervention by users. Oracle features the following categories of locks:

Data locks (DML locks) to protect data.The "table locks" lock the entire table and "row locks" lock individual rows.

Dictionary locks (DDL locks) to protect the structure of objects.

Internal locks to protect internal structures, such as files.

DML operations can acquire data locks at two different levels; one for specific rows and one for entire tables.

Row-level locks:

An exclusive lock is acquired for an individual row on behalf of a transaction when the row is modified by a DML statement. If a transaction obtains a row level lock, it also acquires a table (dictionary) lock for the corresponding table. This prevents conflicting DDL (DROP TABLE, ALTER TABLE) operations that would override data modifications in an on-going transaction.

Intent locks:

Microsoft SQL Server and Sybase Adaptive Server locking is fully automatic and does not require intervention by users. Microsoft SQL Server and Sybase Adaptive Server apply exclusive locks for INSERT, UPDATE, and DELETE operations. When an exclusive lock is set, no other transaction can obtain any type of lock on those objects until the original lock is in place. For non-update or read operations, a shared lock is applied. If a shared lock is applied to a table or a page, other transactions can also obtain a shared lock on that table or page. However, no transaction can obtain an exclusive lock. Therefore, Microsoft SQL Server and Sybase Adaptive Server reads block the modifications to the data.

Extent locks:

Extent locks lock a group of eight database pages while they are being allocated or freed. These locks are held during a CREATE or DROP statement, or during an INSERT that requires new data or index pages.

A list of active locks for the current server can be seen with SP_LOCK system procedure.

Table-level data locks can be held in any of the following modes:

Row share table lock (RW):

This indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. This prevents other transactions from obtaining exclusive write access to the same table by using the LOCK TABLE table IN EXCLUSIVE MODE statement. Apart from that, all the queries, inserts, deletes, and updates are allowed in that table.

Row exclusive table lock (RX):

This generally indicates that the transaction holding the lock has made one or more updates to the rows in the table. Queries, inserts, deletes, updates are allowed in that table.

Share lock (SL):

Share row exclusive lock(SRX)

Exclusive lock (X):

The dynamic performance table V$LOCK keeps the information about locks.

Recommendations:

In Microsoft SQL Server and Sybase Adaptive Server, SELECT statements obtain shared locks on pages/rows. This prevents other statements from obtaining an exclusive lock on those pages/rows. All statements that update the data need an exclusive lock. This means that the SELECT statement in Microsoft SQL Server and Sybase Adaptive Server blocks the UPDATE statements as long as the transaction that includes the SELECT statement does not commit or rollback. This also means that two transactions are physically serialized whenever one transaction selects the data and the other transaction wants to change the data first and then select the data again. In Oracle, however, SELECT statements do not block UPDATE statements, since the rollback segments are used to store the changed data before it is updated in the actual tables. Also, the reader of the data is never blocked in Oracle. This allows Oracle transactions to be executed simultaneously.

If Microsoft SQL Server and Sybase Adaptive Server logical transactions are automatically translated to Oracle logical transactions, the transactions explained above that execute properly in Microsoft SQL Server and Sybase Adaptive Server as they are serialized causes a deadlock in Oracle. These transactions should be identified and serialized to avoid the deadlock. These transactions are serialized in Microsoft SQL Server and Sybase Adaptive Server as INSERT, UPDATE, and DELETE statements block other statements.

Row-Level Versus Page-Level Locking

Table 2-22 Row-Level Versus Page-Level Locking in Oracle and Microsoft SQL Server and Sybase Adaptive Server

*Microsoft SQL Server and Sybase Adaptive Server*	Oracle
Microsoft SQL Server and Sybase Adaptive Server do not have a row-level locking feature. Microsoft SQL Server and Sybase Adaptive Server apply a page-level lock, which effectively locks all rows on the page, whenever any row in the page is being updated. This is an exclusive lock whenever the data is being changed by DML statements. Microsoft SQL Server 7.0 implements a form of row-level locking. Microsoft SQL Server 7.0 escalates locks at row level to page level automatically. SELECT statements are blocked by exclusive locks that lock an entire page.	Oracle has a row-locking feature. Only one row is locked when a DML statement is changing the row.

Microsoft SQL Server and Sybase Adaptive Server

Oracle

Microsoft SQL Server and Sybase Adaptive Server do not have a row-level locking feature.

Microsoft SQL Server and Sybase Adaptive Server apply a page-level lock, which effectively locks all rows on the page, whenever any row in the page is being updated. This is an exclusive lock whenever the data is being changed by DML statements.

Microsoft SQL Server 7.0 implements a form of row-level locking.

Microsoft SQL Server 7.0 escalates locks at row level to page level automatically.

SELECT statements are blocked by exclusive locks that lock an entire page.

Oracle has a row-locking feature. Only one row is locked when a DML statement is changing the row.

Recommendations:

No changes are required to take advantage of the row-level locking feature of Oracle.

Read Consistency

Table 2-23 Read Consistency in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server	Oracle
Microsoft SQL Server and Sybase Adaptive Server provide the HOLDLOCK function for transaction-level read consistency. Specifying a SELECT with HOLDLOCK puts a shared lock on the data. More than one user can execute a SELECT with HOLDLOCK at the same time without blocking each other. When one of the users tries to update the selected data, HOLDLOCK blocks the update until the other users commit, rollback, or attempt an update and a deadlock occurs. This means that HOLDLOCK prevents other transactions from updating the same data until the current transaction is in effect.	Read consistency as supported by Oracle does the following: Ensures that the set of data seen by a statement is consistent at a single point-in-time and does not change during statement execution Ensures that reads of database data do not wait for other reads or writes of the same data Ensures that writes of database data do not wait for reads of the same data Ensures that writes wait for other writes only if they attempt to update identical rows in concurrent transactions To provide read consistency, Oracle creates a read-consistent set of data when a table is being read and simultaneously updated. Read consistency functions as follows: When an update occurs, the original datavalues changed by the update are recorde in rollback segments. While the update remains part of an uncommitted transaction, any user that reads the modified data views the original data values. Only statements that start afteranother user's transaction is committed reflect the changes made by the transaction. You can specify that a transaction be read only using the following command: SET TRANSACTION READ ONLY

Microsoft SQL Server

Oracle

Microsoft SQL Server and Sybase Adaptive Server provide the HOLDLOCK function for transaction-level read consistency. Specifying a SELECT with HOLDLOCK puts a shared lock on the data. More than one user can execute a SELECT with HOLDLOCK at the same time without blocking each other.

When one of the users tries to update the selected data, HOLDLOCK blocks the update until the other users commit, rollback, or attempt an update and a deadlock occurs. This means that HOLDLOCK prevents other transactions from updating the same data until the current transaction is in effect.

Read consistency as supported by Oracle does the following:

Ensures that the set of data seen by a statement is consistent at a single point-in-time and does not change during statement execution
Ensures that reads of database data do not wait for other reads or writes of the same data
Ensures that writes of database data do not wait for reads of the same data
Ensures that writes wait for other writes only if they attempt to update identical rows in concurrent transactions

To provide read consistency, Oracle creates a read-consistent set of data when a table is being read and simultaneously updated.

Read consistency functions as follows:

When an update occurs, the original datavalues changed by the update are recorde in rollback segments.
While the update remains part of an uncommitted transaction, any user that reads the modified data views the original data values. Only statements that start afteranother user's transaction is committed reflect the changes made by the transaction.

You can specify that a transaction be read only using the following command:

  SET TRANSACTION READ ONLY

Logical Transaction Handling

Table 2-24 Logical Transaction Handling in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Microsoft SQL Server and Sybase Adaptive Server	Oracle
After completion, any statement not within a transaction is automatically committed.A statement can be a batch containing multiple T-SQL statements that are sent to the server as one stream. The changes to the database are automatically committed after the batch executes. A ROLLBACK TRAN statement subsequently executed has no effect. In Microsoft SQL Server and Sybase Adaptive Server, transactions are not implicit. Start logical transaction with a BEGIN TRANSACTION clause. Logical transactions can be committed or rolled back as follows. BEGIN TRANSACTION [transaction_ name] Use COMMIT TRAN to commit the transaction to the database. You have the option to specify the transaction name. Use ROLLBACK TRAN to roll back the transaction. You can set savepoints to roll back to a certain point in the logical transaction using the following command: SAVE TRANSACTION savepoint_name Roll back to the specified SAVEPOINT with the following command: ROLLBACK TRAN <savepoint_name>	Statements are not automatically committed to the database. The COMMIT WORK statement is required to commit the pending changes to the database. Oracle transactions are implicit. This means that the logical transaction starts as soon as data changes in the database. COMMIT WORK commits the pending changes to the database. ROLLBACK undoes all the transactions after the last COMMIT WORK statement. Savepoints can be set in transactions with the following command: SET SAVEPOINT savepoint_name The following command rolls back to the specified SAVEPOINT: ROLLBACK <savepoint_name> Two-phase commit is automatic and transparent in Oracle. Two-phase commit operations are needed only for transactions which modify data on two or more databases.
Microsoft SQL Server and Sybase Adaptive Server allow you to nest BEGIN TRAN/COMMIT TRAN statements. When nested, the outermost pair of transactions creates and commits the transaction. The inner pairs keep track of the nesting levels. A ROLLBACK command in the nested transactions rolls back to the outermost BEGIN TRAN level, if it does not include the name of the SAVEPOINT. Most Microsoft SQL Server and Sybase Adaptive Server applications require two-phase commit, even on a single server. To see if the server is prepared to commit the transaction, use PREPARE TRAN in two-phase commit applications. Completed transactions are written to the database device at CHECKPOINT. A CHECKPOINT writes all dirty pages to the disk devices since the last CHECKPOINT. Calls to remote procedures are executed independently of any transaction in which they are included.	When a CHECKPOINT occurs, the completed transactions are written to the database device. A CHECKPOINT writes all dirty pages to the disk devices that have been modified since last checkpoint

Microsoft SQL Server and Sybase Adaptive Server

Oracle

After completion, any statement not within a transaction is automatically committed.A statement can be a batch containing multiple T-SQL statements that are sent to the server as one stream. The changes to the database are automatically committed after the batch executes. A ROLLBACK TRAN statement subsequently executed has no effect. In Microsoft SQL Server and Sybase Adaptive Server, transactions are not implicit. Start logical transaction with a BEGIN TRANSACTION clause. Logical transactions can be committed or rolled back as follows.

BEGIN TRANSACTION [transaction_
name]

Use COMMIT TRAN to commit the transaction to the database. You have the option to specify the transaction name. Use ROLLBACK TRAN to roll back the transaction. You can set savepoints to roll back to a certain point in the logical transaction using the following command:

  SAVE TRANSACTION savepoint_name

Roll back to the specified SAVEPOINT with the following command:

ROLLBACK TRAN <savepoint_name>

Statements are not automatically committed to the database. The COMMIT WORK statement is required to commit the pending changes to the database.

Oracle transactions are implicit. This means that the logical transaction starts as soon as data changes in the database.

COMMIT WORK commits the pending changes to the database.

ROLLBACK undoes all the transactions after the last COMMIT WORK statement.

Savepoints can be set in transactions with the following command:

  SET SAVEPOINT savepoint_name

The following command rolls back to the specified SAVEPOINT:

  ROLLBACK <savepoint_name>

Two-phase commit is automatic and transparent in Oracle. Two-phase commit operations are needed only for transactions which modify data on two or more databases.

Microsoft SQL Server and Sybase Adaptive Server allow you to nest BEGIN TRAN/COMMIT TRAN statements. When nested, the outermost pair of transactions creates and commits the transaction. The inner pairs keep track of the nesting levels. A ROLLBACK command in the nested transactions rolls back to the outermost BEGIN TRAN level, if it does not include the name of the SAVEPOINT. Most Microsoft SQL Server and Sybase Adaptive Server applications require two-phase commit, even on a single server. To see if the server is prepared to commit the transaction, use PREPARE TRAN in two-phase commit applications.

Completed transactions are written to the database device at CHECKPOINT. A CHECKPOINT writes all dirty pages to the disk devices since the last CHECKPOINT.

Calls to remote procedures are executed independently of any transaction in which they are included.

When a CHECKPOINT occurs, the completed transactions are written to the database device. A CHECKPOINT writes all dirty pages to the disk devices that have been modified since last checkpoint

Recommendations:

Transactions are not implicit in Microsoft SQL Server and Sybase Adaptive Server. Therefore, applications expect that every statement they issue is automatically committed it is executed.

Oracle transactions are always implicit, which means that individual statements are not committed automatically. When converting an Microsoft SQL Server and Sybase Adaptive Server application to an Oracle application, care needs to be taken to determine what constitutes a transaction in that application. In general, a COMMIT work statement needs to be issued after every "batch" of statements, single statement, or stored procedure call to replicate the behavior of Microsoft SQL Server and Sybase Adaptive Server for the application.

In Microsoft SQL Server and Sybase Adaptive Server, transactions may also be explicitly begun by a client application by issuing a BEGIN TRAN statement during the conversion process.

2 Microsoft SQL Server, Sybase Adaptive Server, and Oracle Compared

Schema Migration

Schema Object Similarities

Table 2-1 Schema Objects in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Schema Object Names

Table Design Considerations

Data Types

DATETIME Data Types

IMAGE and TEXT Data Types (Binary Large Objects)

Microsoft SQL Server and Sybase Adaptive Server User-Defined Data Types

Entity Integrity Constraints

Referential Integrity Constraints

Unique Key Constraints

Check Constraints

SQL Server Rule:

Data Types

Data Types Table

Table 2-2 Data Types in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Table 2-3 Data Type Conversion for NULL Values

Data Storage Concepts

Data Storage Concepts Table

Table 2-4 Data Storage Concepts in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Data Manipulation Language

Connecting to the Database

Table 2-5 Connecting to the Database in Oracle and Microsoft SQL Server and Sybase Adaptive Server

SELECT Statement

Table 2-6 SELECT Statements in Oracle and Microsoft SQL Server and Sybase Adaptive Server

SELECT Statements without FROM Clauses:

SELECT INTO Statement:

Subqueries in Place of Columns:

Microsoft SQL Server and Sybase Adaptive Server:

Oracle:

Comparing Subqueries to Subqueries:

Column Aliases:

Table Aliases:

Compute:

Outer JOIN Syntax:

SELECT with GROUP BY Statement

Table 2-7 SELECT with GROUP BY Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

INSERT Statement

Table 2-8 INSERT Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

UPDATE Statement

Table 2-9 UPDATE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Method 1 - Convert UPDATE statements with FROM clauses:

Method 2 - Convert UPDATE statements with FROM clauses:

DELETE Statement

Table 2-10 DELETE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Remove Second FROM Clause:

Operators

Comparison Operators

Table 2-11 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Table 2-12 Changing NULL Constructs

Arithmetic Operators

Table 2-13 Arithmetic Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

String Operators

Table 2-14 String Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Set Operators

Table 2-15 Set Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Bit Operators

Table 2-16 Bit Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Built-In Functions

Character Functions

Table 2-17 Character Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Miscellaneous Functions

Table 2-18 Comparison Operators in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Defining Functions in Oracle:

Date Functions

Table 2-19 Date Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Mathematical Functions

Table 2-20 Mathematical Functions in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Locking Concepts and Data Concurrency Issues

Locking

Table 2-21 Locking in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Row-Level Versus Page-Level Locking

Table 2-22 Row-Level Versus Page-Level Locking in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Read Consistency

Table 2-23 Read Consistency in Oracle and Microsoft SQL Server and Sybase Adaptive Server

Logical Transaction Handling

Table 2-24 Logical Transaction Handling in Oracle and Microsoft SQL Server and Sybase Adaptive Server

2
Microsoft SQL Server, Sybase Adaptive Server, and Oracle Compared