Chapter 11 - Developing: Applications - Migrating Oracle SQL and PL/SQL

Article
12/05/2007

Introduction

Although Oracle and Microsoft® SQL Server™ both use the ANSI SQL language, each uses proprietary extensions to add functionality. Oracle uses PL/SQL, while SQL Server uses Transact SQL. This chapter focuses on application code that may need to be modified for use with SQL Server because of these extensions.

This chapter should be used in conjunction with Chapters 12 through 17. These chapters provide information for application-specific languages, including Perl, PHP, Java, Python, Pro*C, and Oracle Forms.

No application migration will be successful without a clear understanding of the Oracle technologies that come into play during an Oracle database migration to the SQL Server platform. The strategy for application conversion is broken down into the following two segments:

Data access, which is discussed in this chapter.
Programming language and connectivity tier, which are discussed in Chapters 12 through 17.

Migrating Data Access

The application conversion development team is responsible for ensuring that applications designed for the Oracle source database work the same way with the migrated SQL Server target database.

The application migration process may appear complicated. Before doing the migration of the Oracle production database, the DBA or application developer should install a SQL Server test database. Applications can then be modified and tested, if necessary, to ensure their original functionality with the SQL Server database. Testing is necessary because of the core differences between Oracle and SQL Server.

There are some architectural differences between each RDBMS. The terminology used to describe Oracle architecture often has a completely different meaning in Microsoft SQL Server; for example, the term database has a different meaning. Additionally, both Oracle and SQL Server have made many proprietary extensions to the SQL-92 standard. These extensions are discussed throughout this chapter.

From an application developer's perspective, Oracle and SQL Server manage data in similar ways. The internal differences between Oracle and SQL Server are significant, but if managed properly, have minimal impact on a migrated application.

The most significant migration issue that confronts the developer is the implementation of the SQL-92 SQL language standard and the extensions that each RDBMS has to offer. Some developers use only standard SQL language statements, preferring to keep their program code as generic as possible. Generally, this means restricting program code to the entry-level SQL-92 standard, which is implemented consistently across many database products, including Oracle and SQL Server.

Using RDBMS-specific extensions might produce unneeded complexity in the program code during the migration. For example, Oracle's DECODE function is a nonstandard SQL extension specific to Oracle. The CASE expression in SQL Server is not implemented in all database products.

Both the Oracle DECODE and the SQL Server CASE expressions can perform sophisticated conditional evaluations from within a query. The alternative to using these functions is to perform the function programmatically, which could require substantially more data be retrieved from the RDBMS.

SQL has seen several sets of standards implemented and embraced by American National Standards Institute (ANSI) and International Standards Organization (ISO). Although there are several enhancement releases of each standard set, the major releases are SQL-86, SQL-89 (also called SQL1), SQL-92 (also called SQL2) and SQL-99 (also called SQL3), with SQL-99 merging ANSI and ISO standards into one set.

There are four levels or sets of features in SQL-92: entry-level, transitional, intermediate, and full. SQL Server 2000 Transact-SQL complies with the entry level of the SQL-92 standard, and it supports many additional features from the intermediate and full levels of the standard.

SQL-99 has two sets of features: core and non-core. Oracle 9i fully supports a majority of the core SQL-99 features, and it partially supports the non-core features.

The SQL-92 entry-level feature set is very similar to the SQL-99 core feature set, which means Oracle 9i supports entry level features of SQL-92 entry level. Even though both support many of the standard features, Oracle and SQL Server do not use the same syntax. In addition, each RDBMS has its own unique functionality and extensions.

Oracle SQL statements and Microsoft SQL Server T-SQL are compatible with each other in several areas with minimal changes in the syntax. All the basic and advanced functionality furnished by Oracle can be achieved in SQL Server with ease.

Though both these RDBMSs can be used to build robust and efficient systems, they differ radically in the administrative functions and platform dependence.

This section shows how to arrive at T-SQL equivalents for the most common Oracle SQL usages, and it includes comprehensive coverage of all aspects of the SQL language implementation.

Sample Tables

The following tables are used in all the examples in this chapter.

Table 11.1: Category Table

COLUMN_NAME	DATATYPE	CONSTRAINT
CATEGORYID	VARCHAR(10)	Primary Key
CATEGORYNAME	VARCHAR(40)
DESCRIPTION	VARCHAR(50)

Table 11.2: Customer Table

COLUMN_NAME	DATATYPE	CONSTRAINT
CUSTOMERID	VARCHAR(10)	Primary Key
COMPANYNAME	VARCHAR(40)
CONTACTNAME	VARCHAR(40)
CONTACTTITLE	VARCHAR(40)
ADDRESS	VARCHAR(50)
CITY	VARCHAR(30)
REGION	VARCHAR(30)
POSTALCODE	VARCHAR(10)
COUNTRY	VARCHAR(30)
PHONE	NUMERIC(10.0)
FAX	NUMERIC(10.0)

Table 11.3: Employee Table

COLUMN_NAME	DATATYPE	CONSTRAINT
EMPLOYEEID	VARCHAR(10)	Primary Key
LASTNAME	VARCHAR(40)
FIRSTNAME	VARCHAR(40)
TITLE	VARCHAR(40)
TITLEOFCOURTESY	VARCHAR(50)
BIRTHDATE	DATETIME
HIREDATE	DATETIME
ADDRESS	VARCHAR(40)
CITY	VARCHAR(30)
POSTALCODE	NUMERIC(10.0)
COUNTRY	VARCHAR(10)
REPORTINGTO	VARCHAR(10)	Foreign Key

Table 11.4: OrderMaster Table

COLUMN_NAME	DATATYPE	CONSTRAINT
ORDERID	VARCHAR(10)	Primary Key
CUSTOMERID	VARCHAR(10)	Foreign Key
EMPLOYEEID	VARCHAR(10)	Foreign Key
ORDERDATE	DATETIME
REQUIREDDATE	DATETIME
SHIPPEDDATE	DATETIME
SHIPVIA	VARCHAR(30)
FREIGHT	NUMERIC(10.0)
SHIPNAME	VARCHAR(30)
SHIPADDRESS	VARCHAR(30)
SHIPCITY	VARCHAR(10)
SHIPREGION	VARCHAR(30)
SHIPPOSTALCODE	VARCHAR(10)

Table 11.5: OrderDetails Table

COLUMN_NAME	DATATYPE	CONSTRAINT
ORDERID	VARCHAR(10)	Foreign Key
PRODUCTID	VARCHAR(10)	Foreign Key
UNITPRICE	NUMERIC(10.2)
QUANTITY	NUMERIC(10.0)
DISCOUNT	NUMERIC(10.0)

Table 11.6: OrderPrice Table

COLUMN_NAME	DATATYPE	CONSTRAINT
ORDERID	VARCHAR(10)	Foreign Key
PRODUCTID	VARCHAR(10)	Foreign Key
REVISEDPRICE	NUMERIC(10.2)
REVISEDON	DATETIME

Table 11.7: Product Table

COLUMN_NAME	DATATYPE	CONSTRAINT
PRODUCTID	VARCHAR(10)	Primary Key
PRODUCTNAME	VARCHAR(30)
SUPPLIERID	VARCHAR(30)	Foreign Key
CATEGORYID	VARCHAR(10)	Foreign Key
QUANTITYPERUNIT	NUMERIC(10.0)
UNITPRICE	NUMERIC(10.2)
UNITINSTOCK	NUMERIC(10.0)
UNITSONORDER	NUMERIC(10.0)
REORDERLEVEL	NUMERIC(10.0)
DISCONTINUED	NUMERIC(10.0)

Table 11.8: Shippers Table

COLUMN_NAME	DATATYPE	CONSTRAINT
SHIPPERID	VARCHAR(10)	Primary Key
COMPANYNAME	VARCHAR(30)
PHONE	NUMERIC(10.0)

Table 11.9: Suppliers Table

COLUMN_NAME	DATATYPE	CONSTRAINT
SUPPLIERID	INT DENTITY	Primary Key
COMPANYNAME	VARCHAR(30)
CONTACTNAME	VARCHAR(30)
CONTACTTITLE	VARCHAR(40)
ADDRESS	VARCHAR(40)
CITY	VARCHAR(30)
POSTALCODE	NUMERIC(10.0)
COUNTRY	VARCHAR(40)
PHONE	NUMERIC(10)
FAX	NUMERIC(10)

Table 11.10: ShippedOrders Table

COLUMN_NAME	DATATYPE	CONSTRAINT
ORDERID	VARCHAR(10)	Foreign Key
CUSTOMERID	VARCHAR(10)	Foreign Key
EMPLOYEEID	VARCHAR(10)	Foreign Key
ORDERDATE	DATETIME
REQUIREDDATE	DATETIME
SHIPPEDDATE	DATETIME
SHIPVIA	VARCHAR(30)
FREIGHT	NUMERIC(10.0)
SHIPNAME	VARCHAR(30)
SHIPADDRESS	VARCHAR(30)
SHIPCITY	VARCHAR(10)
SHIPREGION	VARCHAR(30)
SHIPPOSTALCODE	VARCHAR(10)

Migration Process Overview

Recommended high-level steps for the migration process include:

Extraction of data access. Identify the SQL statements used in the application and make sure it will work with SQL Server. If some SQL statements are database-specific, rewrite them for SQL Server.
Transaction management. Accommodate changes in the transaction because of the migration.
Fetch strategy. Pay special attention to cursor management and rewrite it in easier way by making use of effective cursor management available in SQL Server 2000. Try to avoid cursors being used by Oracle application if possible.
Subprograms conversion. Identify all the procedures, functions, and triggers and rewrite in T-SQL.
Job scheduling. Batch jobs that are written in PL/SQL should be rewritten.
Interface file conversion. For any inbound (text file -> table) jobs, pay attention to the interface file and make sure there are no problems with the current format of the interface file. Pay attention to any outbound interface as well. Keep in mind that the date format between Oracle and SQL Server are different.
Workflow automation. Workflow automation is implemented in the application. The creation of Mail Ids is required.
Performance tuning. Tune the T-SQL statements wherever it is required.

During these steps, the developer should look at the application code and start converting Oracle-specific code into T-SQL compatible code. All major Oracle commands and how to convert them into T-SQL are discussed in detail throughout this chapter.

Oracle and SQL Server both provide extensive support for XML, whose migration is not within the scope of this guide. For details on the SML support available in SQL Server refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_9oj8.asp.

Step 1: Extraction of Data Access

The first step is to identify the SQL used in the application. SQL includes Data Manipulation Language (DML), Data Query Language (DQL or, simply, queries), and operators. The majority of SQL syntax is common between Oracle and SQL Server with minor variations.

There are several different types of information in the SQL code that will need to be identified. If these types do not comply with the specifications and syntax of SQL Server, this information will need to be modified. An in-depth discussion of the following topics is included in this section:

Operators
Functions
Queries
Data Manipulation Language (DML)

Operators

Operators are used for many different purposes in SQL. Table 11.11 displays these operators by type.

Table 11.11: Commonly Used Operators

Operator Type	Example
Arithmetic	+, -, *, /
Comparison	=, <>, <=, >=
Logical	AND, OR, NOT , XOR

These operators may play a role in the SELECT clause of a query (for reporting purposes), or they may be used in a PL/SQL block for performing various calculations to complete the transaction.

The information under the following heading describes how each of the operators is used in Oracle and SQL Server.

Operator Precedence

When multiple arithmetic operators are used in a single query, the processing of the operation takes place according to the precedence of the operators. The precedence level of arithmetic operators in an expression is multiplication (*), division (/), modulo (%), addition (+), concatenation (+), and subtraction (-). Equations contained within parentheses take precedence and are performed first. The order of execution is from left to right.

Concatenation Operators

Concatenation is performed differently between SQL Server and Oracle. The concatenation operator (for character strings) used in Oracle is “||” or CONCAT(string1, string2). The following example shows this syntax for each usage. Each of these statements will produce the same result in Oracle.

SELECT FIRSTNAME || LASTNAME NAME FROM EMPLOYEE

SELECT CONCAT(FIRSTNAME, LASTNAME) NAME FROM EMPLOYEE;

In these statements, the first name is concatenated with the last name and the label for the field is NAME. SQL Server uses a different operator for concatenation. The concatenation operator (for character strings) used in SQL Server is “+.” The following example shows the first name concatenated with the last name, along with empty space in between the names. The label for the field is NAME.

SELECT FIRSTNAME + ‘ ‘ + LASTNAME As NAME FROM EMPLOYEE

Comparison Operators

Conditions are evaluated using comparison operators, including "=", ">" and ">=", “<”, and “<=”. These operators are common to both Oracle and SQL Server, with the exception of the inequality operator. Generally, Oracle developers use "!=" (and rarely "^=") instead of "<>"as an inequality operator. Though "! =" can be used in SQL Server, "<>" is recommended because it is the ANSI Standard.

Oracle and SQL Server have the clauses IS NULL and IS NOT NULL for comparisons involving null values. The use of IS NULL and IS NOT NULL will ensure behavior consistent with that of Oracle irrespective of the setting of ANSI_NULLS variable.

Modulo Operator

The modulo function returns the remainder when two numbers are divided. Oracle uses MOD function as shown in the following example:

SELECT MOD(10,3) FROM DUAL;

This example returns 1. SQL Server uses "%" to perform this operation. The following example is the same statement written for SQL Server:

SELECT 10%3

Logical Operators

Like comparison operators, logical operators are the same in Oracle and SQL Server. Table 11.12 reviews logical operators.

Table 11.12: Common Logical Operators in Oracle and SQL Server

Operator	Meaning
ALL	TRUE if all of a set of comparisons are TRUE
AND	TRUE if both Boolean expressions are TRUE
ANY	TRUE if any one of a set of comparisons is TRUE
BETWEEN	TRUE if the operand is within a range
EXISTS	TRUE if a subquery contains any rows
IN	TRUE if the operand is equal to one of a list of expressions
LIKE	TRUE if the operand matches a pattern
NOT	Reverses the value of any other Boolean operator
SOME	TRUE if some of a set of comparisons are TRUE
IS NULL	TRUE if operand is NULL
IS NOT NULL	TRUE if operand is not NULL
OR	TRUE if either Boolean expression is TRUE

Datatype Precedence

Each column value and constant in a SQL statement has a datatype that is associated with a specific storage format, constraints, and a valid range of values. When a table is created, a datatype is specified for each column.

Arithmetic operations performed on columns and constants of different datatypes, such as INT and SMALLINT, are called as mixed mode arithmetic operations. In mixed mode arithmetic operations, the lower datatype value is converted into a higher datatype value according to datatype precedence.

Datatype conversion is often needed to attain compatibility during calculation, concatenation, or comparison of values.

Functions

In many places, built-in functions are used for conversion and for other purposes. Some commonly used functions are defined and discussed here with examples provided for their use. Some functions that are available in Oracle are not available in SQL Server 2000 or earlier versions. However, those functions can be written in SQL Server with the same name and the same functionality. They are called user-defined functions. Some user-defined functions are described later in this chapter.

Number and Mathematical Functions

Table 11.13 details the differences in syntax and usage between Oracle and SQL Server. These functions are all mathematically based.

Table 11.13: Mathematical Functions in Oracle and SQL Server

Oracle Function	Description	Oracle Example	SQL Server Function	SQL Server Example
ABS	Returns the absolute value of n	ABS (-20)	ABS	ABS(-20)
ACOS	Returns cosine of n. n must be between –1 and 1	ACOS (.4)	ACOS	ACOS(.4)
CEIL	Returns the smallest integer greater than n	CEIL (12.3) returns 13	CEILING	CEILING (12.3) returns 13
FLOOR	Returns the smallest integer smaller than n	FLOOR (12.3) returns 12	FLOOR	FLOOR (12.3) returns 12
MOD	Returns the reminder of m divided by n	MOD (12,5) returns 2	%	12%5 returns 2
POWER	Computes the value of argument 1 raised to the power of argument 2	POWER (10,5) returns 100000	POWER	POWER (10,5) returns 100000
ROUND	Returns the nearest value of the decimal as per the precision specified	ROUND (10.125,2) returns 10.13	ROUND	ROUND (10.125,2) returns 10.130
TRUNC	Truncates the decimal	TRUNC (12.54) returns 12	CONVERT	CONVERT (INTEGER, 12.54) returns 12

Character Functions

Table 11.14 details the differences in syntax and usage between Oracle and SQL Server. These functions are all related to text strings and characters.

Table 11.14: Character Functions in Oracle and SQL Server

Oracle Function	Description	Oracle Example	SQL Server Function	SQL Server Example
CHR	Returns the character for the ASCII value	CHR (65) returns A	CHAR	CHAR (65) returns A
CONCAT	Appends two string values	CONCAT ('Tom', 'Mike') return Tom Mike	+	'Tom' + 'Mike' returns Tom Mike
INITCAP	Gives uppercase to the title (Title case)	INITCAP ('terry adams') returns Terry Adams	No equivalent function	An algorithm can be written for equivalent functionality
LOWER	Returns the lowercase of the string	LOWER ('TIM') returns tim	LOWER	LOWER('TIM') returns tim
LTRIM	Trims the leading spaces in a given String	LTRIM (' TIM') returns 'TIM'	LTRIM	LTRIM(' TIM') returns 'TIM'
REPLACE	Replaces a matching String with a new String in a given String	REPLACE ('TIM','I','o') returns ToM	REPLACE	REPLACE('TIM','i','o') returns ToM
RPAD	Pad characters to the right side of the string	RPAD ('USA',5,'') returns USA*	No equivalent function	An algorithm can be written for equivalent functionality
LPAD	Pad characters to the left side of the string	LPAD ('USA',5,'') returns *USA	No equivalent function	An algorithm can be written for equivalent functionality
SOUNDEX	SOUNDEX function lets you to compare words that are spelled differently but sound alike	SOUNDEX ('CHARLOTTE') is equal to SOUNDEX('CHARLOTE')	SOUNDEX	SOUNDEX ('CHARLOTTE') is equal to SOUNDEX('CHARLOTE')
SUBSTR	To take only a few characters from a string	SUBSTRING ('Bikes', 1,4) returns Bike	SUBSTRING	SUBSTRING ('Bikes', 1,4) returns Bike
TRANSLATE	Translate a character string. Used for encryption	This function in Oracle is used to convert a string into another form. Low level of encryption is done using this.	No equivalent function	An algorithm can be written for equivalent functionality
TRIM	To remove the leading and trailing spaces of a string	TRIM (' THIS IS TEST ') returns THIS IS TEST	LTRIM and RTRIM can be combined	RTRIM(LTRIM(' THIS IS TEST ')) returns THIS IS TEST
UPPER	Returns the uppercase of the string	UPPER ('tim') returns TIM	UPPER	UPPER('tim') returns TIM
ASCII	Returns the ASCII value of the character	ASCII ('A') returns 65	ASCII	ASCII('A') returns 65
INSTR	Used to find the location of a sub string or a character inside a string	INSTR ('NORTH CAROLINA','OR', 1) returns 2	CHARINDEX	CHARINDEX ('OR','NORTH CAROLINA', 1) returns 2
LENGTH	Returns the length of a given string	LENGTH ('TOM') returns 3	LEN	LEN ('TOM') returns 3

Date Functions

Table 11.15 details the differences in syntax and usage between Oracle and SQL Server. These functions are all date-based.

Table 11.15: Date Functions in Oracle and SQL Server

Oracle Function	Description	Oracle Example	SQL Server Function	SQL Server Example
ADD_MONTHS	To add number of months for a given date	ADD_MONTHS ('15-NOV-2004', 1) returns '15-DEC-2004'	DATEADD	DATEADD (MM, 1,'15-NOV-2004') returns '15-DEC-2004'
NEXT_DAY	NEXT_DAY function returns the first weekday later than the passed date	NEXT_DAY (SYSDATE, 'FRIDAY') returns the date of the coming Friday	No equivalent function	An algorithm can be written for equivalent functionality
SYSDATE	Returns the current date	SYSDATE returns the current date	GETDATE ()	GETDATE () returns the current date with time stamp
TO_CHAR	TO_CHAR is used when an INTEGER or DATE value needs to be converted into String	TO_CHAR (sysdate, 'MM/DD/YY')	CAST or CONVERT	CONVERT (char, getdate() , 1) returns date in MM/DD/YY format.
TO_DATE	Any given string in a valid date format can be converted into DATE Datatype	TO_DATE ('12-MAY-2003','DD-MON-YYYY')	CAST	CAST ('12-MAY-2003 12:00:00' as DATETIME)

The CONVERT function is used in SQL Server to change the date format. This is equivalent to the TO_CHAR function of Oracle. CONVERT has three parameters. The first parameter is datatype. The second parameter is for the column to be formatted. The third parameter is the desired format. Please see the list of formats and the corresponding formula in Table 11.16.

Table 11.16: CONVERT Function Formats in SQL Server

WITH CENTURY (YYYY)	INPUT/OUTPUT
0 OR 100 (*)	MON DD YYYY HH:MIAM (OR PM)
101	MM/DD/YY
102	YY.MM.DD
103	DD/MM/YY
104	DD.MM.YY
105	DD-MM-YY
106	DD MON YY
107	MON DD, YY
108	HH:MM:SS
9 OR 109 (*) DEFAULT + MILLISECONDS	MON DD YYYY HH:MI:SS:MMMAM (OR PM)
110	MM-DD-YY
111	YY/MM/DD
112	YYMMDD
13 OR 113 (*)	DD MON YYYY HH:MM:SS:MMM(24H)
114	HH:MI:SS:MMM(24H)
20 OR 120 (*)	YYYY-MM-DD HH:MI:SS(24H)
21 OR 121 (*)	YYYY-MM-DD HH: MI: SS.MMM (24H)
130*	DD MON YYYY HH:MI:SS:MMMAM
131*	DD/MM/YY HH:MI:SS:MMMAM

Conditional Functions

Conditional functions are used to compare values or to evaluate a Boolean expression. Table 11.17 compares these functions between Oracle and SQL Server.

Table 11.17: Conditional Functions in Oracle and SQL

Oracle Function	Description	Oracle Example	SQL Server Function	SQL Server Example
NVL	To return a default value if the expression is null	NVL (SALARY, 0) returns 0 if the column value (SALARY column) is null	ISNULL	ISNULL (SALARY, 0) returns 0 if the column value (SALARY column) is null
NVL2	To return a value if the expression is null or null	NVL2 (Salary, Salary*2, 0) returns two times the salary if not null and 0 if null	CASE	CASE SALARY WHEN null THEN 0 ELSE SALARY*2 END
NULLIF	Returns a null value if the two specified expressions are equivalent	NULLIF (SYSDATE, SYSDATE) returns NULL	NULLIF	NULLIF (GETDATE (), GETDATE ()) returns NULL
DECODE	Used to evaluate the values with "if-else" logic.	DECODE(DISCONTINUED, 0, 'No', 1, 'Yes', 'NA')	CASE	CASE DISCONTINUED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'NA' END
CASE	Used to evaluate the values with "if-else" logic.	CASE discontinued WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'NA' END	CASE	CASE discontinued WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'NA' END

Queries

The SELECT statement is used to query the database and retrieve data. SELECT can be combined with some DDL and DML statements to perform relational operations. The following different clauses commonly used with SELECT in Oracle and SQL Server are discussed in the remainder of this section.

Simple queries
Joins
Database links
Group by
Case
Set operators
Rownum

Optimizer hints are not covered in this guidance. Both Oracle and SQL Server use cost-based optimizers and offer hints that can be used to influence the optimizer. The optimizer hints used with Oracle are not available in SQL Server. For the various types of hints available in SQL Server refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8upf.asp.

Simple Queries

A simple query is defined as a SELECT statement that retrieves data from a single table with or without filter condition. Because this is a standard command, there are no differences in syntax or usage between Oracle and SQL Server. The following example is a valid statement for both Oracle and SQL Server.

SELECT * FROM CUSTOMER;

Joins

Joins play a major role when meaningful information needs to be retrieved from more than one table. Often, reports or intricate queries require data from two or more tables. The data from multiple tables must be logically related. SQL combines data from multiple tables using joins into a single result set.

Joins can be of following types:

Inner join
- Equi-join
- Non-equi join
Outer join
- Left outer join
- Right outer join
- Full outer join
Self-join

Inner Join

Inner join is the normal join that is performed among tables to fetch the matching data. Inner join can be either an equi-join or non-equi join. Both equi and non-equi join are explained under the following headings.

Equi-Join

An equi-join equates fields from two or more tables to fetch matching data from the tables selected. There is considerable difference in the syntax of an equi-join between Oracle and SQL Server.

The standard Oracle equi-join syntax is shown in the following example:

SELECT TABLE1.FIELD1, TABLE2.FIELD1, TABLE2.FILED2
FROM TABLE1, TABLE2
WHERE TABLE1.FIELD3 = TABLE2.FIELD3
AND TABLE1.FIELD4 = TABLE2.FIELD4

Oracle 9i equi-joins are slightly different; the syntax is modified to comply with the ANSI standard. The Oracle 9i syntax is shown in the following example:

SELECT TABLE1.FIELD1, TABLE2.FIELD1, TABLE2.FIELD2
FROM (TABLE1 INNER JOIN TABLE2 ON TABLE1.FIELD3 = TABLE2.FIELD3
AND TABLE1.FIELD4 = TABLE2.FIELD4)

The SQL Server syntax is also slightly different, and it is shown in the following example:

SELECT TABLE1.FIELD1, TABLE2.FIELD1, TABLE2.FIELD2
FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.FIELD3_=_TABLE2.FIELD3
AND TABLE1.FIELD4 = TABLE2.FIELD4

For example, a company needs to create a report with the following information:

Product name
Price
Supplier’s company name
Category name

For a product produced by the example company, the corresponding supplier’s company name and the corresponding category name have to be displayed. The PRODUCT table has SUPPLIERID and CATEGORYID, but not their corresponding names. Procedures for using the PRODUCTNAME for Oracle, Oracle 9i, and SQL Server are shown in the following examples.

In Oracle, the inner join will provide the needed information:

SELECT A.PRODUCTNAME, A.UNITPRICE, B.COMPANYNAME, C.CATEGORYNAME 
FROM PRODUCT A, SUPPLIERS B, CATEGORY C 
WHERE A.SUPPLIERID = B.SUPPLIERID 
AND A.CATEGORYID = C.CATEGORYID

The following code produces the same inner join in Oracle 9i:

SELECT A.PRODUCTNAME, A.UNITPRICE, B.COMPANYNAME, C.CATEGORYNAME 
FROM (PRODUCT A INNER JOIN SUPPLIERS B 
ON A.SUPPLIERID = B.SUPPLIERID INNER JOIN CATEGORY C 
ON A.CATEGORYID = C.CATEGORYID
)

The SQL Server syntax is the same as the preceding Oracle 9i syntax.

Self Joins

Self joins can be used when one row in a table relates itself with other rows in the same table. That is, the same table is used twice for comparison. The same table must be exhibited as two different tables by using different aliases. This function is available in Oracle and SQL Server, though there are slight differences in syntax. The syntax for Oracle self joins is provided here:

SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
FROM TABLE1 A, TABLE1 B 
WHERE A.FIELD1 = B.FIELD1 
AND A.FIELD2 < B.FIELD2

The SQL Server syntax is shown in the following example. Note the differences in the third and fourth lines of the statement.

SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
FROM TABLE1 A JOIN TABLE1 B 
ON A.FIELD1 = B.FIELD1 
WHERE A.FIELD2 < B.FIELD2

Consider a case in which the result set must return a list of all companies in the same city as the company “ABC Inc.”

In Oracle, the statement would appear as:

SELECT B.COMPANYNAME 
FROM CUSTOMER A, CUSTOMER B 
WHERE A.CITY = B.CITY 
AND A.COMPANYNAME = 'ABC Inc'

In SQL Server, the same result set can be returned using the following statement:

SELECT B.COMPANYNAME 
FROM CUSTOMER A JOIN CUSTOMER B 
ON A.CITY = B.CITY 
WHERE A COMPANYNAME = 'ABC Inc'

Non-Equi Joins

Non-equi joins are used when there is not a direct link criterion between the tables. It is simply a join where the joining criteria are anything other than “=.” The difference in the syntax between Oracle and SQL Server in cases of non-equi join is a minor difference in the operators used to notify the non-equi join. The Oracle syntax is provided in the following example:

SELECT A.FIELD1, A.FIELD2, A.FIELD3, B.FIELD4
FROM TABLE1 A, TABLE2 B 
WHERE A.FIELD1 = B.FIELD1
AND A.FIELD2 <> B.FIELD2

The SQL Server syntax is provided in the following example. Note the use of the inner join operator.

SELECT A.FIELD1, A.FIELD2, A.FIELD3, B.FIELD4
FROM TABLE1 A INNER JOIN TABLE2 B 
ON A.FIELD1 = B.FIELD1
WHERE A.FIELD2 <> B.FIELD2

For example, imagine a case where there is a need to retrieve those products listed under the same category but from different suppliers. In Oracle, the statement can be created as follows:

SELECT A.PRODUCTID, A.PRODUCTNAME, A.UNITPRICE
FROM PRODUCT A, PRODUCT B 
WHERE A.CATEGORYID = B.CATEGORYID
AND A.SUPPLIERID <> B.SUPPLIERID

The same result can be accomplished in SQL Server, as shown in the following example:

SELECT A.PRODUCTID, A.PRODUCTNAME, A.UNITPRICE
FROM PRODUCT A INNER JOIN PRODUCT B 
ON A.CATEGORYID = B.CATEGORYID 
AND A.SUPPLIERID <> B.SUPPLIERID

Outer Joins

A join is termed as outer join when the result set contains all the rows from one table and only the matching rows from the other table.

An outer join can be a left outer join or a right outer join. Outer join syntax in Oracle and SQL Server remained different until the release of Oracle 9i. Thereafter, both follow ANSI standards of using INNER JOIN or OUTER JOIN in words instead of operators.

All rows are retrieved from the left table referenced with a left outer join. Here, the outer join operator (+) is placed beside the table that is to the left of the "=" operator during the join.

Oracle — left outer join

The syntax for left outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
```

FROM TABLE1 A, TABLE1 B WHERE A.FIELD1(+) = B.FIELD1

Example for left outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A, SUPPLIERS B WHERE A.SUPPLIERID(+) = B.SUPPLIERID

Oracle 9i — left outer join

The ANSI syntax for left outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
```

FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
ON A.FIELD1 = B.FIELD1

Example for ANSI compliant left outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A LEFT OUTER JOIN SUPPLIERS B ON A.SUPPLIERID = B.SUPPLIERID

SQL Server — left outer join

The syntax for left outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
```

FROM TABLE1 A LEFT OUTER JOIN TABLE2 B
ON A.FIELD1 = B.FIELD1

Example for left outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A LEFT OUTER JOIN SUPPLIERS B ON A.SUPPLIERID = B.SUPPLIERID

All rows are retrieved from the right table referenced in a right outer join. A right outer join is used to furnish null rows to the right side table used during the Join condition.

Oracle — right outer join

The syntax for right outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2 
```

FROM TABLE1 A, TABLE1 B
WHERE A.FIELD1 = B.FIELD1(+)

Example for right outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A, SUPPLIERS B WHERE A.SUPPLIERID = B.SUPPLIERID(+)

SQL Server — right outer join

The syntax for right outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2
```

FROM TABLE1 A RIGHT OUTER JOIN TABLE2 B ON A.FIELD1 = B.FIELD1

Example for right outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A RIGHT OUTER JOIN SUPPLIERS B ON A.SUPPLIERID = B.SUPPLIERID

All rows from both tables are returned in a full outer join.

Oracle — full outer join

Oracle does not have a separate outer join syntax. The following shows an example for full outer join:
```
SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME
```

FROM PRODUCT A, SUPPLIERS B WHERE A.SUPPLIERID = B.SUPPLIERID(+) UNION SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME FROM PRODUCT A, SUPPLIERS B WHERE A.SUPPLIERID(+) = B.SUPPLIERID

SQL Service — full outer join

The syntax for full outer join is as follows:
```
SELECT A.FIELD1, A.FIELD2, B.FIELD1, B.FIELD2
```

FROM TABLE1 A FULL OUTER JOIN TABLE2 B ON A.FIELD1 = B.FIELD1

Example for full outer join is:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT A.PRODUCTNAME, A.CATEGORYID, B.COMPANYNAME, B.CONTACTNAME

FROM PRODUCT A FULL OUTER JOIN SUPPLIERS B ON A.SUPPLIERID = B.SUPPLIERID

Subqueries

A subquery is a normal query that is nested inside another query using parentheses. Subqueries are used to retrieve data from tables that depend on the values on the same table or a different table. A statement containing a subquery is called a parent statement.

A subquery can be commonly found in the FROM and WHERE clauses of a SELECT statement. A subquery in the FROM clause is also called an inline view. Inline views in SQL Server are similar to those in Oracle. In SQL Server, inline views have to be aliased in all cases.

Subqueries found in the WHERE clauses are called nested queries. Subqueries may be nested in other subqueries. T-SQL queries can use a maximum of 256 tables, including all subqueries, and a maximum of 32 levels of nesting. In contrast, Oracle allows 255 levels of nesting. It is uncommon to use more than two or three levels of nesting in queries. SQL Server supports most implementations of the nested query in Oracle. Examples of nested queries are found throughout this chapter. SQL Server, however, does not support the ordered set as shown in the following example:

SELECT productid, revisedprice
FROM OrderPrice
WHERE (orderid, productid) IN (SELECT orderid, productid
             FROM OrderDetails)

In such cases, the query can be converted into one using joins instead of the nested query.

SELECT OP.productid, OP.revisedprice
FROM OrderPrice OP INNER JOIN OrderDetails OD
ON OP.orderid = OD.orderid
AND OP.productid = OD.productid

A nested query in which the nested query references columns in the parent table is called a correlated subquery. A correlated subquery is executed once for each row in the parent statement. Because Oracle syntax and SQL Server syntax are similar, examples are not provided here.

Grouping Result Set

Oracle and SQL Server provide a method of grouping the result set using the GROUP BY clause. The GROUP BY clause summarizes the result set into the groups defined in the query using aggregate functions such as AVG or SUM. Using the HAVING clause can further filter the result set by comparing it with the aggregate function's result. The HAVING clause without a GROUP BY clause behaves like a simple WHERE condition. HAVING clauses are used with aggregation functions and cannot be used with single row functions.

The GROUP BY clause treats the HAVING clause in the same way as SELECT treats WHERE.

A SELECT statement with a GROUP BY clause cannot have a COLUMN_NAME other than the columns available in the GROUP BY list. When GROUP BY is specified, either each column in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must match exactly the select list expression.

A GROUP BY clause can include an expression as long as it does not include any aggregate function, and it cannot contain any sub query, either. In SQL Server, GROUP BY “ALL” can be used, but it is optional. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition.

Because Oracle and SQL Server SQL are similar, examples are not provided here.

Database Link

When tables are to be joined across databases that are in different servers, the database link functionality can be used. In the examples provided in this section, the databases are distributed in two geographical locations. One database is in New York, and another is in Boston. A business user wants to know the total number of orders for the month of May, 2004.

In Oracle, databases are linked using the command CREATE DATABASE LINK. The TNSNAMES.ORA entry for the database is required.
```
SELECT COUNT(*) 
```

FROM
( SELECT ORDERID FROM ORDERMASTER
WHERE orderdate > sysdate - 30 UNION SELECT ORDERID FROM ORDERMASTER@BOSTON WHERE ORDERDATE > sysdate - 30 );

In SQL Server, some configuration is needed before accessing remote databases. Before querying tables from different databases on different servers, log into the first database, go to Query Analyzer, and execute the following code.
```
USE MASTER 
```

GO EXEC SP_ADDLINKEDSERVER
@SERVER= 'BOSTON', @SRVPRODUCT= '', @PROVIDER='SQLOLEDB', @DATASRC='BOSTON\NWIND' GO
EXEC SP_ADDLINKEDSRVLOGIN
@RMTSRVNAME ='BOSTON', @USESELF='FALSE', @LOCALLOGIN=NULL, @RMTUSER ='ABC', @RMTPASSWORD ='ABC' GO

Executing these procedures establishes the link between the servers. Next, execute the following query to retrieve the data from two databases.

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SELECT COUNT(ORDERID)

FROM
(SELECT ORDERID FROM ORDERMASTER
WHERE ORDERDATE > getdate() - 30 UNION SELECT ORDERID FROM [BOSTON].NWIND.DBO.ORDERMASTER
WHERE ORDERDATE > getdate() - 30 )

CASE

CASE is used when there is a need to have "if-else" logic. In the example code that appears in this section, a store manager wants to see customer names and segregate the orders into the three following categories:

An order value less than $5,000 is small
An order value between $5,000 and $10,000 is medium
An order value greater than $10,000 is large

In Oracle, this task would be accomplished using CASE as shown in the following code example:

SELECT ORDERID,
CASE 
WHEN SUM(UNITPRICE) <5000 THEN 'SMALL'
WHEN SUM(UNITPRICE) <= 10000 THEN 'MEDIUM'
ELSE 'LARGE'
END
FROM ORDERDETAILS 
GROUP BY ORDERID;

SQL Server supports the simple and searched case statement syntax found in Oracle.

Set Operators: Union

The union operator in a Data Query Language will fetch the distinct rows across the tables. This is used to combine results of two or more queries. If there is more than one row for the same value, the value will be taken only once. The union all operator provides the same functionality as the union operator but does not eliminate duplicate rows.

In the following sample code, the head office wants a list of supplier names from all the databases. Apparently, the supplier list is in different databases that are in different geographical locations. Hence, the set operators are combined with a database link. The difference between the Oracle and SQL Server code is shown in the following examples:

Oracle
```
SELECT COMPANYNAME 
```

FROM SUPPLIERS@NEWYORK UNION SELECT COMPANYNAME FROM SUPPLIERS@BOSTON;

SQL Server
```
SELECT COMPANYNAME 
```

FROM [NEWYORK].NWIND.DBO.SUPPLIERS UNION SELECT COMPANYNAME FROM [BOSTON].NWIND.DBO.SUPPLIERS

Set Operators: Intersect

The intersect operator will only retrieve the common values from more than one table. In the following example code, intersect is used to retrieve the supplier names common to both NEWYORK and BOSTON stores.

Oracle

SELECT COMPANYNAME FROM SUPPLIERS@BOSTON

INTERSECT SELECT COMPANYNAME FROM SUPPLIERS@NEWYORK

SQL Server

In SQL Server, a subquery with an EXISTS clause can be written for the same purpose.
```
SELECT COMPANYNAME FROM [NEWYORK].NWIND.DBO.SUPPLIERS A 
```

WHERE EXISTS (SELECT 'X' FROM [BOSTON].NWIND.DBO.SUPPLIERS B WHERE A.COMPANYNAME = B.COMPANYNAME);

Set Operators: Minus

A minus operator is used when the difference of values from more than one table needs to be fetched. In the following example code, this operator is used to list the supplier names that are only in the BOSTON database and that are not in NEWYORK database.

Oracle

SELECT COMPANYNAME FROM SUPPLIERS@BOSTON

MINUS SELECT COMPANYNAME FROM SUPPLIERS@NEWYORK;

SQL Server

Because MINUS is not available in SQL Server, NOT EXISTS can be used. The following example shows how to use the NOT EXISTS operator.
```
SELECT COMPANYNAME FROM SUPPLIERS A 
```

WHERE NOT EXISTS (SELECT 'X' FROM [BOSTON].NWIND.DBO.SUPPLIERS B WHERE A.COMPANYNAME = B.COMPANYNAME);

ROWNUM

ROWNUM is a special keyword in Oracle. Using ROWNUM can limit the number of rows retrieved without any condition. In the following example, ROWNUM is used to display the top 10 orders from the ORDERDETAILS table.

Oracle
```
SELECT * 
```

FROM (SELECT ORDERID, SUM(UNITPRICE) FROM ORDERDETAILS GROUP BY ORDERID ORDER BY SUM(UNITPRICE) DESC ) WHERE ROWNUM < 11;

SQL Server
```
SELECT TOP 10 ORDERID, SUM(UNITPRICE) 
```

FROM ORDERDETAILS GROUP BY ORDERID ORDER BY SUM(UNITPRICE) DESC

Data Manipulation Language (DML)

DML includes INSERT, UPDATE, and DELETE. The MERGE statement has also been discussed. The following sections describe how each command is used in Oracle and how the command can be converted into SQL Server-specific syntax.

Insert

The INSERT command is used to add one or more rows to a table. The column values are separated by commas. Insert can be used in several different ways within Oracle. All of the different forms are explained under the following headings.

Inserting a row into a table is identical in SQL Server and in Oracle. The syntax is shown here:

INSERT INTO TABLE1 VALUES (FIELD1, FIELD2 ...);

Insert Sequences

Compatibility between the Oracle tables and the new SQL Server tables should be checked. If a table in Oracle uses SEQUENCES, then SQL Server tables should use IDENTITY.

IDENTITY will automatically increment a column value so that no two rows will have same value for the column. To enable this facility, a column can be included in the table which will have its unique value and auto-incrementing defined. This is achieved through SEQUENCES in Oracle and IDENTITY in SQL Server. This column can also be used as primary key for tables that do not have a natural key.

A sequence is a database object that can generate unique, sequential integer values. It can be used to automatically generate primary key or unique key values. A sequence can be either in ascending or descending order. The syntax is explained in the following examples.

Oracle
```
INSERT INTO SUPPLIERS VALUES
```

(SUPPLIER_SEQUENCE.NEXTVAL,' Abc Inc.', 'Lingerfelt, Steve', 'Mr.', 'Apt # 104 Andrew''s Drive', 'Charlotte', 28262,'USA',7049654371,7049652300);

SQL Server

SQL Server has a unique way of handling running serial numbers: the IDENTITY property. This property provides a unique incremental value for the column. Its use in an insert command is explained later in this section. This is equivalent to Oracle's Sequence. When a column is declared as IDENTITY, a value does not have to be specified for the column. In Oracle, the value has to be explicitly retrieved from a sequence during an insert.

The current value of an IDENTITY can be found using the following code:
```
SELECT IDENT_CURRENT ('SUPPLIERS') 
```

INSERT INTO SUPPLIERS VALUES (' Abc Inc.', 'Lingerfelt, Steve', 'Mr.', 'Apt # 104 Andrew''s Drive', 'Charlotte', 28262,'USA','7049654371','7049652300');

Using Date Values in Insert Operations

During an insert or update, the date format can be the same as Oracle (DD-MON-YYYY). However, while retrieving, the format will be different. A conversion function that was discussed in the "Functions"

section will have to be used. In Oracle, to insert the date with time, a conversion of the date with date time is needed. This is done with the help of the TO_CHAR function. This is not required in SQL Server because it already stores the date with time stamp.

The following example illustrates how to insert the current date in an insert operation of Oracle and SQL Server.

Oracle

INSERT INTO OrderMaster VALUES(1,1076,121,SYSDATE,'19-AUG-2004', NULL, 'Federal Shipping', 40,'Ernst Handel','2817 Milton Dr.','Dallas','TX','87110')

SQL Server

INSERT INTO OrderMaster VALUES(1,1076,121, GETDATE(),'19-AUG-2004', NULL, 'Federal Shipping', 40, 'Ernst Handel','2817 Milton Dr.','Dallas','TX','87110')

The following example shows how the time part of a date is handled in Oracle and how this can be converted for SQL Server database.

Oracle

INSERT INTO OrderMaster VALUES(1,1076,121,TO_DATE('01-MAR-2004 13:45:33', 'DD-MON-YYYY

HH24:MI:SS'),'19-AUG-2004', NULL, 'Federal Shipping', 40, 'Ernst Handel','2817 Milton Dr.','Dallas','TX','87110')

SQL Server

INSERT INTO OrderMaster VALUES(1,1076,121, '01-MAR-2004 13:45:33','19-AUG-2004', NULL, 'Federal Shipping', 40, 'Ernst Handel','2817 Milton Dr.','Dallas','TX','87110')

Insert with Subquery

In many applications, a subquery is used to return a specific subset of rows which are then inserted into the table. The subquery can refer to any table, view, or even the target table itself. If the subquery returns no rows, then no rows are inserted into the table.

Update

Update is an imperative DML statement that facilitates the changes to values of fields in the tables. In an Oracle or SQL Server database, an UPDATE is commonly used to incorporate modifications to an already inserted value. All of the key constraints are handled by the databases prior to affecting the UPDATE permanently. Oracle and SQL Server have followed the conventional ANSI way of using the UPDATE statement.

The UPDATE statement can be performed in three different ways:

Simple update. This is a straightforward change to the values of one or more fields of a table, with or without a condition for updating.

Oracle and SQL Server use the same syntax for UPDATE statement.
Update with joins. Updates with joins are frequently used when a change is required in the field value of only the rows matching specific conditions between two or more tables.

Oracle and SQL Server use the same syntax for UPDATE statement.
Update with subqueries. Requirements will sometimes demand a change to a field value with data from another table. Update with subqueries is useful in situations where data from one table is set to a field from another table.

Oracle and SQL Server use the same syntax conventions for UPDATE with subqueries. An exception is updates involving correlated subqueries.
```
UPDATE OrderDetails o
```

SET o.unitprice = (SELECT unitprice FROM Product p WHERE p.productid = o.productid)

The preceding Oracle update statement can be rewritten using a syntax that is unique to SQL Server as follows:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">UPDATE o

SET o.unitprice = p.unitprice FROM OrderDetails o JOIN Product p ON p.productid = o.productid

Merge

This is a new feature in Oracle 9i. The MERGE statement is used to update and insert rows in a table. In the following examples, an ORDERPRICE table exists that stores the revised price of a product in a particular order. The price can be revised any number of times. If a record already exists, then the price needs to be updated and the date changed. If a row for that order and product does not exist, then you need to insert a new row with the revised price of the product in that particular order.

Oracle
```
MERGE INTO ORDERPRICE A
```

USING (SELECT ORDERID, PRODUCTID FROM ORDERDETAILS WHERE ORDERID=1 AND PRODUCTID =1) B ON (A.ORDERID = B.ORDERID AND A.PRODUCTID = B.PRODUCTID) WHEN MATCHED THEN UPDATE SET REVISEDPRICE = 120, REVISEDON = SYSDATE WHEN NOT MATCHED THEN INSERT INTO ORDERPRICE VALUES (B.ORDERID, B.PRODUCTID, 100, SYSDATE);

SQL Server

To achieve this functionality in SQL Server, use a stored procedure with the following logic. More information about writing stored procedures is included later in this chapter in the "Subprograms Conversion" section.
```
declare @orderid char(10)
```

declare @productid char(10) declare @count integer SELECT @count = COUNT(*) FROM ORDERPRICE WHERE ORDERID= @ORDERID AND PRODUCTID = @PRODUCTID IF @count > 0 UPDATE ORDERPRICE SET REVISEDPRICE = 120, REVISEDON = GETDATE() WHERE ORDERID = @ORDERID AND PRODUCTID = @PRODUCTID ELSE INSERT INTO ORDERPRICE VALUES(@ORDERID, @PRODUCTID, 100, GETDATE())

Delete

As tables are used, unneeded rows can be removed. DELETE is a DML operation that is used to eliminate all or certain rows from a table, keeping the table structure intact. Even though all the rows are deleted, the structure of the table remains and can be populated with additional rows using INSERT. By using conditional deletion, only selected rows are eliminated from the table. Conditional deletion is accomplished by using the conventional WHERE clause. Oracle and SQL Server both allow for conditional deletion. The ANSI way of writing the DELETE statement is used in both Oracle and SQL Server.

The following types of delete exist:

Simple delete. A general way of deleting all or selected rows from a table. A simple delete operation is similar for Oracle and SQL Server.
Delete all rows. Both Oracle and SQL Server offer the TRUNCATE TABLE statement.
Delete with subqueries. Using subqueries in conjunction with the DELETE operation allows for more specificity. The subquery enables the value for the WHERE condition to be fetched from a different table.

Oracle and SQL Server use the same methodology to delete the rows in a table.
Delete duplicate records. It is possible to have exact copies of rows in a table that does not enforce a strict key structure. These situations demand the deletion of all the duplicate records from the table. Oracle and SQL Server have different ways to do this:
- Oracle
```
DELETE FROM TABLE1 WHERE TABLE1.ROWID > 
```

(SELECT MIN (TABLE2.ROWID) FROM TABLE2 WHERE TABLE1.FIELD1 = TABLE2.FIELD1)

  - SQL Server
    
    For an example of how to perform this in SQL Server, refer to <https://support.microsoft.com/default.aspx?scid=kb;en-us;139444>

Truncate

Both Oracle and SQL Server offer the TRUNCATE command for deleting all rows of a table with minimal logging.

Step 2: Transaction Management

After identifying all instances of SQL, transactions need to be carefully modified as part of the data migration process. This section discusses how to handle the transactions in SQL Server.

To begin, identify all of the transaction commands currently used with Oracle and implement them for SQL Server. In Oracle, all commands inside two transaction control language statements are considered as a batch. If a set of statements needs to be considered as a batch, then all the statements must be inside BEGIN TRANSACTION and COMMIT TRANSACTION commands. A discussion of these architecture topics is provided in Appendix A: SQL Server for Oracle Professionals.

Transaction Control Language (TCL)

A transaction can be defined as a group of statements that must either succeed or fail as a group. In Oracle, transactions are normally implicit in nature and can be explicitly specified by use of BEGIN and END clauses. In SQL Server, BEGIN TRAN and END TRAN have to be explicitly used to demarcate a transaction.

Transaction is controlled by any one of these commands:

COMMIT
ROLLBACK
SAVEPOINT

These commands are discussed in detail under the following headings.

COMMIT

The COMMIT command is used to end a transaction. Changes are made permanent in the database with the COMMIT command. This erases all the savepoints inside the transaction and also releases the locks.

After a transaction is committed, the change is permanent and cannot be reverted. Of course, you can always delete the inserted row. There is no way to undo a committed transaction.

The syntax is as follows:

Oracle
```
COMMIT;
```
SQL Server
```
COMMIT TRANSACTION <TRANSACTION NAME>;
```

The COMMIT WORK command functions in the same way as COMMIT TRANSACTION, except COMMIT TRANSACTION accepts a user-defined transaction name. This COMMIT syntax, with or without specifying the optional keyword WORK, is compatible with SQL-92. This command is available in SQL Server using the following command:

COMMIT WORK;

SAVEPOINT

A savepoint is a marker that divides a very lengthy transaction into several smaller ones. SAVEPOINT is used to identify a rollback point in the transaction. Thus, SAVEPOINT is used in conjunction with ROLLBACK to revert a portion of current transaction. The syntax in Oracle and SQL Server differs:

Oracle
```
SAVEPOINT <SAVEPOINT NAME>;
```
SQL Server
```
SAVE TRANSACTION <SAVEPOINT NAME>;
```

ROLLBACK

The ROLLBACK command is used to undo a portion of the current transaction. It is possible to roll back the entire transaction so that all changes made by SQL statements are undone, or to roll back a transaction to a save point so that the SQL statements after the save point are rolled back. The syntax in Oracle and SQL Server differs:

Oracle
```
ROLLBACK;
```

ROLLBACK TO <SAVEPOINT NAME>;

SQL Server

ROLLBACK TRANSACTION <TRANSACTION NAME>

ROLLBACK TRANSACTION <SAVEPOINT NAME>

In addition, the ROLLBACK WORK statement function is similar to the ROLLBACK. The only difference is that ROLLBACK TRANSACTION accepts a user-defined transaction name. With or without specifying the optional WORK keyword, this ROLLBACK syntax is SQL-92-compatible and is available in SQL Server using the following syntax:

ROLLBACK WORK

Step 3: Fetch Strategy

Cursors are effective for row processing and batch processing. Oracle-based applications can use many cursors for row processing. Analyze the code to determine if it can be converted without using cursors. Cursor operations are always costly when compared to SET operations because they use temporary memory area and processes. If the cursors are not handled properly, it may result in dead processes and locks.

In Oracle, a cursor is defined as a work area to execute the SQL statements and to store processing information. Oracle creates a work area to process all the SQL statements. That work area is called an implicit cursor. Oracle also allows you to define an explicit cursor to process and apply business logic.

Oracle has implemented the following types of cursors:

Implicit cursors
Explicit cursors

Implicit cursors are opened automatically whenever queries or DML are issued in Oracle. SQL Server calls this a default result set.

Oracle allows defining a cursor with the help of keyword "cursor." Such explicitly declared cursors have a SELECT statement. The output of the select is stored in separate memory areas and it can be used in a PL/SQL program. Explicit cursors can be parameterized.

Transact SQL Cursors, in SQL Server, are based on the DECLARE CURSOR syntax and are used mainly in Transact-SQL scripts, stored procedures, and triggers. Transact-SQL cursors are implemented on the server and are managed by Transact-SQL statements sent from the client to the server.

Using Transact-SQL Cursors

High-level steps for using Transact-SQL cursors inside the code include:

Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column.
Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
Use the OPEN statement to execute the SELECT statement and populate the cursor.
Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.

The cursor syntax differs between Oracle and SQL Server:

Oracle
```
DECLARE
```

CURSOR <CURSOR_NAME> IS < SELECT STATEMENT> BEGIN FOR <VARIABLE> IN CURSOR_NAME LOOP -- Business Logic END LOOP; END;

SQL Server
```
DECLARE <VARIABLE>
```

DECLARE <CURSOR_NAME> CURSOR FOR <SELECT STATEMENT> BEGIN OPEN <CURSOR_NAME> FETCH NEXT FROM <CURSOR_NAME> INTO <VARIABLE_NAME> WHILE @@FETCH_STATUS = 0 BEGIN -- business logic goes here FETCH NEXT FROM <CURSOR NAME> INTO <VARIABLE NAME> END CLOSE <CURSOR_NAME> DEALLOCATE <CURSOR_NAME> END

Oracle allows the entire cursor record to be transferred into a user-defined data structure defined using ROWTYPE. In SQL Server, the record values need to be assigned into an individual variable. The following example uses ROWTYPE to process the SHIPPERS table record by record.

Oracle
```
DECLARE
```

CURSOR SHIPPER_CURSOR IS SELECT * FROM SHIPPERS; SHIPPER_REC SHIPPERS%ROWTYPE; BEGIN OPEN SHIPPER_CURSOR; LOOP FETCH SHIPPER_CURSOR INTO SHIPPER_REC; EXIT WHEN SHIPPER_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE('SHIPPER NAME IS '||SHIPPER_REC.COMPANYNAME); DBMS_OUTPUT.PUT_LINE('SHIPPER PHONE IS '||SHIPPER_REC.PHONE); END LOOP; CLOSE SHIPPER_CURSOR; END;

SQL Server

DECLARE @SHIPPER_NAME VARCHAR(50),@PHONE VARCHAR(50)

DECLARE SHIPPER_CURSOR CURSOR FOR SELECT COMPANYNAME,PHONE FROM SHIPPERS; BEGIN OPEN SHIPPER_CURSOR; FETCH NEXT FROM SHIPPER_CURSOR INTO @SHIPPER_NAME, @PHONE WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'SHIPPER NAME IS ' + @SHIPPERNAME PRINT 'SHIPPER PHONE IS ' + @PHONE FETCH NEXT FROM SHIPPER_CURSOR INTO @SHIPPER_NAME, @PHONE END CLOSE SHIPPER_CURSOR DEALLOCATE SHIPPER_CURSOR END

Transact SQL Extensions

SQL Server extends the following functionality with cursors. Some of the key features include:

FETCH FIRST

Fetches the first row in the cursor.
FETCH NEXT

Fetches the row after the last row fetched.
FETCH PRIOR

Fetches the row before the last row fetched.
FETCH LAST

Fetches the last row in the cursor.
FETCH ABSOLUTE n

Fetches the nth row from the first row in the cursor if n is a positive integer. If n is a negative integer, the row n rows before the end of the cursor is fetched. If n is 0, no rows are fetched.
FETCH RELATIVE n

Fetches the nth row from the current position of the cursor. If n is positive, the nth row after the last row fetched is fetched. If n is negative, the nth row before the last row fetched is fetched. If n is 0, the same row is fetched again. It also allows retrieving one row or block of rows from the current position in the result set.

It supports data modifications to the rows at the current position in the result set.

Step 4: Subprograms Conversion

The next step is to migrate the procedures, functions, and triggers. This section discusses how each component can be converted for use with SQL Server.

Migrating Procedures

A stored procedure is a precompiled object. This means that a procedure is compiled beforehand and is readily available for the various applications to execute. As a result, time is not wasted parsing and compiling the procedure again. However, the procedure will be compiled every time if it contains dynamic SQL. Stored procedures in SQL Server are very similar to procedures in Oracle PL/SQL. Some similarities include:

Both can accept parameters and return multiple values in the form of output parameters (OUT parameter) to the calling procedure.
Both contain programming statements that perform operations in the database, including calling other procedures.
Both can overwrite existing procedures. Oracle uses the keyword REPLACE, while SQL Server uses ALTER.

The syntax between Oracle and SQL Server procedures differs. The differences are shown in the following examples:

Oracle

CREATE OR REPLACE PROCEDURE <PROCEDURE_NAME>

(PARAMETER1 DATATYPE [IN OUT], PARAMETER2 DATATYPE [IN OUT],) IS VARIABLE1 DATATYPE; VARIABLE2 DATATYPE; BEGIN EXECUTABLE STATEMENTS; EXCEPTION EXECUTABLE STATEMENTS; END;

SQL Server

CREATE PROCEDURE <PROCEDURE_NAME> [ ; number ]

[ { @PARAMETER DATATYPE } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] AS EXECUTABLE STATEMENTS

In the following example, a stored procedure calculates the number of months remaining until retirement for each employee. Retirement age is passed as a parameter to the procedure. The output needs to be written into a table named RETIRE. The RETIRE table has two columns. The columns are EMPLOYEEID and MONTHS_REMAINING. The number of months remaining is calculated and inserted into this table. Before starting the operation, the table needs to be flushed.

Oracle

CREATE OR REPLACE PROCEDURE CALCULATE_RETIRE_MONTHS(RETIRE_AGE NUMBER) IS

CURRENT_AGE NUMBER; MONTHS_REMAINING NUMBER; CURSOR EMP_CURSOR IS SELECT EMPLOYEEID, BIRTHDATE FROM EMPLOYEE; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE RETIRE'; FOR CUR_VAL IN EMP_CURSOR LOOP CURRENT_AGE := ROUND(ROUND(SYSDATE - CUR_VAL.BIRTHDATE)/30) MONTHS_REMAINING := (RETIRE_AGE * 12) - CURRENT_AGE; INSERT INTO RETIRE VALUES(CUR_VAL.EMPLOYEE_ID,MONTHS_REMAINING); END LOOP; COMMIT; END;

SQL Server
```
IF EXISTS (SELECT NAME FROM SYSOBJECTS
```

WHERE NAME = 'CALCULATE_RETIRE_YRS' AND TYPE = 'P') DROP PROCEDURE CALCULATE_RETIRE_YRS GO CREATE PROCEDURE CALCULATE_RETIRE_YRS @RETIRE_AGE INTEGER AS DECLARE @CUR_AGE INTEGER,@MONTHS_REMAINING INTEGER DECLARE @V_DOB DATETIME,@V_EMPLOYEEID VARCHAR(30) DECLARE EMP_CURSOR CURSOR FOR SELECT EMPLOYEEID,BIRTHDATE FROM EMPLOYEE BEGIN TRUNCATE TABLE RETIRE; OPEN EMP_CURSOR FETCH NEXT FROM EMP_CURSOR INTO @V_EMPLOYEEID,@V_DOB IF @@FETCH_STATUS <> 0 BEGIN PRINT 'NO EMP' END ELSE BEGIN WHILE (@@FETCH_STATUS = 0) BEGIN SET @CUR_AGE = ( SELECT DATEDIFF(MONTH,@V_DOB,GETDATE()) ) SET @MONTHS_REMAINING = (SELECT (@RETIRE_AGE * 12) - @CUR_AGE) INSERT INTO RETIRE VALUES (@V_EMPLOYEEID,@MONTHS_REMAINING) FETCH NEXT FROM EMP_CURSOR INTO @V_EMPLOYEEID,@V_DOB END END CLOSE EMP_CURSOR DEALLOCATE EMP_CURSOR END

Functions

While similar to procedures, functions return a value. There may be some mathematical calculation needed for the application that is readily available in the database server. If you need the result of a complex algorithm as part of a SQL statement, you need to write a function that performs the required calculation and returns a value.

The syntax between Oracle and SQL Server functions differs, as shown in the following examples:

Oracle

CREATE OR REPLACE FUNCTION <FUNCTION_NAME>

(PARAMETER DATATYPE, PARAMETER DATATYPE ETC ..) RETURN DATATYPE IS <DECLARATIONS> BEGIN <FUNCTION BODY> RETURN <DATATYPE> EXCEPTION <EXCEPTION HANDLERS>; END;

SQL Server

CREATE FUNCTION <FUNCTION_NAME> @PARAMETER DATATYPE,@PARAMETER DATATYPE ETC .. RETURNS <DATATYPE> AS

BEGIN <FUNCTION BODY> RETURN <DATATYPE> END

The difference between built-in functions in Oracle and SQL Server are discussed here. Oracle also has a function called NEXT_DAY that returns the date of the first weekday named by char that is later than the current date. The char argument must be a day of the week in the date language of the session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored and the return value has the same hours, minutes, and seconds component as the argument date.

By default, this function is not available in SQL Server. But the following example shows one way to provide this function in SQL Server, and it can be expanded to meet your needs:

CREATE FUNCTION NEXT_DAY(@D DATETIME,@DAY VARCHAR(10)) RETURNS DATETIME AS 
BEGIN
DECLARE @DAY_NUMBER INTEGER
DECLARE @NEWDATE DATETIME
IF UPPER(@DAY)='SUNDAY' 
BEGIN
    IF ( 1 - DATEPART(DW,@D))=0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 1 - DATEPART(DW,@D)) < 0
SET @NEWDATE = @D + 1 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 1 - DATEPART(DW,@D)
END
ELSE IF UPPER(@DAY)='MONDAY' OR UPPER(@DAY)= ‘MON’
BEGIN
    IF ( 2 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 2 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 2 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 2 - DATEPART(DW,@D)
END
ELSE IF UPPER(@DAY)='TUESDAY' OR UPPER(@DAY)= ‘TUE’
BEGIN
    IF ( 3 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 3 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 3 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 3 - DATEPART(DW,@D)
END
ELSE IF UPPER(@DAY)='WEDNESDAY' OR UPPER(@DAY)= ‘WED’
BEGIN
    IF ( 4 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 4 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 4 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 4 - DATEPART(DW,@D)
END
ELSE IF UPPER(@DAY)='THURSDAY' OR UPPER(@DAY)= ‘THU’
BEGIN
    IF ( 5 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 5 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 5 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 5 - DATEPART(DW,@D)END
ELSE IF UPPER(@DAY)='FRIDAY' OR UPPER(@DAY)= ‘FRI’
BEGIN
    IF ( 6 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 6 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 6 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 6 - DATEPART(DW,@D)END
ELSE IF UPPER(@DAY)='SATURDAY' OR UPPER(@DAY)= ‘SAT’
BEGIN
    IF ( 7 - DATEPART(DW,@D)) = 0 
        SET @NEWDATE = @D + 7
    ELSE IF ( 7 - DATEPART(DW,@D))< 0
SET @NEWDATE = @D + 7 + ( 7 - DATEPART(DW,@D))
    ELSE
        SET @NEWDATE = @D + 7 - DATEPART(DW,@D)
END
ELSE
    SET @NEWDATE = NULL
RETURN @NEWDATE
END

Triggers

Oracle and SQL Server provide two primary mechanisms for enforcing business rules and data integrity through constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when an event occurs. The event can be INSERT, UPDATE, or DELETE. The trigger and the statement that triggers are treated as a single transaction, which can be rolled back from within the trigger. If an error is detected, the entire transaction is automatically rolled back.

Oracle uses two types of triggers on tables: table-level triggers and row-level triggers. If the key word FOR EACH ROW is specified, then it is a row-level trigger. Row-level triggers are fired for each row when it is affected. For example, if an update statement changes 5 rows, then the trigger will be fired 5 times. Table-level triggers are only fired once. In a table-level trigger, individual values cannot be captured. Row-level triggers capture the individual rows with the help of the keywords :NEW and :OLD. Unlike Oracle, SQL Server does not have two types of triggers. The individual values can be captured from a special table called INSERTED. Old values can be fetched from the DELETED table and the new changed values can be retrieved from the INSERTED table.

The deleted values can be referred with the special variable :OLD in Oracle. SQL Server has a special table called DELETED. The DELETED table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the DELETED table. The DELETED and INSERTED tables in SQL Server are only conceptual; they do not physically exist.

A mapping of the functionality of triggers in Oracle and SQL Server as well as a comparison of the syntax is available under the topic "Triggers" in Chapter 6: "Developing: Databases — Migrating Schemas."

The syntax for the triggers differs as follows:

Oracle triggers

Separate syntax is used for each trigger command:
- INSERT trigger syntax
```
CREATE OR REPLACE TRIGGER <TRIGGER_NAME> 
```

[ BEFORE | AFTER ] INSERT ON <TABLE_NAME> [ FOR EACH ROW ] DECLARE BEGIN EXCEPTION END;

  - UPDATE trigger syntax
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE OR REPLACE TRIGGER &lt;TRIGGER_NAME&gt;

[ BEFORE | AFTER ] UPDATE OF <COLUMN_NAME> ON <TABLE_NAME> [ FOR EACH ROW ] DECLARE BEGIN EXCEPTION END;

  - DELETE trigger syntax
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">CREATE OR REPLACE TRIGGER &lt;TRIGGER_NAME&gt;

[ BEFORE | AFTER ] DELETE ON <TABLE_NAME> [ FOR EACH ROW ] DECLARE BEGIN EXCEPTION END;

SQL Server

Unlike Oracle, only one syntax is needed for trigger functions in SQL Server:
```
CREATE TRIGGER <TRIGGER_NAME> ON <TABLE> 
```

{FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} AS [{IF UPDATE(column)[{AND|OR}UPDATE(column)]}] EXECUTABLE STATEMENTS

The following example further illustrates the use of trigger functions. In this example, the PRODUCT tables need to be updated when an order is issued against any product. The following code uses an INSERT trigger to perform this task.

Oracle
```
CREATE TRIGGER UPDATE_STOCK 
```

AFTER INSERT ON ORDERDETAILS FOR EACH ROW BEGIN UPDATE PRODUCT SET UNITINSTOCK = UNITINSTOCK - :NEW.QUANTITY WHERE PRODUCTID = :NEW.PRODUCTID; EXCEPTION WHEN OTHERS THEN NULL; END;

SQL Server

CREATE TRIGGER UPDATE_STOCK ON ORDERDETAILS

AFTER INSERT AS BEGIN UPDATE PRODUCT SET UNITINSTOCK = UNITINSTOCK – QUANTITY FROM INSERTED WHERE PRODUCT.PRODUCTID = INSERTED.PRODUCTID END

INSTEAD OF Triggers

In general, complex views are not updateable. Complex views are defined as normal views that include more than one table or have GROUP BY expressions. Complex views can be updated using INSTEAD OF triggers. Both SQL Server and Oracle have the option of INSTEAD OF triggers. The INSTEAD OF trigger is generally defined on the view instead of on a table. In Oracle, INSTEAD OF triggers can be applied only to the view and not to tables. In SQL Server, INSTEAD OF triggers can be applied to views and tables. When an insert, update or delete statement is executed against the table, the INSTEAD OF trigger is executed in place of the triggering statement.

Also, INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables are created, but before any other actions are taken. They are executed before any constraints, so they can perform preprocessing that supplements the constraint action.

The triggering mechanism on INSTEAD OF triggers is implemented such that the trigger is not called recursively. The statement is processed as if there is no INSTEAD OF trigger defined for the table.

If an UPDATE statement is executed on a view that has an INSTEAD OF trigger, the trigger will be called, but it is not executed recursively. The UPDATE statement is resolved against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updateable view. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to underlying base tables starts the chain of applying constraints and firing AFTER triggers defined for the table.

SQL Server provides most of the functionality present in Oracle with respect to triggers with very little differences in their implementation. SQL Server, however, does not have the BEFORE triggers and can be programmed using the INSTEAD OF triggers on tables.

In SQL Server triggers on views have the same kind of features as seen on triggers on tables as shown by the following syntax:

CREATE TRIGGER <TRIGGER NAME> ON <VIEW> 
{FOR|AFTER|INSTEAD OF}
{[INSERT][,][UPDATE][,][DELETE]} AS 
BEGIN [{IF UPDATE(column)[{AND|OR}UPDATE(column)]     EXECUTABLE STATEMENTS
END

An example of the use of INSTEAD OF triggers on view is given in: https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvconusinginsteadoftriggersonviews.asp.

Error Handling

Run-time errors in Oracle's PL/SQL programs are done through exceptions and exception handlers. Exceptions are raised when errors occur. Although there are pre-defined system errors, users can also define their own errors in the declaration section using the syntax:

exception_name EXCEPTION;

The user-defined exceptions can be triggered using the syntax:

RAISE exception_name;

Each BEGIN ... END block has an associated exception handler section defined at the end whose syntax is as follows.

EXCEPTION WHEN exception_name THEN
... statements ...
END;

Although these exception handler sections are absent in SQL Server at the block level, error handling is performed through the use of the global variable @@Error, which is passed to the calling program and can be handled there. SQL Server sets the value of the @@Error variable after each TSQL statement. For example:,

INSERT ....
IF @@Error <> 0
    BEGIN
        SELECT 'Unexpected error occurred: Insert failed', @@Error
        Return @@Error
    END

Oracle has many predefined exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, INVALID_CURSOR, etc. The predefined exception OTHERS is of special interest because it can be used as a catch-all exception for any exceptions for which there is no definition in the exception section.

SQL Server error messages can be viewed by selecting sysmessages system table. Each SQL Server internal error is qualified into various severity levels. The severity of errors ranges from 1 to 25. Errors with severity 19 and above are considered critical and may result in complete abortion of the batch. In such cases, @@Error may not capture the error raised.

Similar in functionality to user-defined exceptions and the RAISE_APPLICATON_ERROR function in Oracle, SQL Server provides ways to create customized error messages that can be raised within TSQL code. These are called user-defined errors and can be raised using RAISERROR statement. If you are referencing an error number in the RAISERROR function in TSQL, you must create the user-defined message. You can also dynamically specify a text message in the RAISERROR function. This is done by using sp_addmessage system stored procedure. For more information on how to create and raise user-defined errors, see https://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_3f76.asp.

Step 5: Job Scheduling

Oracle uses application code and OS utilities to schedule a job. SQL Server uses SQL Server agent to perform these jobs. The information in this section describes creating a job and scheduling it in SQL Server.

Scheduling Functions and Jobs

Some frequently used scheduling functions that are packaged with Oracle are discussed in this section. They include:

DBMS_JOB. This is an Oracle supplied package for scheduling jobs. Several different procedures are packed inside this package. Included are:
- DBMS_JOB.INSTANCE. This procedure is used to assign a particular instance to execute a job
- DBMS_JOB.CHANGE. This is used to alter the schedule of a job.
- DBMS_JOB.SUBMIT. After the creation of job, this procedure submits a job to the Oracle instance.
- DBMS_JOB.RUN. Jobs can be executed on demand using this procedure.
cron: cron is a daemon, which means that it only needs to be started once, and will lay dormant until it is required. Generally, a SQL file is written that calls a particular procedure. This SQL file is called from a shell script and this script is scheduled using cron. This is how a job is scheduled in Oracle running on UNIX.

In SQL Server, jobs can be executed in the following ways:

Using the Enterprise Manager
Using the stored procedures SP_ADD_JOB and SP_ADD_JOBSCHEDULE

Using SQL Server Enterprise Manager

job can be scheduled by using the following steps.

To execute a job in the background on scheduled basis, SQL Server agent must be running.

Log in to Enterprise Manager.
Expand the server group and then expand the server. Expand Management. Expand SQL Server Agent and select the Jobs node.
Create a new job by right-clicking Jobs.
Enter a name for the job in the Name field.
Check the Enabled check box to make the job available for execution either immediately or by scheduling it. A disabled job runs only if a user explicitly executes it..
In the Owner list, select a user to be the owner of the job.
Write the description about the job in Description text area.
Select the Steps tab. A job step is an action that the job takes on a database or a server. Every job must have at least one job step. Job steps can be operating system commands, Transact-SQL statements, or ActiveX Scripts.
Click New to get the job step dialog box. Enter a job step name in the Job step name box. Select the type in the Type list, For example, you can click Operating system command (CmdExec).
Enter a value from 0 to 999999 in the Process exit code dialog box.
Enter the operating system command or executable program in the command box. If you select Transact-SQL statements or ActiveX scripts, you need to specify the commands as per the selection.
In the Schedules tab, the schedule can be specified. Jobs can be scheduled to run weekly, daily, or recursive.
In the Notifications tab, you can specify an e-mail address to send a notification if a job fails or succeeds. Messages can also be sent to a pager.

Using SP_ADD_JOB

Jobs in SQL Server can also be scheduled using the stored procedure SP_ADD_JOB. This simply adds a job in SQL Agent service. Job steps and scheduling can be done only with other procedures. The syntax for use and descriptions are provided in the following example:

SP_ADD_JOB [ @JOB_NAME = ] 'JOB_NAME' 
 [ , [ @ENABLED = ] ENABLED ] 
 [ , [ @DESCRIPTION = ] 'DESCRIPTION' ] 
 [ , [ @OWNER_LOGIN_NAME = ] 'LOGIN' ] 
 [ , [ @NOTIFY_LEVEL_EVENTLOG = ] EVENTLOG_LEVEL ] 
 [ , [ @NOTIFY_LEVEL_EMAIL = ] EMAIL_LEVEL ] 
 [ , [ @NOTIFY_LEVEL_NETSEND = ] NETSEND_LEVEL ] 
 [ , [ @NOTIFY_LEVEL_PAGE = ] PAGE_LEVEL ] 
 [ , [ @NOTIFY_EMAIL_OPERATOR_NAME = ] 'EMAIL_NAME' ] 
 [ , [ @NOTIFY_NETSEND_OPERATOR_NAME = ] 'NETSEND_NAME' ] 
 [ , [ @NOTIFY_PAGE_OPERATOR_NAME = ] 'PAGE_NAME' ] 
 [ , [ @DELETE_LEVEL = ] DELETE_LEVEL ] 
[ @JOB_NAME = ] 'JOB_NAME'

@JOB\_NAME is the name of the job. The name must be unique and cannot contain the percent (%) character. The commonly used parameters include:

[ @ENABLED = ] ENABLED

@ENABLED indicates the status of the added job. ENABLED has a default of 1 (enabled). If 0, the job is not enabled and does not run according to its schedule, but it can be run manually.
[ @DESCRIPTION = ] 'DESCRIPTION'

@DESCRIPTION is the description of the job. This description can be a maximum of 512 characters.
[ @OWNER\_LOGIN\_NAME = ] 'LOGIN'

@OWNER\_LOGIN\_NAME is the name of the login that owns the job.
[ @NOTIFY\_LEVEL\_EVENTLOG = ] EVENTLOG_LEVEL

@NOTIFY\_LEVEL\_EVENTLOG is a value indicating when to place an entry in the Windows® application log (Event viewer) for this job. The value can contain one of the values described in Table 11.18:

Table 11.18: Values for EVENTLOG_LEVEL for SP_ADD_JOB

Value

Description

0

Never

1

On success

2 (default)

On failure

3

Always
[ @NOTIFY\_LEVEL\_EMAIL = ] EMAIL_LEVEL

The value indicates when to send e-mail upon the completion of this job. This uses the same values as listed in Table 11.18.
[ @NOTIFY\_LEVEL\_NETSEND = ] NETSEND_LEVEL

The value indicates when to send a network message upon the completion of this job. This uses the same values as listed in Table 11.18.
[ @NOTIFY\_LEVEL\_PAGE = ] PAGE_LEVEL

The value indicates when to send a page upon the completion of this job. This uses the same values as listed in Table 11.18.
[ @NOTIFY\_EMAIL\_OPERATOR\_NAME = ] 'EMAIL_NAME'

This is the e-mail address of the person to send e-mail to when EMAIL_LEVEL is reached.
[ @NOTIFY\_NETSEND\_OPERATOR\_NAME = ] 'NETSEND_NAME'

Name of the operator to whom the network message is sent upon completion of this job.
[ @NOTIFY\_PAGE\_OPERATOR\_NAME = ] 'PAGE_NAME'

Name of the person to page upon completion of this job.
[ @DELETE\_LEVEL = ] DELETE_LEVEL

This value indicates when to delete the job. This uses the same values as listed in Table 11.18.

Value	Description
0	Never
1	On success
2 (default)	On failure
3	Always

The following example shows how to create a job from the stored procedure. This creates a job named FILE2. The job is enabled with the notification level of zero. No notifications will be created upon success or failure. Operator DBO is assigned as owner for this job.

USE MSDB
EXEC SP_ADD_JOB @JOB_NAME = 'FILE2', 
@ENABLED = 1,
@DESCRIPTION = 'FILE DUMP JOB',
@OWNER_LOGIN_NAME = 'DBO',
@NOTIFY_LEVEL_EVENTLOG = 0,
@NOTIFY_LEVEL_EMAIL = 0,
@NOTIFY_LEVEL_NETSEND = 0,
@NOTIFY_LEVEL_PAGE = 0,
@DELETE_LEVEL = 0

After the job is created, it must have some steps to execute. The steps define the function of the job. The following example runs a Data Transformation package by using the DTSRUN utility. SUBSYSTEM should be CMDEXEC to run any utility from a job.

USE MSDB
EXEC SP_ADD_JOBSTEP @JOB_NAME = 'FILE2',
@STEP_NAME = 'STEP1 IN FILE2',
@SUBSYSTEM = 'CMDEXEC',
@COMMAND = 'DTSRUN /S "(LOCAL)" /N "FILETRANSFER" /U "SA" /P "SA" ', 
@RETRY_ATTEMPTS = 5,
@RETRY_INTERVAL = 5

@SUBSYSTEM is used by the SQL Server agent service. The available values are described in Table 11.19.

Table 11.19: Values for @SUBSYSTEM for SP_ADD_JOB

Subsystem Type	Description
ACTIVESCRIPTING	Active Script
CMDEXEC	Operating-system command or executable program
DISTRIBUTION	Replication Distribution Agent job
SNAPSHOT	Replication Snapshot Agent job
LOGREADER	Replication Log Reader Agent job
MERGE	Replication Merge Agent job
'TSQL' (default)	Transact-SQL statement

@COMMAND is the actual command to execute. In the following example, the DTSRUN utility is used. Any executable can be called using @COMMAND.

Any number of steps can be added to a single job. The sequence of the steps is determined by the parameter @STEP\_ID in this procedure.

After the steps have been created, the job can be either executed manually or scheduled. If it needs to be scheduled, then SP_ADD_JOBSCHEDULE is used with a set of parameters.

This example highlights how to schedule a job FILE2 to occur everyday at 1:00 A.M. in SQL Server.

USE MSDB
EXEC SP_ADD_JOBSCHEDULE @JOB_NAME = 'FILE2', 
@NAME = 'DAILY LOAD',
@FREQ_TYPE = 4, -- DAILY
@FREQ_INTERVAL = 1,
@ACTIVE_START_TIME = 10000

@FREQ\_TYPE is the frequency type. It can accept any one of the values listed in Table 11.20:

Table 11.20: Values for @FREQ\_TYPE for SP_ADD_JOB

Value	Description
1	Once
4	Daily
8	Weekly
16	Monthly
32	Monthly, relative to freq interval
64	Run when SQL Server Agent service starts
128	Run when the computer is idle

@FREQ\_INTERVAL acts depend upon the value of @FREQ\_TYPE. For example, if @FREQ\_TYPE is set as once, then @FREQ\_INTERVAL has no impact. If @FREQ\_INTERVAL is set to run weekly, then @FREQ\_INTERVAL is used to indicate on which day the job should run.

For more information on this topic, refer to the MSDN library at https://msdn2.microsoft.com.

Step 6: Interface File Conversion

Any application will have inbound and outbound interface files. During application migration, it is the application developer's responsibility to consider all inbound as well as outbound files. External applications will use specified formats. Even after the conversion from Oracle, the external application will still need the interface file in the same format.

Similarly, the external application might send some interface files to the database. Considering that the format might remain the same, application code must be converted in such a way that the application will handle the file, even though it is not in a SQL Server-specific format.

Step 7: Workflow Automation

When performing the application migration, workflow must also be examined. This section discusses how to send mail from the SQL Server database.

Sending Mail

Sending mail from an application is a very common requirement. Oracle includes a package named UTL_SMTP to provide e-mail functions. Using UTL_SMTP, application developers write their code to send notification mails from PL/SQL programs. Some setup is needed in the server side before UTL_SMTP can be used.

SQL Server has several options to send e-mail. Before using these options, the mail accounts and mail service in the server where SQL Server resides needs to be set up and configured. After this is set up, the XP_SENDMAIL stored procedure can be used to send notification mails. XP_SENDMAIL has all the options of mail services, including attachments. For example, the error log file can be sent to the administrator in case of server side errors.

Before using mail options, set up SQL Mail in SQL Server Enterprise Manager using the following steps:

Expand the server group and then expand the server.
Expand Support Services, right-click SQL Mail, and then click Properties.
In the Profile list, select the profile that you created earlier.
Click Test if you want to test the SQL mail.

After SQL mail is set up, jobs can be configured to send mail alerts on job completion to indicate whether the job succeeded or failed. To configure the jobs, follow these steps:

Go to Jobs, select a particular job, and then click Properties.
On the Notification tab, you can enable the mailing option upon success or failure.
To send mail from Jobs, the Mail component needs to be started. This can be started automatically whenever SQL Server agent starts. Alternatively, XP_STARTMAIL can be used to start the mail agent of SQL Server.

In addition, XP_SENDMAIL can be used to send mail from T-SQL batches, triggers, or procedures. The syntax is shown in the following example:

XP_SENDMAIL {[@RECIPIENTS =] 'RECIPIENTS [;...N]'} 
 [,[@MESSAGE =] 'MESSAGE'] 
 [,[@QUERY =] 'QUERY'] 
 [,[@ATTACHMENTS =] 'ATTACHMENTS [;...N]'] 
 [,[@COPY_RECIPIENTS =] 'COPY_RECIPIENTS [;...N]'
 [,[@BLIND_COPY_RECIPIENTS =] 'BLIND_COPY_RECIPIENTS [;...N]'
 [,[@SUBJECT =] 'SUBJECT']

The following example sends a mail to the mail ID SQLDBA@ABC.COM and a copy to another mail ID SQLDBA1@ABC.COM. This also sends an attachment with the name "error.log" that is in the directory C:\.

XP_SENDMAIL @RECIPIENTS='SQLDBA@ABC.COM', @MESSAGE='Test mail from SQL Mail', @ATTACHMENTS='C:\ERROR_LOG', @COPY_RECIPIENTS='@SQLDBA1@ABC.COM', @SUBJECT='Test Subject'

Step 8: Performance Tuning

After the code changes have been incorporated, the next step is to review and tune the performance. Make sure proper joins are in place and all possible indexed columns are used while executing the query. General techniques and guidelines are discussed in this section.

After following these optimization techniques, test the application with a test database. If possible, use a copy of production data while testing so that the real response times can be recorded and more time can be spent tuning the application.

The goal of performance tuning is to minimize the response time for each query and to maximize the throughput of the entire database server by reducing network traffic, disk I/O, and CPU time. This goal is achieved through understanding application requirements, the logical and physical structure of the data, and tradeoffs between conflicting uses of the database. Performance strategies vary in their effectiveness, and systems with different purposes, such as operational systems and decision support systems, require different performance skills.

System performance is designed and built into a system. It does not just happen. Performance issues should be considered throughout the development cycle, not just at the end when the system is implemented. Many performance issues that result in significant improvements are achieved by careful design from the outset.

Performance problems are usually the result of competition for, or exhaustion of, system resources. When a system resource is exhausted, the system is unable to scale to higher levels of performance. Although other system-level performance issues, such as memory or hardware, are certainly candidates for study, experience shows that the performance gains from tuning a query are only incremental when there are fundamental problems in its design.

This section describes how SQL Server queries can be optimized when migrating from Oracle applications.

For example, in Oracle, there are several areas where a DBA concentrates on tuning the database and queries, such as:

Memory tuning
I/O tuning
SQL tuning
Managing data and transactions

SQL Server automatically manages available hardware resources, reducing the need for extensive system-level manual tuning. Apart from this automatic management, SQL Server provides several ways to optimize performance during the development and after the system is released into production. These methods are discussed under the following headings.

Query Tuning

It may be tempting to address a performance problem solely by system-level server performance tuning; for example, by altering memory size, type of file system, and number and type of processors. Generally, most performance problems cannot be resolved this way. They must be addressed by analyzing the application, queries, and updates that the application is submitting to the database, and how these queries and updates interact with the database schema.

Unexpected long-lasting queries and updates can be caused by:

Slow network communication
Inadequate memory in the server computer
Lack of useful statistics
Out-of-date statistics
Lack of useful indexes
Lack of useful data striping
Improper database design

These issues can be resolved only by looking at the query and its path of execution. SQL Server has several ways to identify all of these issues. Two of the commonly used methods, SQL Profiler and the SET SHOWPLAN statement, are discussed here. Refer to Appendix B for a list of references about improving performance.

SQL Profiler

This graphical tool allows system administrators to monitor events in an instance of SQL Server. Each event can be captured to a file or another table. Using this data, the query can be analyzed. A stored procedure that hampers the performance can also be monitored.

Only use the SQL Profiler for suspicious events. As SQL Profiler is used, it can slow down the server and increase the trace file size. Specific events can be filtered so only a subset of data can be monitored.

After you have traced the events, SQL Profiler allows captured event data to be replayed against an instance of SQL Server, effectively re-executing the saved events as they occurred originally.

SQL Profiler can be used to:

Monitor the performance of an instance of SQL Server.
Debug Transact-SQL statements and stored procedures.
Identify slow-executing queries.
Test SQL statements and stored procedures in the Developing Phase of a project by going through statements to confirm that the code works as expected.
Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system. This is useful for testing or debugging purposes and allows users to continue using the production system without interference.
Audit and review activity that occurred on an instance of SQL Server. This allows a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.
Provide input to the Index Tuning Wizard to determine index usage suggestions.

Using SET SHOWPLAN in SQL Server

SET SHOWLAN is similar to the Oracle EXPAIN PLAN command, and it is used to display detailed information about a query. SET SHOWPLAN_ALL causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements. The syntax for this command is:

SET SHOWPLAN_ALL {ON | OFF}

When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements is returned until the option is set OFF.

For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

Use SET SHOWPLAN_TEXT to return readable output for Microsoft MS-DOS applications, such as the osql utility.

SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified inside a stored procedure; they must be the only statements in a batch.

SET SHOWPLAN_ALL returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps.

For a more detailed view of the output obtained by turning on SET SHOWPLAN_ALL, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_365o.asp.

Chapter 11 - Developing: Applications - Migrating Oracle SQL and PL/SQL

On This Page

Introduction

Migrating Data Access

Sample Tables

Migration Process Overview

Step 1: Extraction of Data Access

Operators

Operator Precedence

Concatenation Operators

Comparison Operators

Modulo Operator

Logical Operators

Datatype Precedence

Functions

Number and Mathematical Functions

Character Functions

Date Functions

Conditional Functions

Queries

Simple Queries

Joins

Inner Join

Equi-Join

Self Joins

Non-Equi Joins

Outer Joins

Subqueries

Grouping Result Set

Database Link

CASE

Set Operators: Union

Set Operators: Intersect

Set Operators: Minus

ROWNUM

Data Manipulation Language (DML)

Insert

Insert Sequences

Using Date Values in Insert Operations

Insert with Subquery

Update

Merge

Delete

Step 2: Transaction Management

Transaction Control Language (TCL)

COMMIT

SAVEPOINT

ROLLBACK

Step 3: Fetch Strategy

Using Transact-SQL Cursors

Transact SQL Extensions

Step 4: Subprograms Conversion

Migrating Procedures

Functions

Triggers

INSTEAD OF Triggers

Error Handling

Step 5: Job Scheduling

Scheduling Functions and Jobs

Using SQL Server Enterprise Manager

Using SP_ADD_JOB

Step 6: Interface File Conversion

Step 7: Workflow Automation

Sending Mail

Step 8: Performance Tuning

Query Tuning

SQL Profiler

Using SET SHOWPLAN in SQL Server

Additional resources