7/10

Beta Draft

# 3 Triggers and Stored Procedures

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

- Triggers

- Stored Procedures

- Data Types

- Schema Objects

- T/SQL Versus PL/SQL Constructs

- T/SQL and PL/SQL Language Elements

## 3.1 Triggers

Microsoft SQL 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 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 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 INSERT/UPDATE/DELETE operation, and the INSERTED table holds the after image of these rows. If there is an error, the triggers can issue a rollback statement.

Most of the Microsoft SQL Server trigger code is written to enforce referential integrity. Microsoft SQL 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 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.

## 3.2 Stored Procedures

Microsoft SQL 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 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 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:

- Individual SQL Statements

- Logical Transaction Handling

- Error Handling Within the Stored Procedure

This section also considers various components of typical Microsoft SQL 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 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 constructs such as the following are SQL Server-specific, and cannot be converted to Oracle without manual intervention:

    update <table_name>
    set ...
    from <table1>, <table_name>
    where...

The manual intervention required to convert statements such as this can be seen in the following examples:

#### 3.2.1.1 Microsoft SQL Server:

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

#### 3.2.1.2 Oracle:

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

#### 3.2.1.3 Microsoft SQL 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:

    UPDATE titles O
    SET price = (   SELECT (O.price + I.author_royalty)
                              FROM title_author I
                            WHERE I.title_id = O.title_id)
    WHERE EXISTS    (SELECT 1
                              FROM title_author
                              WHERE  title_author.title_id = O.title_id) ;

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, 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, 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, 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 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 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 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 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, so that it can be converted to the Oracle RAISE_APPLICATION_ERROR statement.

#### 3.2.3.2 Customized Error Messages

Microsoft SQL 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 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:

- Local Variable

- Server Data Types

- Composite Data Types

### 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 data types and their equivalent Oracle data types.

### 3.3.3 Composite Data Types

Microsoft SQL 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 and Oracle schema objects:

- Procedure

- Function

- Package

- Package Body

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.

### 3.4.1 Procedure

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

- Create

- Drop

- Execute

- Alter

#### 3.4.1.1 Create

Table 3-2 Comparison of Creating the Procedure Schema Object in Oracle and Microsoft SQL Server

Microsoft SQL Server | Oracle
--- | ---
Syntax:
    CREATE PROCEDURE procedure [@formal_parameter formal_parameter_data type [OUTPUT]                     [= default_value] [,@formal_parameter formal_parameter_data type [OUTPUT][= default_value]] ...

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

OR REPLACE keywords in an Oracle CREATE PROCEDURE statement provide an elegant way of recreating the procedure. In Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL Server | Oracle
--- | ---
Syntax:
    EXEC [@return_value = ] procedure [[@formal_parameter = ] {@actual_parameter | constant_literal} [OUT]] [,[[@formal_parameter = ] {@actual_parameter | constant_literal} [OUT]]] ...
| 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 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 OUT 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

Microsoft SQL Server | Oracle
--- | ---
Syntax: The system procedure SP_RECOMPILE recompiles the named stored procedure. For example:
    ALTER PROCEDURE <procedure name>
    |RECOMPILE
    |ENCRYPT
    |RECOMPILE, ENCRYPT
| 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:

- Create

- Drop

- Execute

- Alter

#### 3.4.2.1 Create

Table 3-6 Comparison of Creating the Function Schema Object in Oracle and Microsoft SQL Server

Microsoft SQL Server | Oracle
--- | ---
Syntax: In Microsoft SQL Server, you can convert a stored procedure to a function in Oracle because the stored procedure in Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL 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 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 7.0

Microsoft SQL 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:

- Create

- Drop

- Alter

#### 3.4.3.1 Create

Table 3-10 Comparison of Creating the Package Schema Object in Oracle and Microsoft SQL Server

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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:

- Create

- Drop

- Alter

#### 3.4.4.1 Create

Table 3-13 Comparison of Creating the Package Body Schema Object in Oracle and Microsoft SQL Server

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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

Microsoft SQL Server | Oracle
--- | ---
Syntax: Microsoft SQL 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 constructs and equivalent Oracle constructs generated by SQL Developer. The conversions of the following constructs are discussed in detail:

- CREATE PROCEDURE Statement

- Parameter Passing

- DECLARE Statement

- IF Statement

- RETURN Statement

- RAISERROR Statement

- EXECUTE Statement

- WHILE Statement

- GOTO Statement

- @@Rowcount and @@Error Variables

- ASSIGNMENT Statement

- SELECT Statement with GROUP BY Clause

- Column Aliases

- UPDATE with FROM Statement

- DELETE with FROM Statement

- Temporary Tables

- Cursor Handling

Listed is the syntax for the Microsoft SQL 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 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-specific constructs are converted into PL/SQL constructs if the equivalent constructs are available in PL/SQL.

- Some Microsoft SQL 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-specific constructs that result in syntax errors, an appropriate error message is displayed including the line number.

### 3.5.1 CREATE PROCEDURE Statement

Table 3-16 Comparison of CREATE PROCEDURE Statement in Oracle and Microsoft SQL Server

Microsoft SQL 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

Microsoft SQL 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 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 data types are converted to Oracle base data types. For example, all Microsoft SQL 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

Microsoft SQL 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 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

Microsoft SQL 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 and Oracle are nearly the same except in the following two cases:

- If EXISTS(...) in Microsoft SQL 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

Microsoft SQL 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 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

Microsoft SQL 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 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

Microsoft SQL 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

Microsoft SQL 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 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

Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL 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 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

Microsoft SQL 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 stored procedures return data to the client program in a different way than Oracle.

Microsoft SQL Server and Oracle can all pass data to the client using output parameters in the stored procedures. Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL 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-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

Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL 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

Microsoft SQL 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 introduced cursors in T/SQL. Syntactical conversion of cursors from Microsoft SQL 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 procedural SQL language and PL/SQL is the Oracle procedural SQL language. This section discusses the following T/SQL and PL/SQL language elements:

- Transaction Handling Semantics

- Exception-Handling and Error-Handling Semantics

- Special Global Variables

- Operators

- Built-in Functions

- DDL Constructs within Microsoft SQL Server Stored Procedures

### 3.6.1 Transaction Handling Semantics

Microsoft SQL Server

Microsoft SQL Server offers two different transaction models: the ANSI-standard implicit transaction model and the explicit transaction model.

Microsoft SQL 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, the default is `isolation level 1.`

To implement `isolation level 3`, Microsoft SQL Server applies HOLDLOCK to all the tables taking part in the transaction. In Microsoft SQL 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 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 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 and Oracle. In Microsoft SQL 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 requires that transactions in stored procedures be allowed to nest, whereas Oracle does not support transaction nesting.

Table 3-34 compares Microsoft SQL Server to Oracle transaction-handling statements:

Table 3-34 Comparison of Transaction-Handling Statements in Oracle and Microsoft SQL Server

Microsoft SQL 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 to Oracle conversion strategy for the transaction-handling statements

Table 3-35 Conversion Strategy for Transaction-Handling Statements

Microsoft SQL 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, 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

In Microsoft SQL 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 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 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 behavior.

If the RAISERROR statement in a Microsoft SQL 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

In Microsoft SQL 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 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 and Oracle operators.

### 3.6.5 Built-in Functions

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

### 3.6.6 DDL Constructs within Microsoft SQL Server Stored Procedures

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