3
Triggers and Stored Procedures

This chapter includes the following sections:

• Introduction
  
  
• Data Types
  
  
• Schema Objects
  
  
• T/SQL Versus PL/SQL Constructs
  
  
• T/SQL and PL/SQL Language Elements

Introduction

Microsoft SQL Server and Sybase Adaptive Server store 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 the PL/SQL User's Guide and Reference, Release 1 (9.0.1).

The following topics are discussed in this section:

• Triggers
  
  
• Stored Procedures

Triggers

Microsoft SQL Server and 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 and Sybase Adaptive Server allow 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 and Sybase Adaptive Server achieve 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 and Sybase Adaptive Server trigger code is written to enforce referential integrity. Microsoft SQL Server and Sybase Adaptive Server triggers are executed once per triggering SQL statement (such as INSERT, UPDATE, or DELETE). If you want some actions to be performed for each row that the SQL statement affects, you must code the actions using the INSERTED and DELETED tables.

Oracle has a rich set of triggers. Oracle also provides triggers that fire for events such as INSERT, UPDATE, and DELETE. You can also specify the number of times that the trigger action is to be executed. For example, once for every row affected by the triggering event (such as might be fired by an UPDATE statement that updates many rows), or once for the triggering statement (regardless of how many rows it affects).

A ROW trigger is fired each time that the table is affected by the triggering event. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. A STATEMENT trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects.

Oracle triggers can be defined as either BEFORE triggers or AFTER triggers. BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger, you can avoid unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised.

As combinations, there are four different types of triggers in Oracle:

• BEFORE STATEMENT trigger
  
  
• BEFORE ROW trigger
  
  
• AFTER STATEMENT trigger
  
  
• AFTER ROW trigger

It is sometimes necessary to create a ROW trigger or a STATEMENT trigger to achieve the same functionality as the Microsoft SQL Server and Sybase Adaptive Server trigger. This occurs in the following cases:

• The triggering code reads from its own table (mutating).
  
  
• The triggering code contains group functions.

In the following example, the group function AVG is used to calculate the average salary:

SELECT AVG(inserted.salary)
FROM inserted a, deleted b
WHERE a.id = b.id;

This would be converted to Oracle by creating an AFTER ROW trigger to insert all the updated values into a package, and an AFTER STATEMENT trigger to read from the package and calculate the average.

For examples of Oracle triggers, see the Oracle9i Application Developer's Guide - Fundamentals, Release 1 (9.0.1).

Stored Procedures

Stored procedures provide a powerful way to code the application logic that can be stored with the server. Microsoft SQL Server and 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 and 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 this section:

• Methods Used to Send Data to Clients
  
  
• Individual SQL Statements
  
  
• Logical Transaction Handling
  
  
• Error Handling within the Stored Procedure

This section also considers various components of typical Microsoft SQL Server and Sybase Adaptive Server stored procedures and suggests ways to design them in order to avoid conversion problems. By applying the standards described below to the coding, you can convert your stored procedures from Microsoft SQL Server and Sybase Adaptive Server to Oracle.

Methods Used to Send Data to Clients

Different relational database management systems (RDBMSs) use different methods to send data to clients. For example, in Microsoft SQL Server and Sybase Adaptive Server the server sends data to the client in the form of a byte-stream. The client is responsible for retrieving all the data from the communication channel before sending another request to the server. In Oracle, the client can issue one or more SQL statements on the same network connection, and the system global area (SGA) stores all the data retrieved from the database. The server sends the data to the client as requested and the client sends a FETCH request on the connection whenever it is ready for the next set of results. This section discusses the different methods used to send data to clients under the following headings:

• Output Variables
  
  
• Results Sets: Microsoft SQL Server and Sybase Adaptive Server Method of Sending Data to the Client
  
  
• Oracle: Cursor Variables for Returning Query Results
  
  
• Microsoft SQL Server and Sybase Adaptive Server: Multiple Results Sets
  
  
• Microsoft SQL Server and Sybase Adaptive Server: Cursors

Output Variables

Microsoft SQL Server and Sybase Adaptive Server and Oracle can all send data to clients by means of output variables.

Results Sets: Microsoft SQL Server and Sybase Adaptive Server Method of Sending Data to the Client

Many Microsoft SQL Server and Sybase Adaptive Server applications rely on the SQL Server-specific stream-based data return method called "result sets". Oracle is optimized to return data more efficiently when the data is requested using an ANSI-standard SQL SELECT statement, as compared to any proprietary stored procedure method. Therefore, the best design decision is to use stored procedures for data processing and SELECT statements for queries.

In Oracle, the use of cursor variables allows client programs to retrieve well-structured result sets.

To send even a single row back to the client from the stored procedure, Microsoft SQL Server and Sybase Adaptive Server can use result sets instead of an ANSI-standard method.

For example:

CREATE PROCEDURE get_emp_rec @empid INT
AS
        SELECT  fname, lname, loginid, addr, title, dept, mgrid
        FROM employee           
        WHERE empid = @empid

The above procedure can be converted to an Oracle PL/SQL procedure as follows:

CREATE OR REPLACE PROCEDURE get_emp_rec 
(empid  IN      NUMBER,
 fname  OUT     VARCHAR2,
 lname  OUT     VARCHAR2,
 loginid        OUT     VARCHAR2,
 addr   OUT     VARCHAR2,
 title  OUT     VARCHAR2,
 dept   OUT     NUMBER,
 mgrid  OUT     NUMBER)
AS
BEGIN
        SELECT  fname, lname, loginid, addr, title, dept, mgrid
        INTO    fname, lname, loginid, addr, title, dept, mgrid
        FROM    employee                
        WHERE   empid = empid;
END;

Output variables are a structured way of sending data from server to client. Output variables allow the caller to see the results in a predictable manner, as the structure of the output variable is predefined. This method also allows encapsulation of behavior of the stored procedures.

Output variables offer the following benefits:

• Facilitate better structuring of code
  
  
• Allow the caller to see the results in a structured and predictable way, as the structure of the output variables is well defined
  
  
• Allow encapsulation of behavior of the called routine

If a third-party user interface product uses the result set capability of Microsoft SQL Server and Sybase Adaptive Server, make sure that the same functionality can be made available to the Oracle database. For example, PowerBuilder can use result sets to populate the data windows.

Although many client programs, such as Oracle Call Interface (OCI), precompilers, SQL*Module, and SQL*Plus, recognize cursor variables, most Open Database Connectivity (ODBC) drivers cannot recognize cursor variables. One solution when using ODBC drivers is to identify the code that produces the result set, and move this code online in the client program. The Oracle9i and Oracle8i ODBC Driver release 8.1.5.4.0 and later releases support result sets.

In the following example, an Microsoft SQL Server and Sybase Adaptive Server stored procedure returns a result set with multiple rows:

CREATE PROCEDURE make_loginid
BEGIN
        update employee
        set loginid = substring(fname,1,1) + convert(varchar(7),empid)
           select fname, lname, loginid from employee
END

This procedure sends all the qualifying rows to the client as a continuous data stream. To further process the rows, the client program must retrieve the rows one after another from the communication channel.

The following piece of the DB-Library/C code executes the above procedure and prints each row to the screen.

main()
{
        
/*      Data structure dbproc is conceptually very similar 
                to CDA data structure used in Oracle's OCI/C programs */
        dbcmd(dbproc, "exec make_loginid");
        /*      The above command sets the command buffer with the              
                transact-sql command that needs to be executed. */

        dbsqlexec(dbproc);
        /*      This command causes the parsing and execution of the    
                SQL command on the server side. */

        dbresults(dbproc);
        /*      This command puts the result rows onto the              
                communications channel. */

        /*The following while loop retrieves the result rows one after the other
           by calling the function dbnextrow repeatedly. This
           implementation is cursor implementation through DB-Library functions. 
*/
        while (dbnextrow(dbproc) != NO_MORE_ROWS)
        {
                dbprrow(dbproc);
             /* This function prints the retrieved row to the standard output. 
*/
        }

You can migrate Microsoft SQL Server and Sybase Adaptive Server stored procedures to the Oracle PL/SQL stored procedures or packages in different ways, as follows:

Examples of these different Oracle solutions to the result set problem are presented below:

1. If the SELECT statement is made part of the client code, the PL/SQL code for the make_loginid procedure is as follows:
   
   

   CREATE OR REPLACE PROCEDURE make_loginid
   AS
   BEGIN
           update employee
           set loginid = substr(lname,1,1)
                   ||
                   substr(to_char(empid),1,7);
   END;
   

   The following SELECT statement becomes part of the client code:

           select fname, lname, loginid from employee
   
   

   The following PL/SQL code shows how to migrate the make_loginid procedure to Oracle by using PL/SQL tables as output parameters:

   CREATE OR REPLACE PACKAGE make_loginid_pkg
   IS
   BEGIN
           DECLARE EmpFnameTabType IS TABLE OF
                   employee.fname %TYPE
                   INDEX BY BINARY_INTEGER; 
           DECLARE EmpLnameTabType IS TABLE OF
                   employee.lname %TYPE
                   INDEX BY BINARY_INTEGER; 
           DECLARE EmpLoginidTabType IS TABLE OF
                   employee.loginid %TYPE
                   INDEX BY BINARY_INTEGER; 
           emp_fname_tab   EmpFnameTabType;
           emp_lname_tab   EmpLnameTabType;
           emp_loginid_tab EmpLoginidTabType;
           PROCEDURE make_loginid
                   (emp_fname_tab  OUT     EmpFnameTabType,
                    emp_lname_tab  OUT     EmpLnameTabType,
                    emp_loginid_tab        OUT     EmpLoginidTabType);
   END make_loginid_pkg;
   

   The package body definition is as follows:

   CREATE OR REPLACE PACKAGE BODY make_loginid_pkg
   IS
   BEGIN
           PROCEDURE make_loginid
                   (emp_fname_tab  OUT     EmpFnameTabType,
   
                    emp_lname_tab  OUT     EmpLnameTabType,
                    emp_loginid_tab        OUT     EmpLoginidTabType)
           AS
           DECLARE i BINARY_INTEGER := 0;
           BEGIN
                   update employee
                   set loginid = substr(fname,1,1)
                           ||
                           substr(to_char(empid),1,7);
                   FOR emprec IN (select fname,lname,loginid
                            from employee) LOOP
                           i := i + 1;
                           emp_fname_tab[i] = emprec.fname;
                           emp_lname_tab[i] = emprec.lname;
                           emp_loginid_tab[i] = emprec.loginid;
                   END LOOP; 
           END make_loginid; 
   END make_loginid_pkg;
   

   This procedure updates the PL/SQL tables with the data. This data is then available to the client after the execution of this packaged procedure.

2. The following packaged procedure sends the rows one after the other to the client upon each call to the packaged procedure. The make_loginid_pkg.update_loginid procedure must be executed once and the make_loginid_pkg.fetch_emprec procedure must be executed in a loop to fetch the rows one after another from the client program.

   The package definition is as follows:

   CREATE OR REPLACE PACKAGE make_loginid_pkg
   IS
   BEGIN
   PROCEDURE update_loginid;
   PROCEDURE fetch_emprec
                   done_flag       IN OUT  INTEGER,
                   nrows   IN OUT  INTEGER,
                   fname   OUT             VARCHAR2,
                   lname   OUT             VARCHAR2,
                   loginid OUT             VARCHAR2);
   END make_loginid_pkg;
   
   

   The package body definition is as follows:

   CREATE OR REPLACE PACKAGE BODY make_loginid_pkg
   IS
   BEGIN
   CURSOR emprec IS
           select fname, lname, loginid
           from employee;
   PROCEDURE update_loginid
   IS
   BEGIN
           update employee
           set loginid =   substr(fname,1,1) || 
                           substr(to_char(loginid),1,7);
   END update_loginid; 
   
   PROCEDURE fetch_emprec
                   done_flag       IN OUT  INTEGER,
                   nrows   IN OUT  INTEGER,
                   fname   OUT             VARCHAR2,
                   lname   OUT             VARCHAR2,
                   loginid OUT             VARCHAR2)
   IS
   BEGIN
           IF NOT emprec%ISOPEN THEN
                   OPEN emprec;
                   nrows := 0;
           END IF;
           done_flag := 0;
           FETCH emprec INTO fname, lname, loginid;
                   IF emprec%NOTFOUND THEN
                   CLOSE emprec;
                           done_flag := 1;
           ELSE
                   nrows := nrows + 1;
           ENDIF;
   END fetch_emprec;
    
   END make_loginid_pkg;
   

Oracle: Cursor Variables for Returning Query Results

Oracle allows you to define a cursor variable to return query results. This cursor variable is similar to the user-defined record type and array type. The cursor stored in the cursor variable is like any other cursor. It is a reference to a work area associated with a multi-row query. It denotes both the set of rows and a current row in that set. The cursor referred to in the cursor variable can be opened, fetched from, and closed just like any other cursor.

There is a difference; since it is a PL/SQL variable, it can be passed into and out of procedures like any other PL/SQL variable. As a result, procedures that use cursor variables are reusable. You can see what the output of the procedure is by looking at the procedure definition. You can use the same procedure to return the results of a SELECT statement to a calling client program. Cursor variables can even be the return value of a function. The cursor variables preserve well-structured programming concepts while allowing the client routine to retrieve result sets.

Typically, the cursor would be declared in a client program (for example, OCI, precompilers, SQL*Module, or SQL*Plus) and then passed as an IN OUT parameter to the PL/SQL procedure. The procedure then opens the cursor based on a SELECT statement. The calling program performs the FETCHs from the cursor, including the possibility of using ARRAY FETCH to retrieve multiple rows in one network message, and closes the cursor when it is done.

Pro*C Client:

...
struct emp_record {
        char ename[11];
        float sal;
}emp_record;
SQL_CURSOR c;

EXEC SQL EXECUTE
        BEGIN
                emp_package.open_emp(:c,1);
        END;
END-EXEC;
...
/* fetch loop until done */
EXEC SQL FETCH :c INTO :emp_record;
...
CLOSE :c;
...

Oracle Server:

CREATE OR REPLACE PACKAGE emp_package IS
        TYPE emp_part_rec IS RECORD
        (ename emp.ename%type, sal emp.sal%type);
        TYPE emp_cursor IS REF CURSOR
                RETURN emp_part_rec;
        PROCEDURE open_emp (c_emp IN OUT emp_cursor,
                        select_type IN NUMBER);
END emp_package;

CREATE OR REPLACE PACKAGE BODY emp_package IS
PROCEDURE open_emp (c_emp IN OUT emp_cursor,
                        select_type IN NUMBER) IS
        BEGIN
                IF select_type=1 THEN
                        OPEN c_emp FOR SELECT ename, sal FROM EMP
                                WHERE COMM IS NOT NULL;
                ELSE
                        OPEN c_emp FOR SELECT ename, sal FROM EMP;
                END IF;
        END open_emp;
END emp_package;

Microsoft SQL Server and Sybase Adaptive Server: Multiple Results Sets

Microsoft SQL Server and Sybase Adaptive Server stored procedures can return multiple different result sets to the calling routine.

For example, consider the following procedure:

CREATE PROCEDURE example_proc
AS
BEGIN

SELECT empno, empname, empaddr FROM emp
WHERE empno BETWEEN 1000 and 2000

SELECT empno, deptno, deptname FROM emp, dept
WHERE emp.empno = dept.empno
AND emp.empno BETWEEN 1000 and 2000

END

This procedure returns two different result sets. The client is responsible for processing the results. To convert Microsoft SQL Server and Sybase Adaptive Server multiple result sets to Oracle, pass one more cursor variable to the stored procedure to open a second cursor; the client program then looks at both cursor variables for data. However, it can be difficult to track all the result sets in a single procedure. It is recommended that you just use one result set, that is, one cursor variable per procedure, if possible.

Microsoft SQL Server and Sybase Adaptive Server: Cursors

Cursors allow row-by-row operations on a given result set. Microsoft SQL Server and Sybase Adaptive Server provide ANSI-standard SQL syntax to handle cursors. The additional DECLARE CURSOR, OPEN, FETCH, CLOSE, and DEALLOCATE CURSOR clauses are included in T/SQL. Using these statements you can achieve cursor manipulation in a stored procedure. After FETCHing the individual row of a result set, this current row can be modified with extensions provided with UPDATE and DELETE statements.

The UPDATE statement syntax is as follows:

update <table_name>
set <column_name> = <expression>
from <table1>, <table_name>
where current of <cursor name>
The DELETE statement syntax is as follows: 
delete from <table_name>
where current of <cursor name>
Microsoft SQL Server and Sybase Adaptive Server cursors map one-to-one with 
Oracle cursors. 

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 and Sybase Adaptive 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:

Microsoft SQL Server and Sybase Adaptive Server:

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

Oracle:

DELETE
FROM sales
WHERE title_id IN
 (SELECT title_id
  FROM titles
  WHERE type = 'business'
  )

Microsoft SQL Server and Sybase Adaptive Server:

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

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.

Logical Transaction Handling

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

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

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 and 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 and 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 and 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 an Microsoft SQL Server and 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.

RAISERROR Statement

The Microsoft SQL Server and 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 and Sybase Adaptive Server, so that it can be converted to the Oracle RAISE_APPLICATION_ERROR statement.

Customized Error Messages

Microsoft SQL Server and Sybase Adaptive Server allow 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 and 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.

Data Types

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

• Local Variable
  
  
• Server Data Types
  
  
• Composite Data Types

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

Server Data Types

See the Data Types section in Chapter 2 for a list of Microsoft SQL Server and Sybase Adaptive Server data types and their equivalent Oracle data types.

Composite Data Types

Microsoft SQL Server and Sybase Adaptive Server do not have composite data types

Table 3-1 Composite Data Types in Oracle

Schema Objects

This section compares the following Microsoft SQL Server and Sybase Adaptive 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 four sections

• Syntax
  
  
• Description
  
  
• Permissions
  
  
• Examples

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

Procedure

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

• Create
  
  
• Drop
  
  
• Execute
  
  
• Alter

Create

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

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]

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;

CREATE PROCEDURE myproc @cust 
char(30)= space(30), @cust_id int 
OUTPUT, @param3 datetime OUTPUTAS 
BEGIN DECLARE @local_var1 int, 
@local_var2 datetime SELECT @local_
var2 = getdate() SELECT @param3 = 
@local_var2 SELECT @local_var1 = 
customer_id FROM customer WHERE 
customer = @cust SELECT @cust_id = 
@local_var1 END

CREATE OR REPLACE PROCEDURE sam.credit ( 
acc_no IN NUMBER DEFAULT 0, acc IN 
VARCHAR2, amount IN NUMBER, return_status 
OUT NUMBER ) AS BEGIN UPDATE accounts SET 
balance = balance + amount WHERE account_
id = acc_no; EXCEPTION WHEN SQL%NOTFOUND 
THEN RAISE_APPLICATION_ERROR (-20101, 
`Error updating accounts table'); END 

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 and 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 and Sybase Adaptive Server, the procedure must be dropped explicitly before replacing it.

Drop

Table 3-3 Comparison of Dropping the Procedure Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

DROP PROCEDURE procedure 

DROP PROCEDURE [schema.]procedure 

DROP PROCEDURE myproc

DROP PROCEDURE sam.credit;

Recommendations:

The above statement does not have any effect on the conversion process. This information is provided for reference only.

Execute

Table 3-4 Comparison of Executing the Procedure Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

EXEC [@return_value = ] procedure 
[[@formal_parameter = ] {@actual_
parameter | constant_literal} [OUT]] 
[,[[@formal_parameter = ] {@actual_
parameter | constant_literal} [OUT]]] 
...

procedure
 [([{actual_parameter | 
      constant_literal |
   formal_parameter =>
     {actual_parameter | 
      constant_literal}
   }]
 [,{actual_parameter | 
      constant_literal |
      formal_parameter =>
     {actual_parameter | 
      constant_literal}
   }] ....
)]

  @formal_parameter = @actual_
parameter

  '@actual_parameter ' as: 
  @formal_parameter = 10

  formal_parameter => actual_parameter

  'actual_parameter' as: 
   formal_parameter => 10

  EXEC GetEmplName @EmpID
  EXEC @status = GetAllDeptCodes 
  EXEC @status =  UpdateEmpSalary 
@EmpID, 
   @EmpName
  EXEC UpdateEmpSalary 13000,'Joe 
Richards'

  EXEC UpdateEmpSalary 
@Employee = @EmpName, 
   @Employee_Id = @EmpID

 EXEC UpdateEmpSalary 
@EmpName, @Employee_Id = @EmpID 
 EXEC UpdateEmpSalary 
@Employee = @EmpName, @EmpID

 credit (accno, accname, amt, retstat); 

  credit (acc_no => accno, acc => 
accname, amount => amt, 
      return_status => retstat)

  credit (accno, accname, amount => amt, 
return_status => retstat)

Alter

Table 3-5 Comparison of Altering the Procedure Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

ALTER PROCEDURE <procedure name>
|RECOMPILE
|ENCRYPT
|RECOMPILE, ENCRYPT

ALTER PROCEDURE [schema.]procedure COMPILE

sp_recompile my_proc

ALTER PROCEDURE sam.credit COMPILE;

Function

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

• Create
  
  
• Drop
  
  
• Execute
  
  
• Alter

Create

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

CREATE [OR REPLACE] FUNCTION 
[user.]function [(parameter [OUT] data 
type[,(parameter [IN OUT] data type]...)] 
RETURN data type { IS | AS } block 

  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;

Drop

Table 3-7 Comparison of Dropping the Function Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

DROP FUNCTION [schema.]function 

DROP FUNCTION sam.credit;

Execute

Table 3-8 Comparison of Executing the Function Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

function [({actual_parameter | constant_
literal}...)]

 1) IF sal_ok (new_sal, new_title) THEN
   ....
   END IF;

2) promotable:=
   sal_ok(new_sal, new_title) AND
   (rating>3);

Alter

Table 3-9 Comparison of Altering the Function Schema Object in Oracle and Microsoft SQL Server 7.0

ALTER FUNCTION [schema.]function COMPILE

 ALTER FUNCTION sam.credit COMPILE

Package

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

• Create
  
  
• Drop
  
  
• Alter

Create

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

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] 

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;

Drop

Table 3-11 Comparison of Dropping the Package Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

DROP PACKAGE [BODY] [schema.]package 

DROP PACKAGE emp_actions;

Alter

Table 3-12 Comparison of Altering the Package Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

ALTER PACKAGE [user.]package COMPILE 
[PACKAGE | BODY]

 ALTER PACKAGE emp_actions COMPILE PACKAGE

Package Body

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

• Create
  
  
• Drop
  
  
• Alter

Create

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

CREATE [OR REPLACE] PACKAGE BODY 
[schema.]package                               
{IS | AS} pl/sql_package_body

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;

Drop

Table 3-14 Comparison of Dropping the Package Body Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

DROP PACKAGE [BODY] [schema.]package 

DROP PACKAGE BODY emp_actions;

Alter

Table 3-15 Comparison of Altering the Package Body Schema Object in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

ALTER PACKAGE [user.]package COMPILE 
[PACKAGE | BODY]

ALTER PACKAGE emp_actions COMPILE 
BODY

T/SQL Versus PL/SQL Constructs

This section provides information about the Microsoft SQL Server and Sybase Adaptive Server constructs and equivalent Oracle constructs generated by the Migration Workbench. 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
  
  
• SELECT Statement as Part of the SELECT List
  
  
• SELECT Statement with GROUP BY Clause
  
  
• Column Aliases
  
  
• UPDATE with FROM Statement
  
  
• DELETE with FROM Statement
  
  
• Temporary Tables
  
  
• Result Set (Converted Using a Cursor Variable)
  
  
• Cursor Handling
  
  
• Transaction Handling Statements

Listed is the syntax for the Microsoft SQL Server and 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 the Migration Workbench. These PL/SQL procedures have more lines of code compared to the source Microsoft SQL Server and Sybase Adaptive Server procedures because these PL/SQL procedures are converted to emulate Microsoft SQL Server and Sybase Adaptive Server functionality. The PL/SQL procedures written from scratch for the same functionality in Oracle would be much more compact. The PL/SQL procedures generated by the Migration Workbench indicate the manual conversion required by adding appropriate commands. In general, the Migration Workbench deals with the Microsoft SQL Server and Sybase Adaptive Server T/SQL constructs in one of the following ways:

CREATE PROCEDURE Statement

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

CREATE PROC proc1
AS 
RETURN 0

CREATE OR REPLACE FUNCTION PROC1 
RETURN INTEGER 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        RETURN 0; 
END PROC1; 
/  

Comments

An Microsoft SQL Server and Sybase Adaptive Server stored procedure can be converted to a stored procedure, a function, or a package in Oracle. The output depends upon the option used when running the Migration Workbench.

The Migration Workbench automatically adds what is necessary to simulate Microsoft SQL Server and Sybase Adaptive Server functionality. In the example in Table 3-16 above, the Migration Workbench added the following three variables:

StoO_selcnt     INTEGER;
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER;

These variables are needed in the EXCEPTION clause in the PL/SQL procedures that must be added for each SQL statement to emulate Microsoft SQL Server and Sybase Adaptive Server functionality. See the SELECT Statement topic in this section for clarification of the purpose of these variables.

Parameter Passing

Table 3-17 Comparison of Parameter Passing in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
@x int=-1,
@y money,
@z bit OUT,
@a char(20) = 'TEST'
AS 
    RETURN 0

CREATE OR REPLACE FUNCTION PROC1( 
x       INTEGER  DEFAULT -1, 
y       NUMBER , 
z       IN OUT NUMBER, 
a       CHAR  DEFAULT 'TEST') 
RETURN INTEGER 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        RETURN 0; 
END PROC1; 
/ 

Comments

Parameter passing is almost the same in Microsoft SQL Server and 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 and Sybase Adaptive Server data types are converted to Oracle base data types. For example, all Microsoft SQL Server and Sybase Adaptive Server numeric data types are converted to NUMBER and all alphanumeric data types are converted to VARCHAR2 and CHAR in Oracle.

DECLARE Statement

Table 3-18 Comparison of DECLARE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
DECLARE
        @x int,
        @y money,
        @z bit,
        @a char(20)
                RETURN 0
GO

CREATE OR REPLACE FUNCTION PROC1 
RETURN INTEGER 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
y       NUMBER; 
z       NUMBER; 
a       CHAR(20); 
BEGIN 
        RETURN 0; 
END PROC1; 

Comments

Microsoft SQL Server and Sybase Adaptive Server and Oracle follow similar rules for declaring local variables.

The Migration Workbench 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.

IF Statement

Table 3-19 Comparison of IF Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

CREATE PROC proc1 @Flag int = 
0
AS 
BEGIN
DECLARE @x int
IF ( @Flag=0 )
 SELECT @x = -1
ELSE
 SELECT @x = 10
END

CREATE OR REPLACE PROCEDURE PROC1( 
Flag    INTEGER  DEFAULT 0) 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
BEGIN 
        IF (PROC1.Flag = 0) THEN 
                PROC1.x :=  -1; 
        ELSE 
                PROC1.x :=  10; 
        END IF; 
END; 
/