Skip Headers
Oracle® Database SQL Developer Supplementary Information for Microsoft SQL Server and Sybase Adaptive Server Migrations
Release 1.5

Part Number E12156-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Triggers and Stored Procedures

This chapter describes the differences between Microsoft SQL Server or Sybase Adaptive Server and Oracle. It contains the following sections:

3.1 Triggers

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:

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:

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.

3.2 Stored Procedures

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.

3.2.1 Individual SQL Statements

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:

3.2.1.1 Microsoft SQL Server or Sybase Adaptive Server:

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

3.2.1.2 Oracle:

DELETE sales
WHERE ROWID IN
( SELECT sales.ROWID
  FROM sales ,
       titles
     WHERE sales.title_id = titles.title_id
             AND titles.TYPE = 'business' );

3.2.1.3 Microsoft SQL Server or Sybase Adaptive Server:

UPDATE titles
SET price = price + author_royalty
FROM titles, title_author
WHERE titles.title.id = title_author.title_id

3.2.1.4 Oracle:

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.

3.2.2 Logical Transaction Handling

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.

3.2.2.1 Transaction-Handling Statements

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.

3.2.3 Error Handling Within the Stored Procedure

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.

3.2.3.1 RAISERROR Statement

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.

3.2.3.2 Customized Error Messages

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.

3.3 Data Types

This section provides information about data types under the following headings:

3.3.1 Local Variable

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

3.3.2 Server Data Types

See the "Data Types" for a list of Microsoft SQL Server or Sybase Adaptive Server data types and their equivalent Oracle data types.

3.3.3 Composite 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.


3.4 Schema Objects

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:

Some tables are followed by a recommendations section that contains important information about conversion implications.

3.4.1 Procedure

This section provides the following tables for the schema object procedure :

3.4.1.1 Create

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.

3.4.1.2 Drop

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.

3.4.1.3 Execute

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)

3.4.1.4 Alter

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;

3.4.2 Function

This section provides the following tables for the schema object Function:

3.4.2.1 Create

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;

3.4.2.2 Drop

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;

3.4.2.3 Execute

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.


3.4.2.4 Alter

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

3.4.3 Package

This section provides the following tables for the schema object Package:

3.4.3.1 Create

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;

3.4.3.2 Drop

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.

schema. is the schema containing the package. If you omit schema, Oracle assumes the package is in your own schema.

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;

3.4.3.3 Alter

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

3.4.4 Package Body

This section provides the following tables for the schema object Package Body:

3.4.4.1 Create

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.

schema. is the schema to contain the package. If omitted, the package is created in your current schema.

package is the of the package to be created.

pl/sql_package_body is the package body which can declare and define program objects. For more information on writing package bodies, see the PL/SQL User's Guide and Reference, Release 1 (9.0.1).

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;

3.4.4.2 Drop

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 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.

schema. is the schema containing the package. If you omit schema., Oracle assumes the package is in your own schema.

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;

3.4.4.3 Alter

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

3.5 T/SQL Versus PL/SQL Constructs

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:

3.5.1 CREATE PROCEDURE Statement

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.

3.5.2 Parameter Passing

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.

3.5.3 DECLARE Statement

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.

3.5.4 IF Statement

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.

3.5.5 RETURN Statement

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.

3.5.6 RAISERROR Statement

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.

3.5.7 EXECUTE 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".

3.5.8 WHILE Statement

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.

3.5.9 GOTO Statement

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.

3.5.10 @@Rowcount and @@Error Variables

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.

3.5.11 ASSIGNMENT Statement

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.

3.5.12 SELECT Statement

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.

3.5.13 SELECT Statement with GROUP BY Clause

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.

3.5.14 Column Aliases

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.

3.5.15 UPDATE with FROM Statement

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;

3.5.16 DELETE with FROM Statement

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;

3.5.17 Temporary Tables

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.

3.5.18 Cursor Handling

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.

3.6 T/SQL and PL/SQL Language Elements

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:

3.6.1 Transaction Handling Semantics

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'*/

3.6.1.1 Conversion Preparation Recommendations

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.

3.6.2 Exception-Handling and Error-Handling Semantics

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.

3.6.3 Special Global Variables

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.

3.6.4 Operators

See "Data Manipulation Language" for a discussion of Microsoft SQL Server or Sybase Adaptive Server and Oracle operators.

3.6.5 Built-in Functions

See "Data Manipulation Language" for a discussion of built-in functions in Microsoft SQL Server or Sybase Adaptive Server and Oracle.

3.6.6 DDL Constructs within Microsoft SQL Server or Sybase Adaptive Server Stored Procedures

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.