This chapter describes the differences between Microsoft SQL Server or Sybase Adaptive Server and Oracle. It contains the following sections:
Microsoft SQL Server or Sybase Adaptive Server database triggers are AFTER triggers. This means that triggers are fired after the specific operation is performed. For example, the INSERT trigger fires after the rows are inserted into the database. If the trigger fails, the operation is rolled back.
Microsoft SQL Server or Sybase Adaptive Server allows INSERT, UPDATE, and DELETE triggers. Triggers typically need access to the before image and after image of the data that is being changed. Microsoft SQL Server or Sybase Adaptive Server achieves this with two temporary tables called INSERTED and DELETED. These two tables exist during the execution of the trigger. These tables and the table for which the trigger is written have the exact same structure. The DELETED table holds the before image of the rows that are undergoing change because of the UPDATE/DELETE operation, and the INSERTED table holds the after image of the rows for the INSERT/UPDATE operation. If there is an error, the triggers can issue a rollback statement.
Most of the Microsoft SQL Server or Sybase Adaptive Server trigger code is written to enforce referential integrity. Microsoft SQL Server or Sybase Adaptive Server triggers are executed once per triggering SQL statement (such as INSERT, UPDATE, or DELETE). If you want some actions to be performed for each row that the SQL statement affects, you must code the actions using the INSERTED and DELETED tables.
Oracle has a rich set of triggers. Oracle also provides triggers that fire for events such as INSERT, UPDATE, and DELETE. You can also specify the number of times that the trigger action is to be executed. For example, once for every row affected by the triggering event (such as might be fired by an UPDATE statement that updates many rows), or once for the triggering statement (regardless of how many rows it affects).
A ROW trigger is fired each time that the table is affected by the triggering event. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. A STATEMENT trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects.
Oracle triggers can be defined as either BEFORE triggers or AFTER triggers. BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger, you can avoid unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised.
As combinations, there are four different types of triggers in Oracle:
BEFORE STATEMENT trigger
BEFORE ROW trigger
AFTER STATEMENT trigger
AFTER ROW trigger
It is sometimes necessary to create a ROW trigger or a STATEMENT trigger to achieve the same functionality as the Microsoft SQL Server or Sybase Adaptive Server trigger. This occurs in the following cases:
The triggering code reads from its own table (mutating).
The triggering code contains group functions.
In the following example, the group function AVG is used to calculate the average salary:
SELECT AVG(inserted.salary) FROM inserted a, deleted b WHERE a.id = b.id;
This would be converted to Oracle by creating an AFTER ROW trigger to insert all the updated values into a package, and an AFTER STATEMENT trigger to read from the package and calculate the average.
For examples of Oracle triggers, see Oracle Database 2 Day Developer's Guide.
Microsoft SQL Server or Sybase Adaptive Server stores triggers and stored procedures with the server. Oracle stores triggers and stored subprograms with the server. Oracle has three different kinds of stored subprograms, namely functions, stored procedures, and packages. For detailed discussion on all these objects, see Oracle Database PL/SQL Language Reference.
Stored procedures provide a powerful way to code the application logic that can be stored with the server. Microsoft SQL Server or Sybase Adaptive Server and Oracle all provide stored procedures.
The language used to code these objects is a database-specific procedural extension to SQL. In Oracle it is PL/SQL and in Microsoft SQL Server or Sybase Adaptive Server it is Transact SQL (T/SQL). These languages differ to a considerable extent. The individual SQL statements and the procedural constructs, such as if-then-else, are similar in both versions of the procedural SQL. Considerable differences can be found in the following areas discussed in these sections:
This section also considers various components of typical Microsoft SQL Server or Sybase Adaptive Server stored procedures and suggests ways to design them in order to avoid conversion problems. By applying the standards described in this section to the coding, you can convert your stored procedures from Microsoft SQL Server or Sybase Adaptive Server to Oracle.
In individual SQL statements, you should try to follow ANSI-standard SQL whenever possible. However, there are cases where you need to use database-specific SQL constructs, mostly for ease of use, simplicity of coding, and performance enhancement. For example, Microsoft SQL Server or Sybase Adaptive Server constructs such as the following are SQL Server or Sybase Adaptive Server-specific:
update <table_name> set ... from <table1>, <table_name> where...
The conversion performed by the automatic conversion utilities for statements such as this can be seen in the following examples:
DELETE sales FROM sales, titles WHERE sales.title_id = titles.title_id AND titles.type = 'business'
DELETE sales
WHERE ROWID IN
( SELECT sales.ROWID
FROM sales ,
titles
WHERE sales.title_id = titles.title_id
AND titles.TYPE = 'business' );
UPDATE titles SET price = price + author_royalty FROM titles, title_author WHERE titles.title.id = title_author.title_id
MERGE INTO titles USING (SELECT * FROM title_author) title_author ON ( title.id = title_author.title_id ) WHEN MATCHED THEN UPDATE SET price = price + author_royalty;
All the ANSI-standard SQL statements can be converted from one database to another using automatic conversion utilities.
In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit by definition. This implies that an individual SQL statement is not part of a logical transaction by default. A SQL statement belongs to a logical transaction if the transaction explicitly initiated by the user with a BEGIN TRANSACTION (or BEGIN TRAN) statement is still in effect. The logical transaction ends with a corresponding COMMIT TRANSACTION (or COMMIT TRAN) or ROLLBACK TRANSACTION (or ROLLBACK TRAN) statement. Each SQL statement that is not part of a logical transaction is committed on completion.
In Oracle, transactions are implicit as set by the ANSI standard. The implicit transaction model requires that each SQL statement is part of a logical transaction. A new logical transaction is automatically initiated when a COMMIT or ROLLBACK command is executed. This also implies that data changes from an individual SQL statement are not committed to the database after execution. The changes are committed to the database only when a COMMIT statement is run. The differences in the transaction models impact the coding of application procedures.
For client/server applications, it is recommended that you make the transaction-handling constructs part of the client procedures. The logical transaction is always defined by client users, and they should control it. This strategy is also more suitable for distributed transactions, where the two-phase commit operations are necessary. Making the transaction-handling statements a part of the client code serves a two-fold purpose; the server code is more portable, and the distributed transactions can be independent of the server code. Try to avoid using the BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN statements in the stored procedures. In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit. In Oracle, transactions are implicit. If the transactions are handled by the client, the application code residing on the server can be independent of the transaction model.
Oracle PL/SQL checks each SQL statement for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler. This avoids you having to check the status of every SQL statement. For example, if a SELECT statement does not find any rows in the database, an exception is raised, and the code to deal with this error is executed.
In Microsoft SQL Server or Sybase Adaptive Server, you need not check for errors after each SQL statement. Control is passed to the next statement, irrespective of the error conditions generated by the previous statement. It is your responsibility to check for errors after the execution of each SQL statement. Failure to do so may result in erroneous results.
In Oracle, to simulate the behavior of Microsoft SQL Server or Sybase Adaptive Server and to pass the control to the next statement regardless of the status of execution of the previous SQL statement, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with all possible exceptions for that SQL statement. This coding style is required only to simulate Microsoft SQL Server or Sybase Adaptive Server behavior. An Oracle PL/SQL procedure ideally has only one exception block, and all error conditions are handled in that block.
Consider the following code in a Microsoft SQL Server or Sybase Adaptive Server stored procedure:
begin
select @x = col1 from table1 where col2 = @y
select @z = col3 from table2 where col4 = @x
end
In this code example, if the first SELECT statement does not return any rows, the value of @x could be UNDEFINED. If the control is passed on to the next statement without raising an exception, the second statement returns incorrect results because it requires the value of @x to be set by an earlier statement. In a similar situation, Oracle PL/SQL raises a NO_DATA_FOUND exception if the first statement fails.
The Microsoft SQL Server or Sybase Adaptive Server RAISERROR statement does not return to the calling routine. The error code and message is passed to the client, and the execution of the stored procedure continues further. The Oracle RAISE_APPLICATION_ERROR statement returns to the calling routine. As a standard, a RETURN statement must appear after the RAISERROR statement in Microsoft SQL Server or Sybase Adaptive Server, so that it can be converted to the Oracle RAISE_APPLICATION_ERROR statement.
Microsoft SQL Server or Sybase Adaptive Server allows you to customize the error messages using a system table. The system procedures allow the user to add error messages to the system. Adding error messages to the Microsoft SQL Server or Sybase Adaptive Server system table is not desirable because there is no equivalent on the Oracle system. This can be avoided by maintaining a user-defined error messages table, located in the centralized database. Standard routines can be written to add the error message to the table and retrieve it whenever necessary. This method serves a two-fold purpose: it ensures that the system is more portable across different types of database servers, and it gives the administrator centralized control over the error messages.
This section provides information about data types under the following headings:
T/SQL local variables can be any server data type except TEXT and IMAGE. PL/SQL local variables can be any server data type including the following:
BINARY_INTEGER
BOOLEAN
PL/SQL local variables can also be either of the following composite data types allowed by PL/SQL:
RECORD
TABLE
See the "Data Types" for a list of Microsoft SQL Server or Sybase Adaptive Server data types and their equivalent Oracle data types.
Microsoft SQL Server or Sybase Adaptive Server does not have composite data types.
Table 3-1 Composite Data Types in Oracle
| Oracle | Comments |
|---|---|
|
RECORD |
You can declare a variable to be of type RECORD. Records have uniquely named fields. Logically related data that is dissimilar in type can be held together in a record as a logical unit. |
|
TABLE |
PL/SQL tables can have one column and a primary key, neither of which can be named. The column can belong to any scalar data type. The primary key must belong to type BINARY_INTEGER. |
This section compares the following Microsoft SQL Server or Sybase Adaptive Server and Oracle schema objects:
Each schema object is compared in separate tables based on create, drop, execute and alter, where applicable. The tables are divided into the following sections:
Syntax
Description
Permissions
Examples
Some tables are followed by a recommendations section that contains important information about conversion implications.
This section provides the following tables for the schema object procedure :
Table 3-2 Comparison of Creating the Procedure Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: CREATE PROCEDURE procedure [@formal_parameter formal_parameter_data type [= default_value] [OUTPUT] [,@formal_parameter formal_parameter_data type [= default_value] [OUTPUT]] ... AS BEGIN] procedural_statements [END] |
Syntax: CREATE [OR REPLACE] PROCEDURE [schema.]procedure [(] [formal_parameter [IN | OUT | IN OUT] formal_parameter_data type] [DEFAULT default_value] [,formal_parameter [IN | OUT | IN OUT]formal_parameter_data type] [DEFAULT default_value]] ... [)] IS | AS [local_variable data type;]... BEGIN PL/SQL statements | PL/SQL blocks END; |
|
Description: The CREATE PROCEDURE statement creates the named stored procedure in the database. You can optionally specify the parameters passed to the procedure as OUTPUT. Values of OUTPUT variables are available to the calling routine after the procedure is executed. The parameters specified without the OUTPUT keyword are considered as input parameters. The keyword AS indicates the start of the body of the procedure. The BEGIN and END keywords that enclose the stored procedure body are optional; all the procedural statements contained in the file after AS are considered part of the stored procedure if BEGIN and END are not used to mark blocks. See the T/SQL and PL/SQL Language Elements section of this chapter for more information about the constructs allowed in T/SQL procedures. |
Description: The OR REPLACE keywords replace the procedure by the new definition if it already exists. The parameters passed to the PL/SQL procedure can be specified as IN (input), OUT (output only), or IN OUT (input and output). In the absence of these keywords, the parameter is assumed to be the "IN" parameter. The keyword IS or AS indicates the start of the procedure. The local variables are declared after the keyword IS or AS and before the keyword BEGIN. The BEGIN and END keywords enclose the body of the procedure. |
|
Permissions: You must have the CREATE PROCEDURE system privilege to create the stored procedures. For Microsoft SQL Server 2005, you must also have ALTER permission on the schema in which the function is being created. |
Permissions: To create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create a procedure in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. |
Recommendations:
Functionally identical parts can be identified in the T/SQL procedure and PL/SQL procedure structure. Therefore, you can automate the conversion of most of the constructs from Microsoft SQL Server or Sybase Adaptive Server to Oracle.
OR REPLACE keywords in an Oracle CREATE PROCEDURE statement provide an elegant way of recreating the procedure. In Microsoft SQL Server or Sybase Adaptive Server, the procedure must be dropped explicitly before replacing it.
Table 3-3 Comparison of Dropping the Procedure Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: DROP PROCEDURE procedure |
Syntax: DROP PROCEDURE [schema.]procedure |
|
Description: The procedure definition is deleted from the data dictionary. All the objects that reference this procedure must have references to this procedure removed |
Description: When a procedure is dropped, Oracle invalidates all the local objects that reference the dropped procedure |
|
Permissions: Procedure owners can drop their own procedures. A DBO can drop any procedure. |
Permissions: The procedure must be in the schema of the user or the user must have the DROP ANY PROCEDURE system privilege to execute this command |
|
Example: DROP PROCEDURE myproc |
Example: DROP PROCEDURE sam.credit; |
Recommendations:
The preceding statement does not have any effect on the conversion process. This information is provided for reference only.
Table 3-4 Comparison of Executing the Procedure Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax:
EXEC [@return_value = ] procedure [[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]] [,[[@formal_parameter = ] {@actual_parameter | constant_literal} [OUTPUT]]] ...
|
Syntax:
procedure
[([{actual_parameter |
constant_literal |
formal_parameter =>
{actual_parameter |
constant_literal}
}]
[,{actual_parameter |
constant_literal |
formal_parameter =>
{actual_parameter |
constant_literal}
}] ....
)]
|
|
Description: Microsoft SQL Server or Sybase Adaptive Server stored procedures can only return integer values to the calling routine using the RETURN statement. In the absence of a RETURN statement, the stored procedure still returns a return status to the calling routine. This value can be captured in the "return_value" variable. The formal_parameter is the parameter in the procedure definition. The actual_parameter is defined in the local block which calls the procedure supplying the value of the actual parameter for the respective formal parameter. The association between an actual parameter and formal parameter can be indicated using either positional or named notation. |
Description: Oracle PL/SQL procedures send data back to the calling routine by means of OUT parameters. Oracle offers functions that are a different type of schema objects. Functions can return an atomic value to the calling routine using the RETURN statement. The RETURN statement can return value of any data type. The formal_parameter is the parameter in the procedure definition. The actual_parameter is defined in the local block which calls the procedure supplying the value of the actual parameter for the respective formal parameter. The association between an actual parameter and formal parameter can be indicated using either positional or named notation. |
|
Positional notation: The actual parameters are supplied to the procedure in the same order as the formal parameters in the procedure definition. Named notation: The actual parameters are supplied to the procedure in an order different than that of the formal parameters in the procedure definition by using the name of the formal parameter as: @formal_parameter = @actual_parameter A constant literal can be specified in the place of the following: '@actual_parameter ' as: @formal_parameter = 10 @formal_parameter = 10 The keyword OUTPUT should be specified if the procedure has to return the value of that parameter to the calling routine as OUTPUT. |
Positional notation: The actual parameters are supplied to the procedure in the same order as the formal parameters in the procedure definition. Named notation: The actual parameters are supplied to the procedure in an order different than that of the formal parameters in the procedure definition by using the name of the formal parameter as: formal_parameter => actual_parameter A constant literal can be specified in the place of the following: as: formal_parameter => 10 If the formal_parameter is specified as OUT or IN OUT in the procedure definition, the value is made available to the calling routine after the execution of the procedure. |
|
Permissions: The user should have the EXECUTE permission on the stored procedure. The user need not have explicit privileges to access the underlying objects referred to within the stored procedure. |
Permissions The user should have the EXECUTE privilege on the named procedure. The user need not have explicit privileges to access the underlying objects referred to within the PL/SQL procedure |
|
Example: Positional notation: EXEC GetEmplName @EmpID EXEC @status = GetAllDeptCodes EXEC @status = UpdateEmpSalary @EmpID, @EmpName EXEC UpdateEmpSalary 13000,'Joe Richards' Named notation: EXEC UpdateEmpSalary @Employee = @EmpName, @Employee_Id = @EmpID Mixed notation: EXEC UpdateEmpSalary @EmpName, @Employee_Id = @EmpID EXEC UpdateEmpSalary @Employee = @EmpName, @EmpID |
Example: Positional notation: credit (accno, accname, amt, retstat); Named notation:
credit (acc_no => accno, acc => accname, amount => amt,
return_status => retstat)
Mixed notation (where positional notation must precede named notation): credit (accno, accname, amount => amt, return_status => retstat) |
Table 3-5 Comparison of Altering the Procedure Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server only. Sybase Adaptive Server does not provide an ALTER PROCEDURE statement.
ALTER PROCEDURE procedure_name
[ @parameter data_type [ = default ] [ OUTPUT ]
[, @parameter data_type [ = default ] [ OUTPUT ]] …
]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
[BEGIN]
Statements
[END]
The system procedure SP_RECOMPILE recompiles the named stored procedure. Example: ALTER PROCEDURE update_employee_salary @employee_id INT, @salary FLOAT AS UPDATE employee SET salary = @salary WHERE employee_id = @employee_id; To recompile a procedure using the sp_recompile system stored procedure: sp_recompile my_proc |
Syntax: ALTER PROCEDURE [schema.]procedure COMPILE |
|
Description: This command causes the recompilation of the procedure. Procedures that become invalid for some reason should be recompiled explicitly using this command. |
Description: This command causes the recompilation of the procedure. Procedures that become invalid for some reason should be recompiled explicitly using this command. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead |
|
Permissions: The owner of the procedure can issue this command |
Permissions: The procedure must be in the user's schema or the user must have the ALTER ANY PROCEDURE privilege to use this command |
|
Example: sp_recompile my_proc |
Example: ALTER PROCEDURE sam.credit COMPILE; |
This section provides the following tables for the schema object Function:
Table 3-6 Comparison of Creating the Function Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: In Microsoft SQL Server or Sybase Adaptive Server, you can convert a stored procedure to a function in Oracle because the stored procedure in Microsoft SQL Server or Sybase Adaptive Server can RETURN an integer value to the calling routine using a RETURN statement. A stored procedure returns a status value to the calling routine even in the absence of a RETURN statement. The returned status is equal to ZERO if the procedure execution is successful or NON-ZERO if the procedure fails for some reason. The RETURN statement can return only integer values |
Syntax:
CREATE [OR REPLACE] FUNCTION [user.]function [(parameter [OUT] data type[,(parameter [IN OUT] data type]...)]
RETURN data type {IS|AS} block
|
|
N/A |
Description: The OR REPLACE keywords replace the function with the new definition if it already exists. Parameters passed to the PL/SQL function can be specified as "IN" (input), "OUT" (output), or "IN OUT" (input and output). In the absence of these keywords the parameter is assumed to be IN. RETURN data type specifies the data type of the function's return value. The data type can be any data type supported by PL/SQL. See "Data Types" for more information about data types. |
|
N/A |
Permissions: To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. |
|
N/A |
Example:
CREATE FUNCTION get_bal
(acc_no IN NUMBER)
RETURN NUMBER
IS
acc_bal NUMBER(11,12);
BEGIN
SELECT balance
INTO acc_bal
FROM accounts
WHERE account_id = acc_no;
RETURN(acc_bal);
END;
|
Table 3-7 Comparison of Dropping the Function Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
N/A |
Syntax: DROP FUNCTION [schema.]function |
|
N/A |
Description: When a function is dropped, Oracle invalidates all the local objects that reference the dropped function. |
|
N/A |
Permissions: The function must be in the schema of the user or the user must have the DROP ANY PROCEDURE system privilege to execute this command |
|
N/A |
Example: DROP FUNCTION sam.credit; |
Table 3-8 Comparison of Executing the Function Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
N/A |
Syntax:
function [({actual_parameter | constant_literal}...)]
|
|
N/A |
Description: Functions can return an atomic value to the calling routine using the RETURN statement. A function can be called as part of an expression. This is a very powerful concept. All the Microsoft SQL Server or Sybase Adaptive Server built-in functions can be coded using PL/SQL, and these functions can be called like any other built-in functions in an expression, starting with Oracle. |
|
N/A |
Permissions: You should have the EXECUTE privilege on the function to execute the named function. You need not have explicit privileges to access the underlying objects that are referred to within the PL/SQL function. |
|
N/A |
Example: 1) IF sal_ok (new_sal, new_title) THEN .... END IF; 2) promotable:= sal_ok(new_sal, new_title) AND (rating>3); where sal_ok is a function that returns a BOOLEAN value. |
Table 3-9 Comparison of Altering the Function Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server 7.0
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
N/A |
Syntax: ALTER FUNCTION [schema.]function COMPILE |
|
N/A |
Description: This command causes the recompilation of a function. Functions become invalid if the objects that are referenced from within the function are dropped or altered. Functions that become invalid for some reason should be recompiled explicitly using this command. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead. |
|
N/A |
Permissions: The function must be in the user's schema or the user must have the ALTER ANY PROCEDURE privilege to use this command |
|
N/A |
Example: ALTER FUNCTION sam.credit COMPILE |
This section provides the following tables for the schema object Package:
Table 3-10 Comparison of Creating the Package Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax:
CREATE [OR REPLACE] PACKAGE [user.]package {IS | AS} {variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification | [{variable_declaration | cursor_specification | exception_declaration | record_declaration | plsql_table_declaration | procedure_specification | function_specification}; ]...} END [package]
|
|
N/A |
Description: This is the external or public part of the package. CREATE PACKAGE sets up the specification for a PL/SQL package which can be a group of procedures, functions, exception, variables, constants, and cursors. Functions and procedures of the package can share data through variables, constants, and cursors. The OR REPLACE keywords replace the package by the new definition if it already exists. This requires recompilation of the package and any objects that depend on its specification. |
|
N/A |
Permissions: To create a package in the user's own schema, the user must have the CREATE PROCEDURE system privilege. To create a package in another user's schema, the user must have the CREATE ANY PROCEDURE system privilege. |
|
N/A |
Example:
CREATE PACKAGE emp_actions AS
-- specification
TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);
CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp;
PROCEDURE hire_employee
(ename CHAR,
job CHAR,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire-employee (emp_id NUMBER);
END emp_actions;
|
Table 3-11 Comparison of Dropping the Package Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax: DROP PACKAGE [BODY] [schema.]package |
|
N/A |
Description: The BODY option drops only the body of the package. If you omit BODY, Oracle drops both the body and specification of the package. If you drop the body and specification of the package, Oracle invalidates any local objects that depend on the package specification.
When a package is dropped, Oracle invalidates all the local objects that reference the dropped package. |
|
N/A |
Permissions: The package must be in the schema of the user or the user must have the DROP ANY PROCEDURE system privilege to execute this command. |
|
N/A |
Example: DROP PACKAGE emp_actions; |
Table 3-12 Comparison of Altering the Package Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax: ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY] |
|
N/A |
Description: Packages that become invalid for some reason should be recompiled explicitly using this command. This command causes the recompilation of all package objects together. You cannot use the ALTER PROCEDURE or ALTER FUNCTION commands to individually recompile a procedure or function that is part of a package. PACKAGE, the default option, recompiles the package body and specification. BODY recompiles only the package body. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead. |
|
N/A |
Permissions: The package must be in the user's schema or the user must have the ALTER ANY PROCEDURE privilege to use this command. |
|
N/A |
Example: ALTER PACKAGE emp_actions COMPILE PACKAGE |
This section provides the following tables for the schema object Package Body:
Table 3-13 Comparison of Creating the Package Body Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax:
CREATE [OR REPLACE] PACKAGE BODY [schema.]package {IS | AS} pl/sql_package_body
|
|
N/A |
Description: This is the internal or private part of the package. CREATE PACKAGE creates the body of a stored package. OR REPLACE recreates the package body if it already exists. If you change a package body, Oracle recompiles it.
package is the of the package to be created.
|
|
N/A |
Permissions: To create a package in your own schema, you must have the CREATE PROCEDURE privilege. To create a package in another user's schema, you must have the CREATE ANY PROCEDURE privilege. |
|
N/A |
Example:
CREATE PACKAGE BODY emp_actions AS
-- body
CURSOR desc_salary (emp_id NUMBER)
RETURN EmpRecTyp IS
SELECT empno, sal FROM emp
ORDER BY sal DESC;
PROCEDURE hire_employee
(ename CHAR,
job CHAR,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES
(empno_seq.NEXTVAL, ename,
job, mgr, SYSDATE, sal,
comm, deptno);
END hire_employee;
PROCEDURE fire_employee
(emp_id NUMBER) IS
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
END fire_employee;
END emp_actions;
|
Table 3-14 Comparison of Dropping the Package Body Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax: DROP PACKAGE [BODY] [schema.]package |
|
N/A |
Description: The
When a package is dropped, Oracle invalidates all the local objects that reference the dropped package. |
|
N/A |
Permissions: The package must be in the your own schema or you must have the DROP ANY PROCEDURE system privilege to execute this command. |
|
N/A |
Example: DROP PACKAGE BODY emp_actions; |
Table 3-15 Comparison of Altering the Package Body Schema Object in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Syntax: Microsoft SQL Server or Sybase Adaptive Server does not support this concept. |
Syntax: ALTER PACKAGE [user.]package COMPILE [PACKAGE | BODY] |
|
N/A |
Description: Packages that become invalid for some reason should be recompiled explicitly using this command. This command causes the recompilation of all package objects together. You cannot use the ALTER PROCEDURE or ALTER FUNCTION commands to individually recompile a procedure or function that is part of a package. PACKAGE, the default option, recompiles the package body and specification. BODY recompiles only the package body. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead. |
|
N/A |
Permissions: The package must be your own schema or you must have the ALTER ANY PROCEDURE privilege to use this command. |
|
N/A |
Example: ALTER PACKAGE emp_actions COMPILE BODY |
This section provides information about the Microsoft SQL Server or Sybase Adaptive Server constructs and equivalent Oracle constructs generated by SQL Developer. The conversions of the following constructs are discussed in detail:
Listed is the syntax for the Microsoft SQL Server or Sybase Adaptive Server constructs and their Oracle equivalents, as well as comments about conversion considerations.
The procedures in the Oracle column are the direct output of SQL Developer. In general, SQL Developer deals with the Microsoft SQL Server or Sybase Adaptive Server T/SQL constructs in one of the following ways:
The ANSI-standard SQL statements are converted to PL/SQL because it supports ANSI-standard SQL.
Microsoft SQL Server or Sybase Adaptive Server-specific constructs are converted into PL/SQL constructs if the equivalent constructs are available in PL/SQL.
Some Microsoft SQL Server or Sybase Adaptive Server-specific constructs are ignored and appropriate comments are incorporated in the output file.
Constructs that require manual conversion are wrapped around with proper comments in the output file.
For Microsoft SQL Server or Sybase Adaptive Server-specific constructs that result in syntax errors, an appropriate error message is displayed including the line number.
Table 3-16 Comparison of CREATE PROCEDURE Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1 AS RETURN 0 |
CREATE OR REPLACE FUNCTION proc1 RETURN NUMBER AS BEGIN RETURN 0; END; |
Comments
The REPLACE keyword is added to replace the procedure, function, or package if it already exists.
The procedure is translated to an Oracle function because it returns a value.
Table 3-17 Comparison of Parameter Passing in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
@x int=-1,
@y money,
@z bit OUT,
@a char(20) = 'TEST'
AS
RETURN 0
|
CREATE OR REPLACE FUNCTION proc1 ( v_x IN NUMBER DEFAULT -1, v_y IN NUMBER, v_z OUT NUMBER, v_a IN CHAR DEFAULT 'TEST' ) RETURN NUMBER AS BEGIN RETURN 0; END; |
Comments
Parameter passing is almost the same in Microsoft SQL Server or Sybase Adaptive Server and Oracle. By default, all the parameters are INPUT parameters, if not specified otherwise.
The value of the INPUT parameter cannot be changed from within the PL/SQL procedure. Thus, an INPUT parameter cannot be assigned any values nor can it be passed to another procedure as an OUT parameter. In Oracle, only IN parameters can be assigned a default value.
The @ sign in a parameter name declaration is removed in Oracle.
In Oracle, the parameter data type definition does not include length/size.
Microsoft SQL Server or Sybase Adaptive Server data types are converted to Oracle base data types. For example, all Microsoft SQL Server or Sybase Adaptive Server numeric data types are converted to NUMBER and all alphanumeric data types are converted to VARCHAR2 and CHAR in Oracle.
Table 3-18 Comparison of DECLARE Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
DECLARE
@x int,
@y money,
@z bit,
@a char(20)
RETURN 0
GO
|
CREATE OR REPLACE PROCEDURE proc1 AS v_x NUMBER(10,0); v_y NUMBER(19,2); v_z NUMBER(1,0); v_a CHAR(20); BEGIN RETURN; END; |
Comments
Microsoft SQL Server or Sybase Adaptive Server and Oracle follow similar rules for declaring local variables.
SQL Developer overrides the scope rule for variable declarations. As a result, all the local variables are defined at the top of the procedure body in Oracle.
Table 3-19 Comparison of IF Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Example 1: CREATE PROC proc1 @Flag int = 0 AS BEGIN DECLARE @x int IF ( @Flag=0 ) SELECT @x = -1 ELSE SELECT @x = 10 END |
Example 1:
CREATE OR REPLACE PROCEDURE proc1
(
v_Flag IN NUMBER DEFAULT 0
)
AS
v_x NUMBER(10,0);
BEGIN
IF ( v_Flag = 0 ) THEN
v_x := -1;
ELSE
v_x := 10;
END IF
|
|
Example 2: CREATE PROC proc1 @Flag char(2) = '' AS BEGIN DECLARE @x int IF ( @Flag='' ) SELECT @x = -1 ELSE IF (@Flag = 'a') SELECT @x = 10 ELSE IF (@Flag = 'b') SELECT @x = 20 END |
Example 2:
CREATE OR REPLACE PROCEDURE proc1
(
v_Flag IN CHAR DEFAULT ''
)
AS
v_x NUMBER(10,0);
BEGIN
IF ( v_Flag = '' ) THEN
v_x := -1;
ELSE
IF ( v_Flag = 'a' ) THEN
v_x := 10;
ELSE
IF ( v_Flag = 'b' ) THEN
v_x := 20;
END IF;
END IF;
END IF;
END;
|
|
Example 3: CREATE PROC proc1 AS BEGIN DECLARE @x int IF EXISTS ( SELECT * FROM table2 ) SELECT @x = -1 END |
Example 3:
CREATE OR REPLACE PROCEDURE proc1
AS
v_x NUMBER(10,0);
v_temp NUMBER(1, 0) := 0;
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT *
FROM table2 );
IF v_temp = 1 THEN
v_x := -1;
END IF;
END;
|
|
Example 4:
CREATE PROC proc1 @basesal money, @empid int
AS
BEGIN
IF (select sal from emp where empid = @empid) < @basesal
UPDATE emp
SET sal_flag = -1
WHERE empid = @empid
END
|
Example 4:
CREATE OR REPLACE PROCEDURE proc1
(
v_basesal IN NUMBER,
v_empid IN NUMBER
)
AS
v_temp NUMBER(1, 0) := 0;
BEGIN
SELECT 1 INTO v_temp
FROM DUAL
WHERE ( SELECT sal
FROM emp
WHERE empid = v_empid ) < v_basesal;
IF v_temp = 1 THEN
UPDATE emp
SET sal_flag = -1
WHERE empid = v_empid;
END IF;
END;
|
Comments
IF statements in Microsoft SQL Server or Sybase Adaptive Server and Oracle are nearly the same except in the following two cases:
If EXISTS(...) in Microsoft SQL Server or Sybase Adaptive Server does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO WHERE EXISTS clause and an IF statement as shown in Example 3 in Table 3-19.
IF (SELECT... ) with comparison does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO...WHERE... clause, as shown in Example 4 in Table 3-19.
Table 3-20 Comparison of RETURN Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
@x int
AS
IF @x = -1
RETURN 25022
ELSE
RETURN 25011
|
CREATE OR REPLACE FUNCTION proc1
(
v_x IN NUMBER
)
AS
BEGIN
IF v_x = -1 THEN
RETURN 25022;
ELSE
RETURN 25011;
END IF;
END;
|
Comments
A RETURN statement is used to return a single value back to the calling program and works the same in both databases. Microsoft SQL Server or Sybase Adaptive Server can return only the numeric data type, while Oracle can return any of the server data types or the PL/SQL data types.
In a PL/SQL procedure, a RETURN statement can only return the control back to the calling program without returning any data. SQL Developer translates procedures returning values to functions automatically.
Table 3-21 Comparison of RAISERROR Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
RAISERROR 12345 "No Employees found"
|
CREATE OR REPLACE PROCEDURE PROC1 AS BEGIN raise_application_error(-20999, 'No Employees found'); END PROC1; |
Comments
Microsoft SQL Server or Sybase Adaptive Server uses RAISERROR to notify the client program of any error that occurred. This statement does not end the execution of the procedure, and the control is passed to the next statement.
PL/SQL provides similar functionality with RAISE_APPLICATION_ERROR statements.However, it ends the execution of the stored subprogram and returns the control to the calling program. It is equivalent to a combination of RAISERROR and a RETURN statement.
Table 3-22 Comparison of EXECUTE Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1 AS EXEC SetExistFlag EXEC SetExistFlag yes=@yes, @Status OUT EXEC @Status = RecordExists EXEC SetExistFlag @yes |
CREATE OR REPLACE PROCEDURE proc1 AS BEGIN SetExistFlag; SetExistFlag(yes=>v_yes,Status); Status:=RecordExists; SetExistFlag(v_yes); END proc1; |
Comments
The EXECUTE statement is used to execute another stored procedure from within a procedure. In PL/SQL, the procedure is called by its name within the PL/SQL block.
SQL Developer converts the parameter-calling convention to be either positional, named, or mixed. For information on parameter-calling conventions, see "Schema Objects".
Table 3-23 Comparison of WHILE Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Example 1:
CREATE PROC proc1
@i int
AS
WHILE @i > 0
BEGIN
print 'Looping inside WHILE....'
SELECT @i = @i + 1
END
|
Example 1:
CREATE OR REPLACE PROCEDURE proc1
(
iv_i IN NUMBER
)
AS
v_i NUMBER(10,0) :=iv_i;
BEGIN
WHILE v_i > 0
LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....');
v_i := v_i + 1;
END;
END LOOP;
END;
|
|
Example 2:
CREATE PROC proc1
@i int,
@y int
AS
WHILE @i > 0
BEGIN
print 'Looping inside WHILE....'
SELECT @i = @i + 1
END
|
Example 2:
CREATE OR REPLACE PROCEDURE proc1
(
iv_i IN NUMBER,
v_y IN NUMBER
)
AS
v_i NUMBER(10,0):=iv_i;
BEGIN
WHILE v_i > 0
LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('Looping inside WHILE....');
v_i := v_i + 1;
END;
END LOOP;
END;
|
|
Example 3: CREATE PROC proc1 AS DECLARE @sal money SELECT @sal = 0 WHILE EXISTS(SELECT * FROM emp where sal < @sal ) BEGIN SELECT @sal = @sal + 99 DELETE emp WHERE sal < @sal END GO |
Example 3:
CREATE OR REPLACE PROCEDURE proc1
AS
v_sal NUMBER(19,2);
v_temp NUMBER(1, 0) := 0;
BEGIN
v_sal := 0;
LOOP
v_temp := 0;
SELECT 1 INTO v_temp
FROM DUAL
WHERE EXISTS ( SELECT *
FROM emp
WHERE sal < v_sal );
IF v_temp != 1 THEN
EXIT;
END IF;
BEGIN
v_sal := v_sal + 99;
DELETE emp
WHERE sal < v_sal;
END;
END LOOP;
END;
|
|
Example 4: CREATE PROC proc1 AS DECLARE @sal money WHILE (SELECT count (*) FROM emp ) > 0 BEGIN SELECT @sal = max(sal) from emp WHERE stat = 1 DELETE emp WHERE sal < @sal END GO |
Example 4:
CREATE OR REPLACE PROCEDURE proc1
AS
v_sal NUMBER(19,2);
v_temp NUMBER(1, 0) := 0;
BEGIN
LOOP
v_temp := 0;
SELECT 1 INTO v_temp
FROM DUAL
WHERE ( SELECT COUNT(*)
FROM emp ) > 0;
IF v_temp != 1 THEN
EXIT;
END IF;
BEGIN
SELECT MAX(sal)
INTO v_sal
FROM emp
WHERE stat = 1;
DELETE emp
WHERE sal < v_sal;
END;
END LOOP;
END;
|
Comments
SQL Developer can convert most WHILE constructs. However, the CONTINUE within a WHILE loop in Microsoft SQL Server or Sybase Adaptive Server does not have a direct equivalent in PL/SQL. It is simulated using the GOTO statement with a label.
Table 3-24 Comparison of GOTO Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1 @Status int
AS
DECLARE @j int
IF @Status = -1
GOTO Error
SELECT @j = -1
Error:
SELECT @j = -99
|
CREATE OR REPLACE PROCEDURE proc1
(
v_Status IN NUMBER
)
AS
v_j NUMBER(10,0);
BEGIN
IF v_Status = -1 THEN
GOTO Error;
END IF;
v_j := -1;
<<Error>>
v_j := -99;
END;
|
Comments
The GOTO <label> statement is converted automatically. No manual changes are required.
Table 3-25 Comparison of @@Rowcount and @@Error Variables in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
DECLARE @x int
SELECT @x=count(*) FROM emp
IF @@rowcount = 0
print 'No rows found.'
IF @@error = 0
print 'No errors.'
|
CREATE OR REPLACE PROCEDURE proc1
AS
v_sys_error NUMBER := 0;
v_x NUMBER(10,0);
BEGIN
BEGIN
SELECT COUNT(*)
INTO v_x
FROM emp ;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No rows found.');
END IF;
IF v_sys_error = 0 THEN
DBMS_OUTPUT.PUT_LINE('No errors.');
END IF;
END;
|
Comments
@@rowcount is converted to the PL/SQL cursor attribute SQL%ROWCOUNT.
@@error is converted to v_sys_error, which contains the value returned by the SQLCODE function. The value returned by SQLCODE should only be assigned within an exception block; otherwise, it returns a value of zero.
Table 3-26 Comparison of ASSIGNMENT Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1 AS DECLARE @x int SELECT @x = -1 SELECT @x=sum(salary) FROM employee |
CREATE OR REPLACE PROCEDURE proc1
AS
v_x NUMBER(10,0);
BEGIN
v_x := -1;
SELECT SUM(salary)
INTO v_x
FROM employee ;
END;
|
Comments
Assignment in Microsoft SQL Server or Sybase Adaptive Server is done using the SELECT statement, as illustrated in Table 3-26.
PL/SQL assigns values to a variable as follows:
It uses the assignment statement to assign the value of a variable or an expression to a local variable. It assigns a value from a database using the SELECT..INTO clause. This requires that the SQL returns only one row.
Table 3-27 Comparison of SELECT Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
|
Example 1 CREATE PROC proc1 AS SELECT ename FROM employee |
Example 1
CREATE OR REPLACE PROCEDURE proc1
(
cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cv_1 FOR
SELECT ename
FROM employee ;
END;
|
|
Example 2 CREATE PROC proc1 AS DECLARE @name char(20) SELECT @id = id FROM employee RETURN id |
Example 2
CREATE OR REPLACE FUNCTION proc1
AS
v_name CHAR(20);
BEGIN
SELECT id
INTO v_id
FROM employee ;
RETURN v_id;
END;
|
Comments
Because of the differences in their architectures, Microsoft SQL Server or Sybase Adaptive Server stored procedures return data to the client program in a different way than Oracle.
Microsoft SQL Server or Sybase Adaptive Server and Oracle can all pass data to the client using output parameters in the stored procedures. Microsoft SQL Server or Sybase Adaptive Server 6.5 uses another method known as result sets to transfer the data from the server to client.
Table 3-28 Comparison of SELECT Statement with GROUP BY Clause in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1 AS DECLARE @ename char(20) DECLARE @salary int SELECT @ename=ename, @salary=salary FROM emp WHERE salary > 100000 GROUP BY deptno |
CREATE OR REPLACE PROCEDURE proc1
AS
v_ename CHAR(20);
v_salary NUMBER(10,0);
BEGIN
SELECT ename,
salary
INTO v_ename,
v_salary
FROM emp
WHERE salary > 100000
GROUP BY deptno;
END;
|
Comments
T/SQL allows GROUP BY statements where the column used in the GROUP BY clause does not need to be part of the SELECT list. PL/SQL does not allow this type of GROUP BY clause.
SQL Developer converts this type of SELECT statement to PL/SQL. However, the equivalent PL/SQL statement returns an error in Oracle.
Table 3-29 Comparison of Column Aliases in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
@Status int=0
AS
SELECT x=sum(salary) FROM employee
|
CREATE OR REPLACE PROCEDURE proc1
(
v_Status IN NUMBER DEFAULT 0,
cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cv_1 FOR
SELECT SUM(salary) x
FROM employee ;
END;
|
Comments
SQL Developer can convert Microsoft SQL Server or Sybase Adaptive Server-specific column aliases to the equivalent Oracle format. No manual changes are required.
Table 3-30 Comparison of UPDATE with FROM Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
UPDATE table1
SET col1 = 1
FROM table1, table2
WHERE table1.id = table2.id
|
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
UPDATE table1
SET ( col1 ) = ( SELECT 1
FROM table1 ,
table2
WHERE table1.id = table2.id );
END;
|
Table 3-31 Comparison of DELETE with FROM Statement in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
DELETE FROM table1
FROM table1, table2
WHERE table1.id = table2.id
|
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DELETE FROM table1
WHERE ROWID IN
(SELECT table1.ROWID
FROM table1, table2
WHERE table1.id = table2.id);
END;
|
Table 3-32 Comparison of Temporary Tables in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC proc1
AS
SELECT col1, col2
INTO #Tab
FROM table1
WHERE table1.id = 100
|
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
DELETE FROM tt_Tab;
INSERT INTO tt_Tab (
SELECT col1,
col2
FROM table1
WHERE table1.id = 100 );
END;
|
Comments
Temporary tables are supported by Oracle, and SQL Developer uses this feature. The DDL of the temporary table is extracted and generated as a standalone object.
Table 3-33 Comparison of Cursor Handling Result Set in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
CREATE PROC cursor_demo AS DECLARE @empno INT DECLARE @ename CHAR(100) DECLARE @sal FLOAT DECLARE cursor_1 CURSOR FOR SELECT empno, ename, sal FROM emp OPEN cursor_1 FETCH cursor_1 INTO @empno, @ename, @sal CLOSE cursor_1 DEALLOCATE CURSOR cursor_1 |
CREATE OR REPLACE PROCEDURE cursor_demo
AS
CURSOR cursor_1
IS SELECT empno,
ename,
sal
FROM emp ;
v_empno NUMBER(10,0);
v_ename CHAR(100);
v_sal NUMBER;
BEGIN
OPEN cursor_1;
FETCH cursor_1 INTO v_empno,v_ename,v_sal;
CLOSE cursor_1;
END;
|
Comments
Microsoft SQL Server and Sybase Adaptive Server introduced cursors in T/SQL. Syntactical conversion of cursors from Microsoft SQL Server or Sybase Adaptive Server to Oracle is straightforward.
Note:
In PL/SQL, deallocation of cursors is not required because it happens automatically.T/SQL is the Microsoft SQL Server or Sybase Adaptive Server procedural SQL language and PL/SQL is the Oracle procedural SQL language. This section discusses the following T/SQL and PL/SQL language elements:
Microsoft SQL Server or Sybase Adaptive Server
Microsoft SQL Server or Sybase Adaptive Server offers two different transaction models: the ANSI-standard implicit transaction model and the explicit transaction model.
Microsoft SQL Server or Sybase Adaptive Server provides options to support ANSI-standard transactions. These options can be set or un-set using the SET command.
The following SET command sets the implicit transaction mode:
set chained on
The following SET command sets the isolation level to the desired level:
set transaction isolation level {1|3}
isolation level 1 prevents dirty reads. Isolation level 2 prevents un-repeatable reads. Isolation level 3 prevents phantoms. Isolation level 3 is required by ANSI standards. For Microsoft SQL Server or Sybase Adaptive Server, the default is isolation level 1.
To implement isolation level 3, Microsoft SQL Server or Sybase Adaptive Server applies HOLDLOCK to all the tables taking part in the transaction. In Microsoft SQL Server or Sybase Adaptive Server, HOLDLOCK, along with page-level locks, can block users for a considerable length of time, causing poor response time.
If the Microsoft SQL Server or Sybase Adaptive Server application implements ANSI-standard chained (implicit) transactions with isolation level 3, the application migrates smoothly to Oracle because Oracle implements the ANSI-standard implicit transaction model, which ensures repeatable reads.
In a non-ANSI standard application, Microsoft SQL Server or Sybase Adaptive Server transactions are explicit. A logical transaction has to be explicitly started with the statement BEGIN TRANSACTION. The transaction is committed with a COMMIT TRANSACTION or rolled back with a ROLLBACK TRANSACTION statement. The transactions can be named. For example, the following statement starts a transaction named
account_tran. BEGIN TRANSACTION account_tran
The explicit transaction mode allows nested transactions. However, the nesting is only syntactical. Only outermost BEGIN TRANSACTION and COMMIT TRANSACTION statements actually create and commit the transaction. This could be confusing as the inner COMMIT TRANSACTION does not actually commit.
The following example illustrates the nested transactions:
BEGIN TRANSACTION
/* T/SQL Statements */
BEGIN TRANSACTION
/* T/SQL Statements */
BEGIN TRANSACTION account_tran
/* T/SQL Statements */
IF SUCCESS
COMMIT TRANSACTION account_tran
ELSE
ROLLBACK TRANSACTION account_tran
END IF
/* T/SQL Statements */
IF SUCCESS
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END IF
/* T/SQL Statements */
COMMIT TRANSACTION
When BEGIN TRANSACTION and COMMIT TRANSACTION statements are nested, the outermost pair creates and commits the transaction while the inner pairs only keep track of nesting levels. The transaction is not committed until the outermost COMMIT TRANSACTION statement is executed. Normally the nesting of the transaction occurs when stored procedures containing BEGIN TRANSACTION /COMMIT TRANSACTION statements call other procedures with transaction-handling statements. The global variable @@trancount keeps track of the number of currently active transactions for the current user. If you have more than one open transaction, you need to ROLLBACK, then COMMIT.
The named and unnamed inner COMMIT TRANSACTION statements have no effect. The inner ROLLBACK TRANSACTION statements without the name roll back the statements to the outermost BEGIN TRANSACTION statement and the current transaction is canceled. The named inner ROLLBACK TRANSACTION statements cancel the respective named transactions.
Oracle
Oracle applies ANSI-standard implicit transaction methods. A logical transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK, or connection to the database. A transaction ends with a COMMIT, ROLLBACK, or disconnection from the database. An implicit COMMIT statement is issued before and after each DDL statement. The implicit transaction model prevents artificial nesting of transactions because only one logical transaction per session can be in effect. The user can set SAVEPOINT in a transaction and roll back a partial transaction to the SAVEPOINT.
For example:
UPDATE test_table SET col1='value_1'; SAVEPOINT first_sp; UPDATE test_table SET col1='value_2'; ROLLBACK TO SAVEPOINT first_sp; COMMIT; /* col1 is 'value_1'*/
Logical transactions are handled differently in Microsoft SQL Server or Sybase Adaptive Server and Oracle. In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit by default. Oracle implements ANSI-standard implicit transactions. This prevents a direct conversion from T/SQL transaction-handling statements to PL/SQL transaction-handling statements.
Also, Microsoft SQL Server or Sybase Adaptive Server requires that transactions in stored procedures be allowed to nest, whereas Oracle does not support transaction nesting.
Table 3-34 compares Microsoft SQL Server or Sybase Adaptive Server to Oracle transaction-handling statements:
Table 3-34 Comparison of Transaction-Handling Statements in Oracle and Microsoft SQL Server or Sybase Adaptive Server
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
BEGIN TRAN |
|
BEGIN TRAN tran_1 |
SAVEPOINT tran_1 |
COMMIT TRAN (for the transaction with nest level=1) |
COMMIT |
COMMIT TRAN (for the transaction with nest level>1) |
|
COMMIT TRAN tran_1 (for the transaction with nest level=1) |
COMMIT |
COMMIT TRAN tran_1 (for the transaction with nest level>1) |
|
ROLLBACK TRAN |
ROLLBACK |
ROLLBACK TRAN tran_1 |
ROLLBACK TO SAVEPOINT tran_1 |
At the time of conversion, SQL Developer cannot determine the nest level of the current transaction-handling statement. The variable @@trancount is a runtime environment variable.
Table 3-35 shows the currently implemented Microsoft SQL Server or Sybase Adaptive Server to Oracle conversion strategy for the transaction-handling statements
Table 3-35 Conversion Strategy for Transaction-Handling Statements
| Microsoft SQL Server or Sybase Adaptive Server | Oracle |
|---|---|
BEGIN TRAN |
/*BEGIN TRAN >>> statement ignored <<<*/ |
BEGIN TRAN tran_1 |
SAVEPOINT tran_1; |
COMMIT TRAN (for the transaction with nest level=1) |
COMMIT WORK; |
COMMIT TRAN (for the transaction with nest level>1) |
COMMIT WORK; |
COMMIT TRAN tran_1 (for the transaction with nest level=1) |
COMMIT WORK; |
COMMIT TRAN tran_1 (for the transaction with nest level>1) |
COMMIT WORK; |
ROLLBACK TRAN |
ROLLBACK WORK; |
ROLLBACK TRAN tran_1 |
ROLLBACK TO SAVEPOINT tran_1 |
SAVE TRAN tran_1 |
SAVEPOINT tran_1 |
Because of the difference in the way the two databases handle transactions, you may want to consider some reorganization of the transactions.
Try to design client/server applications so that the transaction-handling statements are part of the client code rather than the stored procedure code. This strategy should work because the logical transactions are almost always designed by the user and should be controlled by the user.
For the conversion of stored procedures, consider setting a SAVEPOINT at the beginning of the procedures, and roll back only to the SAVEPOINT. In Microsoft SQL Server or Sybase Adaptive Server, make the changes so that at least the outermost transaction is controlled in the client application.
Microsoft SQL Server or Sybase Adaptive Server
In Microsoft SQL Server or Sybase Adaptive Server, you must check for errors after each SQL statement because control is passed to the next statement regardless of any error conditions generated by the previous statement. The client ERROR_HANDLER routine is invoked as a call-back routine if any server error occurs, and the error conditions can be handled in the call-back routine.
Stored procedures use the RAISERROR statement to notify the client of any error condition. This statement does not cause the control to return to the calling routine.
Microsoft SQL Server or Sybase Adaptive Server allows you to customize the error messages using a system table. The system procedures allow the user to add error messages to this table.
Oracle
In Oracle, each SQL statement is automatically checked for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler if one exists. This frees you from needing to check the status of every SQL statement. For example, if a SELECT statement does not find any row in the database, an exception is raised. The corresponding exception handler part of the block should include the code to deal with this error. The built-in RAISE_APPLICATION_ERROR procedure notifies the client of the server error condition and returns immediately to the calling routine.
Oracle places an implicit SAVEPOINT at the beginning of a procedure. The built-in RAISE_APPLICATION_ERROR procedure rolls back to this SAVEPOINT or the last committed transaction within the procedure. The control is returned to the calling routine.
The Oracle RAISE_APPLICATION_ERROR statement allows the user to customize the error message. If an exception is raised, SQLCODE is returned automatically by PL/SQL to the caller. It keeps propagating until it is handled.
Recommendations
To simulate Microsoft SQL Server or Sybase Adaptive Server behavior in Oracle, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with the exceptions that need to be trapped for the SQL statement.
See "T/SQL Versus PL/SQL Constructs" for more information about the extra code required to simulate Microsoft SQL Server or Sybase Adaptive Server behavior.
If the RAISERROR statement in a Microsoft SQL Server or Sybase Adaptive Server stored procedure is immediately followed by the RETURN statement, these two statements can be converted to the Oracle RAISE_APPLICATION_ERROR statement.
You can customize error messages with the help of a user-defined table. You can write standard routines to add and retrieve error messages to this table. This method serves a two-fold purpose: it ensures that the system is portable, and it gives the administrator centralized control over the error messages.
Microsoft SQL Server or Sybase Adaptive Server
In Microsoft SQL Server or Sybase Adaptive Server, the following global variables are particularly useful in the conversion process:
@@error:
The server error code indicating the execution status of the most recently executed T/SQL statement. For code examples, see "@@Rowcount and @@Error Variables".
@@identity:
Returns the last identity value generated by the statement. It does not revert to a previous setting due to ROLLBACKS or other transactions.
@@rowcount:
The number of rows affected by the most recently executed T/SQL statement. For code examples, see "@@Rowcount and @@Error Variables".
@@servername:
The name of the local Microsoft SQL Server or Sybase Adaptive Server server.
@@sqlstatus:
The status information resulting from the last FETCH statements.
@@tranchained:
The current transaction mode of the T/SQL procedure. If @@tranchained returns 1, the TL/SQL procedure is in chained, or implicit transaction mode.
@@trancount:
Keeps track of the nesting level for the nested transactions for the current user.
@@transtate:
The current state of the transaction.
Oracle
SQLCODE:
The server error code indicating the execution status of the most recently executed PL/SQL statement.
SQL%ROWCOUNT:
The variable attached to the implicit cursor associated with each SQL statement executed from within the PL/SQL procedures. This variable contains the number of rows affected by the execution of the SQL statement attached to the implicit cursor.
Recommendations:
The @@error variable has a direct equivalent in Oracle, and that is the SQLCODE function. The SQLCODE function returns the server error code.
The SQL%ROWCOUNT variable in Oracle is functionally equivalent to @@rowcount.
There are many more special global variables available with PL/SQL. Not all those variables are listed here. There are more special global variables available in T/SQL also. Not all those variables are listed here because they do not play a major role in the conversion process.
See "Data Manipulation Language" for a discussion of Microsoft SQL Server or Sybase Adaptive Server and Oracle operators.
See "Data Manipulation Language" for a discussion of built-in functions in Microsoft SQL Server or Sybase Adaptive Server and Oracle.
Microsoft SQL Server or Sybase Adaptive Server allows DDL constructs to be part of the stored procedures. Oracle allows DDL statements as part of the dynamic SQL. Oracle issues an implicit COMMIT statement after each DDL statement.
SQL Developer currently converts DDL constructs within stored procedures into standalone DDL constructs. The DDL is removed from the body of the stored procedure and is created before the procedure. Support for dynamic SQL is planned for a future release.