SELECT and Data Manipulation Statements

Use the following when migrating your Oracle DML statements and PL/SQL programs to SQL Server.

  1. Verify that the syntax of all SELECT, INSERT, UPDATE, and DELETE statements is valid. Make any required modifications.
  2. Change all outer joins to SQL-92 standard outer join syntax.
  3. Replace Oracle functions with the appropriate SQL Server functions.
  4. Check all comparison operators.
  5. Replace the “||” string concatenation operator with the “+” string concatenation operator.
  6. Replace PL/SQL programs with Transact-SQL programs.
  7. Change all PL/SQL cursors to either noncursor SELECT statements or Transact-SQL cursors.
  8. Replace PL/SQL procedures, functions, and packages with Transact-SQL procedures.
  9. Convert PL/SQL triggers to Transact-SQL triggers.
  10. Use the SET SHOWPLAN statement to tune your queries for performance.

SELECT Statements

The SELECT statement syntax used by Oracle and Microsoft SQL Server is similar.

Oracle Microsoft SQL Server
SELECT [/*+ optimizer_hints*/]
[ALL | DISTINCT] select_list
[FROM
{table_name | view_name |    select_statement}]
[WHERE clause]
[GROUP BY group_by_expression]
[HAVING search_condition]
[START WITH … CONNECT BY]
[{UNION | UNION ALL | INTERSECT |
  MINUS} SELECT …]
[ORDER BY clause]
[FOR UPDATE]		 SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[ GROUP BY [ALL] group_by_expression [,…n]
       [ WITH { CUBE | ROLLUP } ]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

In addition:

UNION Operator
COMPUTE Clause
FOR BROWSE Clause
OPTION Clause

Oracle-specific cost-based optimizer hints are not supported by SQL Server, and must be removed. The recommended technique is to use SQL Server cost-based optimization. For more information, see “Tuning SQL Statements” later in this chapter.

SQL Server does not support the Oracle START WITH…CONNECT BY clause. You can replace this in SQL Server by creating a stored procedure that performs the same task.

The Oracle INTERSECT and MINUS set operators are not supported by SQL Server. The SQL Server EXISTS and NOT EXISTS clauses can be used to accomplish the same result.

The following example uses the INTERSECT operator to find the course code and course name for all classes that have students. Notice how the EXISTS operator replaces the use of the INTERSECT operator. The data that is returned is identical.

Oracle Microsoft SQL Server
SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
INTERSECT
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE		 SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE EXISTS
(SELECT 'X' FROM    STUDENT_ADMIN.GRADE     G
WHERE C.CCODE = G.CCODE)

This example uses the MINUS operator to find those classes that do not have any students.

Oracle Microsoft SQL Server
SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE		 SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM    STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

INSERT Statements

The INSERT statement syntax used by Oracle and Microsoft SQL Server is similar.

Oracle Microsoft SQL Server
INSERT INTO
{table_name | view_name | select_statement} [(column_list)]
{values_list | select_statement}		 INSERT [INTO]
   {
    table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
    | view_name [ [AS] table_alias]
    | rowset_function_limited
   }

   {    [(column_list)]
       { VALUES ( {    DEFAULT
                       |    NULL
                       |    expression
                       }[,…n]
           )
       | derived_table
       | execute_statement   
       }
   }
   | DEFAULT VALUES

The Transact-SQL language supports inserts into tables and views, but does not support INSERT operations into SELECT statements. If your Oracle application code performs inserts into SELECT statements, this must be changed.

Oracle Microsoft SQL Server
INSERT INTO (SELECT SSN, CCODE,    GRADE FROM GRADE)
VALUES ('111111111', '1111',NULL)		 INSERT INTO GRADE (SSN, CCODE,    GRADE)
VALUES ('111111111', '1111',NULL)

The Transact-SQL values_list parameter offers the SQL-92 standard keyword DEFAULT, which is not supported by Oracle. This keyword specifies that the default value for the column be used when an insert is performed. If a default value does not exist for the specified column, a NULL is inserted. If the column does not allow NULLs, an error message is returned. If the column is defined as a timestamp data type, the next sequential value is inserted.

The DEFAULT keyword cannot be used with an identity column. To generate the next sequential number, columns with the IDENTITY property must not be listed in the column_list or values_clause. You do not have to use the DEFAULT keyword to obtain the default value for a column. As in Oracle, if the column is not referenced in the column_list and it has a default value, the default value is placed in the column. This is the most compatible approach to use when performing the migration.

One useful Transact-SQL option (EXECute procedure_name) is to execute a procedure and pipe its output into a target table or view. Oracle does not allow you to do this.

UPDATE Statements

Because Transact-SQL supports most of the syntax used by the Oracle UPDATE command, minimal revision is required.

Oracle Microsoft SQL Server
UPDATE
{table_name | view_name |    select_statement}
SET [column_name(s) = {constant_value |    expression | select_statement | column_list |
  variable_list]
{where_statement}		 UPDATE
{
table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
view_name [ [AS] table_alias]
| rowset_function_limited
}
SET
{column_name = {expression | DEFAULT | NULL}
| @variable = expression
| @variable = column = expression } [,…n]

{{[FROM {<table_source>} [,…n] ]

[WHERE
<search_condition>] }
|
[WHERE CURRENT OF
{ { [GLOBAL] cursor_name } | cursor_variable_name}
] }
[OPTION (<query_hint> [,…n] )]

The Transact-SQL UPDATE statement does not support update operations against SELECT statements. If your Oracle application code performs updates against SELECT statements, you can turn the SELECT statement into a view, and then use the view name in the SQL Server UPDATE statement. See the example shown previously in “INSERT Statements.”

The Oracle UPDATE command can use only program variables from within a PL/SQL block. The Transact-SQL language does not require the use of blocks to use variables.

Oracle Microsoft SQL Server
DECLARE
VAR1 NUMBER(10,2);
BEGIN
    VAR1 := 2500;
    UPDATE     STUDENT_ADMIN.STUDENT
   SET TUITION_TOTAL = VAR1;
END;		 DECLARE
@VAR1 NUMERIC(10,2)
SELECT @VAR1 = 2500
UPDATE STUDENT_ADMIN.STUDENT
SET TUITION_TOTAL=@VAR1

The keyword DEFAULT can be used to set a column to its default value in SQL Server. You cannot set a column to a default value with the Oracle UPDATE command.

Transact-SQL and Oracle SQL support the use of subqueries in an UPDATE statement. However, the Transact-SQL FROM clause can be used to create an UPDATE based on a join. This capability makes your UPDATE syntax more readable and in some cases can improve performance.

Oracle Microsoft SQL Server
UPDATE
  STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
WHERE SSN IN (SELECT SSN
        FROM GRADE G
            WHERE G.SSN = S.SSN
            AND G.CCODE = '1234')		 Subquery:

UPDATE
  STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
WHERE SSN IN (SELECT SSN
        FROM GRADE G
            WHERE G.SSN = S.SSN
            AND G.CCODE = '1234')

FROM clause:

UPDATE
  STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
  FROM GRADE G
     WHERE S.SSN = G.SSN
          AND G.CCODE = '1234'

DELETE Statements

In most cases, you do not need to modify DELETE statements. If you perform deletes against SELECT statements in Oracle, you must modify the syntax for SQL Server because this functionality is not supported by Transact-SQL.

Transact-SQL supports the use of subqueries in the WHERE clause, as well as joins in the FROM clause. The latter can produce more efficient statements. See the example shown previously in “UPDATE Statements.”

Oracle Microsoft SQL Server
DELETE [FROM]
{table_name | view_name |    select_statement}
[WHERE clause] 		DELETE
[FROM ]
{
table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
| view_name [ [AS] table_alias]
| rowset_function_limited
}

[ FROM {<table_source>} [,…n] ]

[WHERE
{ <search_condition>
|    { [ CURRENT OF
{
{ [ GLOBAL ] cursor_name }
cursor_variable_name
}
]
}
]
[OPTION (<query_hint> [,…n])]

TRUNCATE TABLE Statement

The TRUNCATE TABLE syntax used by Oracle and Microsoft SQL Server is similar. TRUNCATE TABLE is used to remove all of the rows from a table and cannot be rolled back. The table structure and all of its indexes continue to exist. DELETE triggers are not executed. If a table is referenced by a FOREIGN KEY constraint, it cannot be truncated.

Oracle Microsoft SQL Server
TRUNCATE TABLE table_name
[{DROP | REUSE} STORAGE]		 TRUNCATE TABLE table_name

In SQL Server, this statement can be issued only by the table owner. In Oracle, this command can be issued if you are the table owner or have the DELETE TABLE system privilege.

The Oracle TRUNCATE TABLE command can optionally release the storage space occupied by the rows in the table. The SQL Server TRUNCATE TABLE statement always reclaims space occupied by the table data and its associated indexes.

Manipulating Data in identity
and timestamp Columns

Oracle sequences are database objects that are not directly related to any given table or column. The relationship between a column and a sequence is implemented in the application, by assigning the sequence value to a column programmatically. Therefore, Oracle does not enforce any rules when it works with sequences. However, in Microsoft SQL Server identity columns, values cannot be updated and the DEFAULT keyword cannot be used.

By default, data cannot be inserted directly into an identity column. The identity column automatically generates a unique, sequential number for each new row inserted in the table. This default can be overridden using the following SET statement:

SET IDENTITY_INSERT table_name ON

  

With IDENTITY_INSERT set to ON, the user can insert any value into the identity column of a new row. To prevent the entry of duplicate numbers, a unique index must be created against the column. The purpose of this statement is to allow a user to re-create a value for a row that has been deleted accidentally. The @@IDENTITY function can be used to obtain the last identity value.

The TRUNCATE TABLE statement resets an identity column to its original SEED value. If you do not want to reset the identity value for a column, use the DELETE statement without a WHERE clause instead of the TRUNCATE TABLE statement. You will have to evaluate how this affects your Oracle migration, because ORACLE SEQUENCES are not reset following the TRUNCATE TABLE command.

You can perform only inserts or deletes when working with timestamp columns. If you attempt to update a timestamp column, you receive this error message:

Msg 272, Level 16, State 1 Can’t update a TIMESTAMP column.

  

Locking Requested Rows

Oracle uses the FOR UPDATE clause to lock rows specified in the SELECT command. You do not need to use the equivalent clause in Microsoft SQL Server because this is the default behavior.

Row Aggregates and the Compute Clause

The SQL Server COMPUTE clause is used to generate row aggregate functions (SUM, AVG, MIN, MAX, and COUNT), which appear as additional rows in the query results. It allows you to see detail and summary rows in one set of results. You can calculate summary values for subgroups, and you can calculate more than one aggregate function for the same group.

The Oracle SELECT command syntax does not support the COMPUTE clause. Nevertheless, the SQL Server COMPUTE clause works just like the COMPUTE command found in the Oracle SQL*Plus query tool.

Join Clauses

Microsoft SQL Server 7.0 allows up to 256 tables to be joined in a join clause, including both temporary and permanent tables. There is no join limit in Oracle.

When using outer joins in Oracle, the outer join operator (+) is placed typically next to the child (foreign key) column in the join. The (+) identifies the column with fewer unique values. This always occurs unless the foreign key allows null values, in which case (+) can be placed on the parent (PRIMARY KEY or UNIQUE constraint) column. You cannot place the (+) on both sides of the equal sign (=).

With SQL Server, you can use the *= and =* outer join operators. The * is used to identify the column that has more unique values. If the child (foreign key) column does not allow null values, the * is placed on the parent (PRIMARY KEY or UNIQUE constraint) column side of the equal sign. The placement of the * is essentially reversed in Oracle. You cannot place the * on both sides of the equal sign (=).

The *= and =* are considered legacy join operators. SQL Server also supports the SQL-92 standard join operators listed below. It is recommended that you use this syntax. The SQL-92 standard syntax is more powerful and has fewer restrictions than the * operators.

Join operation Description
CROSS JOIN This is the cross product of two tables. It returns the same rows as if no WHERE clause was specified in an old-style join. This type of join is called a Cartesian-join in Oracle.
INNER This join specifies that all inner rows be returned. Any unmatched rows are discarded. This is identical to a standard Oracle table join.
LEFT[OUTER] This type of join specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+).
RIGHT[OUTER] This type of join specifies that all of the right table outer rows be returned, even if no column matches are found. This operates just like an Oracle outer join (+).
FULL [OUTER] If a row from either table does not match the selection criteria, specifies the row be included in the result set and its output columns that correspond to the other table be set to NULL. This would be the same as placing the Oracle outer join operator on both sides of the “=“ sign (col1(+) = col2(+)), which is not allowed.

The following code examples return lists of classes taken by all students. Outer joins are defined between the student and grade tables that allow all students to appear, even those who are not enrolled in any classes. Outer joins are also added to the class table in order to return the class names. If outer joins are not added to the class tables, those students who are not enrolled in any classes are not returned because they have null course codes (CCODE).

Oracle Microsoft SQL Server
SELECT S.SSN AS SSN,
FNAME, LNAME
FROM STUDENT_ADMIN.STUDENT S,
DEPT_ADMIN.CLASS C,
STUDENT_ADMIN.GRADE G
WHERE S.SSN = G.SSN(+)
AND G.CCODE = C.CCODE(+)		 SELECT S.SSN AS SSN,
FNAME, LNAME
FROM STUDENT_ADMIN.GRADE G
RIGHT OUTER JOIN
STUDENT_ADMIN.STUDENT S
ON G.SSN = S.SSN
LEFT OUTER JOIN
DEPT_ADMIN.CLASS C
ON G.CCODE = C.CCODE

Using SELECT Statements as Table Names

Microsoft SQL Server and Oracle support the use of SELECT statements as the source of tables when performing queries. SQL Server requires an alias; the use of an alias is optional with Oracle.

Oracle Microsoft SQL Server
SELECT SSN, LNAME, FNAME,
TUITION_PAID, SUM_PAID
FROM STUDENT_ADMIN.STUDENT,
(SELECT SUM(TUITION_PAID) SUM_PAID FROM    STUDENT_ADMIN.STUDENT)		 SELECT SSN, LNAME, FNAME,
TUITION_PAID, SUM_PAID
FROM STUDENT_ADMIN.STUDENT,
(SELECT SUM(TUITION_PAID) SUM_PAID FROM    STUDENT_ADMIN.STUDENT) SUM_STUDENT

Reading and Modifying BLOBs

Microsoft SQL Server implements binary large objects (BLOBs) with text and image columns. Oracle implements BLOBs with LONG and LONG RAW columns. In Oracle, a SELECT command can query the values in LONG and LONG RAW columns.

In SQL Server, you can use a standard Transact-SQL statement or the specialized READTEXT statement to read data in text and image columns. The READTEXT statement allows you to read partial sections of a text or image column. Oracle does not provide an equivalent statement for working with LONG and LONG RAW columns.

The READTEXT statement makes use of a text_pointer, which can be obtained using the TEXTPTR function. The TEXTPTR function returns a pointer to the text or image column in the specified row or to the text or image column in the last row returned by the query if more than one row is returned. Because the TEXTPTR function returns a 16-byte binary string, it is best to declare a local variable to hold the text pointer, and then use the variable with READTEXT.

The READTEXT statement specifies how many bytes to return. The value in the @@TEXTSIZE function, which is the limit on the number of characters or bytes to be returned, supersedes the size specified by the READTEXT statement if it is less than the specified size for READTEXT.

The SET statement can be used with the TEXTSIZE parameter to specify the size, in bytes, of text data to be returned with a SELECT statement. If you specify a TEXTSIZE of 0, the size is reset to the default (4 KB). Setting the TEXTSIZE parameter affects the @@TEXTSIZE function. The SQL Server ODBC driver automatically sets the TEXTSIZE parameter when the SQL_MAX_LENGTH statement option is changed.

In Oracle, UPDATE and INSERT commands are used to change values in LONG and LONG RAW columns. In SQL Server, you can use standard UPDATE and INSERT statements, or you can use the UPDATETEXT and WRITETEXT statements. Both UPDATETEXT and WRITETEXT allow a nonlogged option, and UPDATETEXT allows for partial updating of a text or image column.

The UPDATETEXT statement can be used to replace existing data, delete existing data, or insert new data. Newly inserted data can be a constant value, table name, column name, or text pointer.

The WRITETEXT statement completely overwrites any existing data in the column it affects. Use WRITETEXT to replace text data and UPDATETEXT to modify text data. The UPDATETEXT statement is more flexible because it changes only a portion of a text of image value rather than the entire value.

For more information, see SQL Server Books Online.