SQL Language Support

This section outlines the similarities and differences between Transact-SQL and PL/SQL language syntax and discusses conversion strategies.

Recommended Conversion Strategy

You should use the following checklist when migrating your Oracle DML statements and PL/SQL programs:

Verify that the syntax for of all SELECT, INSERT, UPDATE, and DELETE statements is valid. Make any modifications that are required.
Change all outer joins to ANSI standard outer join syntax.
Replace Oracle functions with the appropriate SQL Server functions.
Check all comparison operators.
Replace the "||" string concatenation operators with the "+" string concatenation operators.
Replace PL/SQL programs with Transact-SQL programs.
Change all PL/SQL cursors to either noncursor SELECT statements or Transact-SQL cursors.
Replace PL/SQL procedures, functions, and packages with Transact-SQL procedures.
Convert PL/SQL triggers to Transact-SQL triggers.
Use the SET SHOWPLAN statement to performance tune your queries.

INSERT Statements

INSERT statements require minimal revision because Oracle's syntax is similar to SQL Server's.

Oracle	SQL Server
INSERT INTO {table_name \| view_name \| *select_statement} [(column_list)] {values_list* \| select_statement}	INSERT INTO {table_name \| view_name} [(column_list)] {values_list \| select_statement \| *DEFAULT VALUES* \| *default* \| *EXECute{ procedure_name*}

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	SQL Server
INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE) VALUES ('111111111', '1111',NULL)	INSERT INTO GRADE (SSN, CCODE, GRADE) VALUES ('111111111', '1111',NULL)

Because Transact-SQL supports inserts into views, you can also create a view based on the SELECT statement.

The Transact-SQL values_list parameter offers the keyword DEFAULT, which is not available with 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. In order 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 need to use the DEFAULT keyword to obtain the default value for a column. Just as in Oracle, if the column is not referenced in the column_list and it has a default value, the default value is placed into the column. This is the most compatible approach to use when performing the migration.

One interesting Transact-SQL option (EXECute procedure_name) is the capability to execute a procedure and effectively 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 with the Oracle UPDATE statement, minimum revision is required.

Oracle	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 \| view_name} SET [column_name(s) = {constant_value \| expression \| select_statement \| column_list \| default \| variable_list] [FROM {table_name \| view_name}[(optimizer_hints)}] [where_statement}

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 the "INSERT Statements" section.

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

Oracle	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. You cannot set a column to a default value with the Oracle UPDATE statement.

Transact-SQL and Oracle SQL support the use of subqueries in an UPDATE statement.

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

The Transact-SQL FROM clause can be used to turn an UPDATE based on a subquery into an UPDATE based on a join. This capability makes your UPDATE syntax more readable and in some cases can improve performance:

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, the syntax must be modified because it is not supported by Transact-SQL.

Oracle	SQL Server
DELETE [FROM] {table_name \| view_name \| *select_statement*} [WHERE clause]	DELETE [FROM] {table_name \| view_name} [FROM {table_name \| view_name}[(optimizer_hints)}] [WHERE clause]

Transact-SQL supports the use of subqueries when performing deletes. However, using the FROM clause can produce more efficient statements. See the example shown previously in the "UPDATE Statements" section.

TRUNCATE TABLE Statement

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

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

In SQL Server, this statement can only be issued 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 be directed to release or keep 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 not directly related to any given table. Therefore, Oracle does not enforce any rules when working with sequences. However, SQL Server is quite strict when using identity columns: the DEFAULT keyword cannot be used when working with identity columns. Additionally, values in identity columns cannot be updated.

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 is allowed to insert any value into a new row with an identity column. 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 accidentally deleted. The @@IDENTITY global variable can be used to obtain the last identity value.

The TRUNCATE TABLE statement resets an identity column to its original SEED value. This can affect any type of synchronization that exists with Oracle tables using sequences, because sequences are not reset following the TRUNCATE TABLE command.

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 can perform only inserts or deletes when working with timestamp columns. If you attempt to update a timestamp column, you receive the following error message:

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

text and image Columns

When you migrate to SQL Server, you use text and image columns in place of LONG and LONG RAW columns. In Oracle, UPDATE and INSERT statements 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 the Microsoft SQL Server Transact-SQL Reference.

SELECT Statements

The SELECT statement syntax is quite similar in each DBMS.

Oracle	SQL Server
SELECT [/+ optimizer_hints/] [ALL \| DISTINCT] select_list [FROM {table_name \| view_name \| select_statement}] [WHERE clause] [GROUP BY clause] [HAVING clause] [START WITH … CONNECT BY] [{UNION \| UNION ALL \| INTERSECT \| MINUS} SELECT …] [ORDER BY clause] [FOR UPDATE]	SELECT [ALL \| DISTINCT] select_list *[INTO [new_table_name]]* [FROM {table_name \| view_name \| select_statement} [JOIN {table_name \| view_name \| select_statement} ON search_conditions] [(optimizer_hints)] [WHERE clause] [GROUP BY clause] [HAVING clause] [{UNION \| UNION ALL} SELECT …] [ORDER BY clause] [COMPUTE clause] [FOR BROWSE]

Oracle-specific cost-based optimizer hints are not supported by SQL Server. They must be removed from your statement code. For more information, see "Tuning SQL Statements" later in this paper. However, the recommended technique is to assume that the default SQL Server cost-based optimization will work fine, because it usually does.

SQL Server does not support the Oracle START WITH…CONNECT BY clause. If your application currently takes advantage of this hierarchical reporting capability, those statements that include this clause must be rewritten. You could potentially replace this in SQL Server with a user-written procedure that performs the same task.

The INTERSECT and MINUS set operators are not supported by SQL Server. However, the 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, regardless of query.

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

Locking Requested Rows

Oracle uses the FOR UPDATE clause to lock rowsspecified in the SELECT statement. Usually you don't need to use the equivalent clause in SQL Server. Don't confuse the FOR BROWSE and the FOR UPDATE clause. The FOR BROWSE clause is a specialized facility for use in client application programs that need additional metadata at run time.

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 statement syntax does not support the COMPUTE clause. It is worth noting that the COMPUTE clause works just like the COMPUTE command found in the Oracle SQL*Plus query tool.

Join Clauses

SQL Server allows up to 16 tables to be joined in a join clause. This includes both temporary and permanent tables. There is no join limit in Oracle.

When using outer joins in Oracle, the outer join operator (+) is typically placed 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 nulls, in which case the outer join operator (+) may be placed on the parent (PRIMARY KEY or UNIQUE constraint) column. You cannot place the outer join operator (+) on both sides of the equal sign (=).

When using 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 nulls, 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 supports the ANSI-standard join operators, and it is recommended that you use this syntax. The ANSI-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 code examples return listings 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). Notice how the syntax differs between Oracle and SQL Server.

Oracle	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

SQL Server and Oracle support the use of SELECT statements as table names when performing queries. However, it is important to note that an alias must be provided with the SQL Server SELECT statement, while an alias is optional with Oracle.

Oracle	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 text and image Columns

In Oracle, you use a SELECT statement to query the values in LONG and LONG RAW columns. Because the LONG or LONG RAW column is part of a queried row, no specialized functions are required to access this information.

In SQL Server, you can use a standard SQL statement or the specialized READTEXT statement to read data in text and image columns. It is important to note that 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 global variable @@textsize, which is the limit on the number of bytes of data 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 zero, the size is reset to the default (4K). Setting the TEXTSIZE parameter affects the global variable @@textsize. The SQL Server ODBC driver automatically sets the TEXTSIZE parameter when the SQL_MAX_LENGTH statement option is changed.