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; 
/

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

CREATE OR REPLACE PROCEDURE PROC1( 
Flag    CHAR  DEFAULT ' ') 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 


StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
BEGIN 
        IF (PROC1.Flag = ' ') THEN 
                PROC1.x :=  -1; 
        ELSE 
           IF (PROC1.Flag = 'a') THEN 
                 PROC1.x :=  10; 
        ELSE 
           IF (PROC1.Flag = 'b') THEN 
                PROC1.x :=  20; 
                        END IF; 
                END IF; 
        END IF; 
END; 
/

CREATE PROC proc1
AS 
BEGIN
DECLARE @x int
IF EXISTS ( SELECT * FROM 
table2 )
 SELECT @x = -1
END

CREATE OR REPLACE PROCEDURE PROC1 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
BEGIN 
         
        BEGIN 
        StoO_selcnt := 0; 
        StoO_error  := 0; 
        StoO_rowcnt := 0; 
        SELECT 1 INTO StoO_selcnt 
        FROM DUAL 
        WHERE EXISTS ( 
                SELECT  * 
                 FROM TABLE2); 
     StoO_rowcnt := SQL%ROWCOUNT; 
     EXCEPTION 
           WHEN OTHERS THEN 
              StoO_selcnt := 0; 
              StoO_error := SQLCODE; 
              StoO_errmsg := SQLERRM; 
        END; 
        IF StoO_selcnt != 0 THEN 
                PROC1.x :=  -1; 
        END IF; 
END; 
/

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

CREATE OR REPLACE PROCEDURE PROC1( 
basesal         NUMBER , 
empid   INTEGER ) 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        BEGIN 
        StoO_selcnt := 0; 
        StoO_error  := 0; 
        StoO_rowcnt := 0; 
        SELECT 1 INTO StoO_selcnt 
        FROM DUAL 
        WHERE ( 
                SELECT  SAL 
                 FROM EMP  
                WHERE EMPID = 
PROC1.empid)<PROC1.basesal; 
        StoO_rowcnt := SQL%ROWCOUNT; 
        EXCEPTION 
             WHEN OTHERS THEN 
                StoO_selcnt := 0; 
               StoO_error := SQLCODE; 
              StoO_errmsg := SQLERRM; 
        END; 
        IF StoO_selcnt != 0 THEN 
                BEGIN 
                StoO_error   := 0; 
                StoO_rowcnt  := 0; 
                UPDATE EMP 
                SET SAL_FLAG = -1 
                
           WHERE EMPID = PROC1.empid; 
         StoO_rowcnt := SQL%ROWCOUNT; 
            EXCEPTION 
                    WHEN OTHERS THEN 
            StoO_error  := SQLCODE; 
           StoO_errmsg := SQLERRM; 
                END; 
        END IF; 
END; 
/

Comments

IF statements in Microsoft SQL Server and Sybase Adaptive Server and Oracle are nearly the same except in the following two cases:

If EXISTS(...) in Microsoft SQL Server and Sybase Adaptive Server does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO WHERE EXISTS clause and an IF statement as shown in Example 3 above.

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

RETURN Statement

Table 3-20 Comparison of RETURN Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
@x int
AS 
IF @x = -1
        RETURN 25022
ELSE
        RETURN 25011

CREATE OR REPLACE FUNCTION PROC1( 
x       INTEGER ) 
RETURN INTEGER 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        IF PROC1.x = -1 THEN 
                RETURN 25022; 
        ELSE 
                RETURN 25011; 
        END IF; 
END PROC1; 
/ 

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 and Sybase Adaptive Server can return only the numeric data type, while Oracle can return any of the server data types or the PL/SQL data types.

In a PL/SQL procedure, a RETURN statement can only return the control back to the calling program without returning any data. For this reason, the value is commented out if the Microsoft SQL Server and Sybase Adaptive Server procedure is converted to a PL/SQL procedure, but not commented out if converted to a PL/SQL function. The Migration Workbench does this automatically.

RAISERROR Statement

Table 3-21 Comparison of RAISERROR Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

 CREATE PROC proc1
 AS 
      RAISERROR 12345 "No Employees 
found"

CREATE OR REPLACE PROCEDURE PROC1 
  AS 
     StoO_selcnt     INTEGER; 
     StoO_error      INTEGER; 
     StoO_rowcnt     INTEGER; 
     StoO_errmsg     VARCHAR2(255); 
     StoO_sqlstatus  INTEGER; 
                                       
BEGIN 
                                               
raise_application_error(-20999, 12345 || 
'-' || "No Employees Found"); 
        END PROC1; 
        /

Comments

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

The Migration Workbench copies the error code and error message from a RAISERROR statement and places them in the RAISE_APPLICATION_ERROR statement appended to the error message.

EXECUTE Statement

Table 3-22 Comparison of EXECUTE Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

 CREATE PROC proc1
 AS 
EXEC SetExistFlag
EXEC SetExistFlag yes=@yes, @Status 
OUT
EXEC @Status = RecordExists
EXEC SetExistFlag @yes

CREATE OR REPLACE PROCEDURE PROC1 
   AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
  BEGIN 
         BEGIN 
         SETEXISTFLAG; 
             EXCEPTION 
               WHEN OTHERS THEN 
            StoO_error := SQLCODE; 
            StoO_errmsg := SQLERRM; 
END; 
     BEGIN 
       SETEXISTFLAG(=>PROC1.yes, 
          PROC1.Status); 
              EXCEPTION 
                    WHEN OTHERS THEN 
                     StoO_error := 
SQLCODE; 
                     StoO_errmsg := 
SQLERRM; 
END; 
       BEGIN 
           PROC1.Status:=RECORDEXISTS; 
           EXCEPTION 
           WHEN OTHERS THEN 
                   StoO_error := SQLCODE; 
                   StoO_errmsg := SQLERRM; 
END; 
        BEGIN 
             SETEXISTFLAG(PROC1.yes); 
             EXCEPTION 
             WHEN OTHERS THEN 
               StoO_error := SQLCODE; 
               StoO_errmsg := SQLERRM; 
END; 
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. If a procedure is converted to a PL/SQL function, make sure to assign the RETURN value to a variable when calling it (see the call to RecordExists in Table 3-22 above).

The Migration Workbench converts the parameter-calling convention to be either positional, named, or mixed. For information on parameter-calling conventions, see the Schema Objects section in this chapter.

WHILE Statement

Table 3-23 Comparison of WHILE Statement in Oracle and
Microsoft SQL Server and Sybase Adaptive Server  

CREATE PROC proc1
 @i int
 AS 
         WHILE @i > 0

         BEGIN
          print 'Looping 
inside WHILE....'
          SELECT @i = @i 
+ 1
         END

 CREATE OR REPLACE PROCEDURE PROC1(                                      
in_i    IN INTEGER ) 
      AS 
                                       StoO_selcnt     
INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER;                                        
i       INTEGER; 
BEGIN 
    PROC1.i := PROC1.in_i; 
           <<i_loop1>> 
     WHILE PROC1.i > 0 LOOP 
BEGIN 
DBMS_OUTPUT.PUT_LINE('Looping inside while.....') ; 
  PROC1.i :=  PROC1.i + 1; 
                    END; 
END LOOP; 
END PROC1; 
   /

CREATE PROC proc1 
 @i int,
 @y int
 AS
         WHILE @i > 0
         BEGIN
          print 
'Looping inside 
WHILE....'
          SELECT @i = 
@i + 1
         END

CREATE OR REPLACE PROCEDURE PROC1( 
 in_i    IN INTEGER , 
    y       INTEGER ) 
 AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
        i       INTEGER; 
BEGIN 
PROC1.i := PROC1.in_i; 
    <<i_loop1>> 
  WHILE PROC1.i > 0 LOOP 
BEGIN                                                    
DBMS_OUTPUT.PUT_LINE('Looping inside 
while.....') ; 
PROC1.i :=  PROC1.i + 1; 
END; 
END LOOP; 
END PROC1; 
/

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

 CREATE OR REPLACE PROCEDURE PROC1 
 AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
sal     NUMBER; 
 BEGIN 
      PROC1.sal :=  0; 
         <<i_loop1>> 
         WHILE 1 = 1 LOOP 
  BEGIN 
BEGIN 
StoO_selcnt := 0; 
StoO_error  := 0; 
SELECT 1 INTO StoO_selcnt FROM DUAL 
WHERE (EXISTS ( 
SELECT  * 
FROM EMP 
WHERE SAL < PROC1.sal)); 
EXCEPTION 

 WHEN OTHERS THEN 
StoO_selcnt := 0; 
StoO_error := SQLCODE; 
StoO_errmsg := SQLERRM; 
   END; 
         IF StoO_selcnt != 1 THEN 
            EXIT; 
            END IF; 
       PROC1.sal :=  PROC1.sal + 99; 
            BEGIN 
 StoO_error   := 0; 
 StoO_rowcnt  := 0; 
 DELETE  EMP 
      WHERE SAL < PROC1.sal; 
  StoO_rowcnt := SQL%ROWCOUNT; 
  EXCEPTION 
      WHEN OTHERS THEN 
       StoO_error  := SQLCODE; 
       StoO_errmsg := SQLERRM; 
          END; 
            END; 
             END LOOP; 

               END PROC1; 
/

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

CREATE OR REPLACE PROCEDURE PROC1 
  AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
        sal     NUMBER; 
BEGIN 
      <<i_loop1>> 
WHILE 1 = 1 LOOP 
BEGIN 
                                                       BEGIN 
StoO_selcnt := 0; 
StoO_error  := 0; 
SELECT 1 INTO StoO_selcnt FROM DUAL 
WHERE (( 
SELECT  COUNT(*) 
FROM EMP)>0); 
EXCEPTION 
WHEN OTHERS THEN 
StoO_selcnt := 0; 
StoO_error := SQLCODE; 
StoO_errmsg := SQLERRM; 
END; 
IF StoO_selcnt != 1 THEN 
EXIT; 
END IF; 
BEGIN 
StoO_rowcnt := 0; 
StoO_selcnt := 0; 
StoO_error  := 0; 
 SELECT   MAX(SAL) 
INTO PROC1.sal FROM EMP 
WHERE STAT = 1; 
   StoO_rowcnt := SQL%ROWCOUNT; 
EXCEPTION 
  WHEN TOO_MANY_ROWS THEN 
   StoO_rowcnt := 2; 
 WHEN OTHERS THEN 
  StoO_rowcnt := 0; 
  StoO_selcnt := 0; 
  StoO_error := SQLCODE; 
  StoO_errmsg := SQLERRM; 
   END; 
  BEGIN 
 StoO_error   := 0; 
 StoO_rowcnt  := 0; 
DELETE  EMP 
    WHERE SAL < PROC1.sal; 

   StoO_rowcnt := SQL%ROWCOUNT; 
EXCEPTION 
 WHEN OTHERS THEN 
 StoO_error  := SQLCODE; 
StoO_errmsg := SQLERRM; 
END; 
END; 
END LOOP; 
END PROC1; 

Comments

The Migration Workbench can convert most WHILE constructs. However, the CONTINUE within a WHILE loop in Microsoft SQL Server and Sybase Adaptive Server does not have a direct equivalent in PL/SQL. It is simulated using the GOTO statement with a label. Because the Migration Workbench is a single-pass parser, it adds a label statement at the very beginning of every WHILE loop (see Example 2 in Table 3-23 above).

GOTO Statement

Table 3-24 Comparison of GOTO Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

 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( 
 Status  INTEGER ) 
 AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
        j       INTEGER; 
    BEGIN 
IF PROC1.Status = -1 THEN 
GOTO ERROR; 
END IF; 
PROC1.j :=  -1; 
  <<ERROR>> 
  PROC1.j :=  99; 
      END PROC1; 
/ 

Comments

The GOTO <label> statement is converted automatically. No manual changes are required.

@@Rowcount and @@Error Variables

Table 3-25 Comparison of @@Rowcount and @@Error Variables in Oracle and Microsoft SQL Server and Sybase Adaptive Server

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
StoO_selcnt     INTEGER;
StoO_error      INTEGER;
StoO_rowcnt     INTEGER;
 i_x INTEGER;
BEGIN
BEGIN
 SELECT count(*) 
 INTO i_x
 FROM emp;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
END;
IF StoO_rowcnt = 0 THEN
DBMS_OUTPUT.PUT_LINE
('No rows found.') ;
END IF;
IF StoO_error = 0 THEN
DBMS_OUTPUT.PUT_LINE('No errors.') ;
END IF;
END;
/

Comments

@@rowcount is converted to StoO_rowcnt, which takes its value from the PL/SQL cursor attribute SQL%ROWCOUNT.

@@error is converted to StoO_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. This requires that the Migration Workbench add a local exception block around every SQL statement and a few PL/SQL statements. Other global variables are converted with a warning message. These may need to be converted manually.

ASSIGNMENT Statement

Table 3-26 Comparison of ASSIGNMENT Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
DECLARE @x int
   SELECT @x = -1
   SELECT @x=sum(salary) FROM 
employee

CREATE OR REPLACE PROCEDURE PROC1  
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
BEGIN 
        PROC1.x :=  -1; 
        BEGIN 
                StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
                StoO_error  := 0; 
 
                SELECT   SUM(SALARY) 
                INTO PROC1.x FROM 
EMPLOYEE; 
                StoO_rowcnt := 
SQL%ROWCOUNT; 
 
          EXCEPTION 
          WHEN TOO_MANY_ROWS THEN 
           StoO_rowcnt := 2; 
           WHEN OTHERS THEN 
           StoO_rowcnt := 0; 
           StoO_selcnt := 0; 
           StoO_error := SQLCODE; 
           StoO_errmsg := SQLERRM; 
        END; 
END PROC1; 
/

Comments

Assignment in Microsoft SQL Server and Sybase Adaptive Server is done using the SELECT statement as illustrated in Table 3-26.

PL/SQL assigns values to a variable as follows:

It uses the assignment statement to assign the value of a variable or an expression to a local variable. It assigns a value from a database using the SELECT..INTO clause. This requires that the SQL returns only one row, or a NULL value is assigned to the variable as can be seen in the following example:

        SELECT empno INTO empno
        FROM employee
        WHERE ename = 'JOE RICHARDS'

SELECT Statement

Table 3-27 Comparison of SELECT Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server  

CREATE PROC proc1
AS 
SELECT ename FROM employee

CREATE OR REPLACE PACKAGE PROC1Pkg AS 
TYPE RT1 IS RECORD ( 
        ENAME            
EMPLOYEE.ENAME%TYPE 
        ); 
TYPE RCT1 IS REF CURSOR RETURN RT1; 
END; 
/ 
CREATE OR REPLACE PROCEDURE PROC1( 
RC1     IN OUT PROC1Pkg.RCT1) 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        OPEN RC1 FOR 
        SELECT ENAME FROM EMPLOYEE; 
END PROC1; 
/

CREATE PROC proc1
AS 
DECLARE @name char(20)
SELECT @name = ename FROM 
employee
IF @@rowcount = 0
 RETURN 25022

CREATE OR REPLACE FUNCTION PROC1 
RETURN INTEGER 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
name    CHAR(20); 
BEGIN 
        BEGIN 
                StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
                StoO_error  := 0; 
 
                SELECT   ENAME 
       INTO PROC1.name FROM EMPLOYEE; 
       StoO_rowcnt := SQL%ROWCOUNT; 
 
         EXCEPTION 
            WHEN TOO_MANY_ROWS THEN 
                 StoO_rowcnt := 2; 
                     WHEN OTHERS THEN 
               StoO_rowcnt := 0; 
               StoO_selcnt := 0; 
               StoO_error := SQLCODE; 
              StoO_errmsg := SQLERRM; 
        END; 
        IF StoO_rowcnt = 0 THEN 
                RETURN 25022; 
        END IF; 
END PROC1; 
/

Comments

Because of the differences in their architectures, Microsoft SQL Server and Sybase Adaptive Server stored procedures return data to the client program in a different way than Oracle.

Microsoft SQL Server and Sybase Adaptive Server and Oracle can all pass data to the client using output parameters in the stored procedures. Microsoft SQL Server and Sybase Adaptive Server use another method known as result sets to transfer the data from the server to client. The examples discussed here do not return multiple rows to the client.

In Example 1, the procedure returns a single row result set to the client which is converted to a PL/SQL procedure that returns a single row using the output parameters.

Example 1:

A SELECT statement is converted into a SELECT...INTO clause and the extra parameter "i_oval1" is added to the procedure definition. Since the Migration Workbench does not currently look up the data types on the Oracle server, it sets the default data type to VAR1CHAR2.

In Microsoft SQL Server and Sybase Adaptive Server, if the SELECT statement that assigns value to a variable returns more than one value, the last value that is returned is assigned to the variable.

Example 2:

The second example illustrates fetching data into a local variable. Since this is straightforward, the Migration Workbench handles it successfully.

SELECT Statement as Part of the SELECT List

Table 3-28 Comparison of SELECT Statement as Part of the SELECT List in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS
DECLARE @x int
DECLARE @y char(20)
SELECT @x = col1, @y = (select name 
from emp)
FROM table1

CREATE OR REPLACE PROCEDURE PROC1 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
x       INTEGER; 
y       CHAR(20); 
t_var1       VARCHAR2(255); 
BEGIN 
/****** Subqueries in select list is not supported in 
Oracle. *******/ 
/****** MANUAL CONVERSION MIGHT BE REQUIRED *******/ 
        BEGIN 
        StoO_error   := 0; 
        StoO_rowcnt  := 0; 
        SELECT NAME 
        INTO t_var1 
         FROM EMP; 
        StoO_rowcnt := SQL%ROWCOUNT; 
        EXCEPTION 
        WHEN TOO_MANY_ROWS THEN 
            StoO_StoO_rowcnt  := 2; 
        WHEN OTHERS THEN 
            StoO_StoO_rowcnt  := 0; 
            StoO_error  := SQLCODE; 
            StoO_errmsg := SQLERRM; 
        END; 
        BEGIN 
               StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
                StoO_error  := 0; 
        SELECT   COL1,  t_var1 
   INTO PROC1.x, PROC1.y FROM TABLE1; 
    StoO_rowcnt := SQL%ROWCOUNT; 
             EXCEPTION 
             WHEN TOO_MANY_ROWS THEN 
                 StoO_rowcnt := 2; 
             WHEN OTHERS THEN 
              StoO_rowcnt := 0; 
              StoO_selcnt := 0; 
              StoO_error := SQLCODE; 
              StoO_errmsg := SQLERRM; 
        END; 
END PROC1; 
/ 

Comments

The Microsoft SQL Server and Sybase Adaptive Server SELECT statement with a subquery as part of the SELECT list cannot be converted to PL/SQL using the Migration Workbench. Manual changes are needed to convert this type of SELECT statement.

The Migration Workbench writes appropriate comments in the output PL/SQL procedures and the subqueries are omitted.

SELECT Statement with GROUP BY Clause

Table 3-29 Comparison of SELECT Statement with GROUP BY Clause in Oracle and Microsoft SQL Server and Sybase Adaptive Server

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 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
ename   CHAR(20); 
salary  INTEGER; 
BEGIN 
        BEGIN 
                StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
                StoO_error  := 0; 
 
       SELECT   ENAME,  SALARY 
                INTO PROC1.ename, 
PROC1.salary FROM EMP  
                WHERE SALARY > 100000  
                GROUP BY DEPTNO; 
      StoO_rowcnt := SQL%ROWCOUNT; 
 
                EXCEPTION 
          WHEN TOO_MANY_ROWS THEN 
                StoO_rowcnt := 2; 
                 WHEN OTHERS THEN 
                StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
             StoO_error := SQLCODE; 
            StoO_errmsg := SQLERRM; 
        END; 
END PROC1; 
/ 

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.

The Migration Workbench converts this type of SELECT statement to PL/SQL. However, the equivalent PL/SQL statement returns an error in Oracle.

Column Aliases

Table 3-30 Comparison of Column Aliases in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
@Status int=0
AS 
        SELECT x=sum(salary) 
FROM employee

 CREATE OR REPLACE PROCEDURE PROC1( 
Status  INTEGER  DEFAULT 0, 
RC1     IN OUT PROC1Pkg.RCT1) 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        OPEN RC1 FOR 
        SELECT SUM(SALARY)  "X" FROM 
EMPLOYEE; 
END PROC1; 
/ 

Comments

The Migration Workbench can convert Microsoft SQL Server-specific column aliases to the equivalent Oracle format. No manual changes are required.

UPDATE with FROM Statement

Table 3-31 Comparison of UPDATE with FROM Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
        UPDATE table1
        SET col1 = 1
        FROM table1, table2
        WHERE table1.id = 
table2.id

CREATE OR REPLACE PROCEDURE proc1
AS
StoO_selcnt     INTEGER;
StoO_error      INTEGER;
StoO_rowcnt     INTEGER;
BEGIN
        BEGIN
        UPDATE table1
        SET
    col1 = 1 /* FROM table1,table2 -- 
     MANUAL CONVERSION */
      WHERE table1.id = table2.id;
      StoO_rowcnt := SQL%ROWCOUNT;
      EXCEPTION
            WHEN OTHERS THEN
            StoO_error := SQLCODE;
        END;
END;

Comments

An UPDATE with a FROM clause cannot be converted. Instead, the Migration Workbench provides a comment indicating that manual conversion is required.

There are two ways to convert UPDATE with a FROM statements, and these are illustrated below.

Method 1:

Use the subquery in the SET clause if columns are being updated to values coming from a different table. For example, consider the following T/SQL statement:

UPDATE titles
SET pub_id = publishers.pub_id
FROM titles, publishers
WHERE titles.title like 'C%'
AND publishers.pub_name = 'new age'

Convert this statement to the following PL/SQL statement in Oracle :

UPDATE titles
SET pub_id 
( SELECT a.pub_id
                FROM publishers a
                WHERE publishers.pub_name = 'new age'
)
WHERE titles.title like 'C%'

Method 2:

Use the subquery in the WHERE clause for all other UPDATE...FROM statements. For example, consider the following T/SQL statement:

UPDATE shippint_parts
SET qty = 0
FROM shipping_parts sp, suppliers s
WHERE sp.supplier_num = s.supplier_num
AND s.location = "USA"

Convert this statement to the following PL/SQL statement in Oracle:

UPDATE shipping_parts
SET qty = 0 
WHERE supplier_num IN (
SELECT supplier_num 
FROM suppliers
WHERE location = 'USA')

DELETE with FROM Statement

Table 3-32 Comparison of DELETE with FROM Statement in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
        DELETE FROM table1
        FROM table1, table2
        WHERE table1.id = table2.id 

CREATE OR REPLACE PROCEDURE PROC1 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
UF1_rowid       ROWID; 
UF1_oval1       TABLE1.COL1%TYPE; 
 
CURSOR UF1_cursor IS 
        SELECT TABLE1.ROWID, 1 FROM 
TABLE1, TABLE2 
         
        WHERE TABLE1.ID = TABLE2.ID 
        FOR UPDATE OF TABLE1.COL1; 
BEGIN 
         
        OPEN UF1_cursor; 
        LOOP 
FETCH UF1_cursor INTO UF1_rowid, UF1_
oval1; 
   EXIT WHEN UF1_cursor%NOTFOUND; 
                BEGIN 
                StoO_error   := 0; 
                StoO_rowcnt  := 0; 
        UPDATE TABLE1 SET COL1 = UF1_oval1 
                WHERE ROWID = UF1_rowid; 
        StoO_rowcnt := SQL%ROWCOUNT; 
                EXCEPTION 
                        WHEN OTHERS THEN 
                  StoO_error  := SQLCODE; 
                  StoO_errmsg := SQLERRM; 
                END; 
        END LOOP; 
        CLOSE UF1_cursor; 
END PROC1; 
/

Comments

A DELETE with FROM..FROM clause must be converted manually.

While converting DELETE with FROM..FROM clause, remove the second FROM clause. For example consider the following T/SQL statement:

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

Convert the above statement to the following PL/SQL statement in Oracle:

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

Temporary Tables

Table 3-33 Comparison of Temporary Tables in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
        SELECT col1, col2
        INTO #Tab
        FROM table1
        WHERE table1.id = 100

 CREATE OR REPLACE PROCEDURE PROC1 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
    /*CONVERTING SELECT  INTO t_Tab*/ 
   /*TO INSERT INTO t_Tab*/ 
        BEGIN 
                StoO_rowcnt := 0; 
                StoO_selcnt := 0; 
                StoO_error  := 0; 
 
             INSERT INTO t_Tab 
                SELECT  
USERENV('SESSIONID'), COL1, COL2 FROM 
TABLE1  
                WHERE TABLE1.ID = 100; 
             StoO_rowcnt := SQL%ROWCOUNT; 
 
                EXCEPTION 
                 WHEN TOO_MANY_ROWS THEN 
                      StoO_rowcnt := 2; 
                       WHEN OTHERS THEN 
                       StoO_rowcnt := 0; 
                       StoO_selcnt := 0; 
                    StoO_error := SQLCODE; 
                   StoO_errmsg := SQLERRM; 
        END; 
END PROC1; 
/

Comments

Temporary tables are supported by Oracle9i and Oracle8i. The Migration Workbench utilizes this feature in Oracle9i and Oracle8i.

Also, SELECT..INTO..#TEMPTAB is converted to an INSERT statement. You must make manual changes to ensure that rows are unique to a particular session and all the rows for that session are deleted at the end of the operation. This requires that you add an extra column to the table definition and the value of USERENV('session_id') for all the rows inserted. At the end, delete all rows for that session_id. If many procedures use the same temp table in the same session, SEQUENCE can be used to make sure that the rows are unique to a particular session_id/SEQUENCE combination.

Result Set (Converted Using a Cursor Variable)

Command Option -M

Table 3-34 Comparison of Result Set in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
        SELECT col1, col2
        FROM table1

 CREATE OR REPLACE PACKAGE PROC1Pkg 
AS 
TYPE RT1 IS RECORD ( 
        COL1             
TABLE1.COL1%TYPE, 
        COL2             
TABLE1.COL2%TYPE 
        ); 
TYPE RCT1 IS REF CURSOR RETURN RT1; 
END; 
/ 
 
CREATE OR REPLACE PROCEDURE PROC1( 
RC1     IN OUT PROC1Pkg.RCT1) 
AS 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
BEGIN 
        OPEN RC1 FOR 
        SELECT COL1,  COL2 FROM 
TABLE1; 
END PROC1; 
/ 

Comments

Convert an Microsoft SQL Server and Sybase Adaptive Server procedure that returns a multi-row result set to a PL/SQL packaged function by selecting the appropriate parse option in the property sheet for a stored procedure.

The T/SQL SELECT statement is converted to a cursor and a cursor variable is added as an OUT parameter to return the data back to the calling program. Use the cursor referenced by the cursor variable to fetch the result rows.

For more details on how Result Sets are handled by the Migration Workbench, see T/SQL and PL/SQL Language Elements section in this chapter.

Cursor Handling

Table 3-35 Comparison of Cursor Handling Result Set in Oracle and Microsoft SQL Server and Sybase Adaptive Server

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 
StoO_selcnt     INTEGER; 
StoO_error      INTEGER; 
StoO_rowcnt     INTEGER; 
StoO_errmsg     VARCHAR2(255); 
StoO_sqlstatus  INTEGER; 
empno   INTEGER; 
ename   CHAR(100); 
sal     NUMBER; 
CURSOR CURSOR_1 IS 
     SELECT  EMPNO, ENAME, SAL 
         FROM EMP; 
 
BEGIN 
        OPEN CURSOR_1; 
 
         CURSOR_1 INTO 
     cursor_demo.empno, cursor_
demo.ename, cursor_demo.sal; 
 
 
        IF CURSOR_1%NOTFOUND THEN 
                StoO_sqlstatus := 2; 
        ELSE 
                StoO_sqlstatus := 0; 
        END IF; 
        CLOSE CURSOR_1; 
 
/*[SPCONV-ERR(xxx)]:Deallocate Cursor 
is not supported*/ 
 
        NULL; 
 
END CURSOR_DEMO; 
/

Comments

Microsoft SQL Server and Sybase Adaptive Server introduced cursors in T/SQL. Syntactical conversion of cursors from Microsoft SQL Server and Sybase Adaptive Server to Oracle is very straightforward.

Transaction Handling Statements

Table 3-36 Comparison of Transaction-Handling Statements in Oracle and Microsoft SQL Server and Sybase Adaptive Server

CREATE PROC proc1
AS 
        BEGIN TRAN tran1

        UPDATE table1
        SET id = id + 1
        WHERE name = 'Event'

        IF @@Rowcount != 1
        BEGIN
          ROLLBACK TRAN tran1
                RETURN 25700
        END

        COMMIT TRAN tran1
        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 
        SAVEPOINT TRAN1; 
        BEGIN 
        StoO_error   := 0; 
        StoO_rowcnt  := 0; 
        UPDATE TABLE1 
        SET ID = ID + 1 
         
        WHERE NAME = 'Event'; 
        StoO_rowcnt := SQL%ROWCOUNT; 
        EXCEPTION 
                WHEN OTHERS THEN 
            StoO_error  := SQLCODE; 
            StoO_errmsg := SQLERRM; 
        END; 
        IF StoO_rowcnt != 1 THEN 
        BEGIN 
         ROLLBACK TO SAVEPOINT TRAN1; 
                RETURN 25700; 
        END; 
        END IF; 
        COMMIT WORK; 
        RETURN 0; 
END PROC1; 
/  

Comments

The Migration Workbench does a one-to-one mapping when converting Microsoft SQL Server and Sybase Adaptive Server transaction commands to their Oracle equivalents. For more details about how transactions are handled in Oracle, see the Transaction-Handling Semantics topic later in this chapter.

T/SQL and PL/SQL Language Elements

T/SQL is the Microsoft SQL Server and Sybase Adaptive Server procedural SQL language and PL/SQL is the Oracle procedural SQL language. This section discusses the following T/SQL and PL/SQL language elements:

• Transaction Handling Semantics
  
  
• Exception-Handling and Error-Handling Semantics
  
  
• Special Global Variables
  
  
• Operators
  
  
• Built-in Functions
  
  
• Sending Data to the Client: Result Sets
  
  
• DDL Constructs within Microsoft SQL Server and Sybase Adaptive Server Stored Procedures

Transaction Handling Semantics

Microsoft SQL Server and Sybase Adaptive Server

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

Microsoft SQL Server and Sybase Adaptive Server provide 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 and Sybase Adaptive Server, the default is isolation level 1.

To implement isolation level 3, Microsoft SQL Server and Sybase Adaptive Server apply HOLDLOCK to all the tables taking part in the transaction. In Microsoft SQL Server and Sybase Adaptive Server, HOLDLOCK, along with page-level locks, can block users for a considerable length of time, causing poor response time.

If the Microsoft SQL Server and Sybase Adaptive Server application implements ANSI-standard chained (implicit) transactions with isolation level 3, the application migrates smoothly to Oracle because Oracle implements the ANSI-standard implicit transaction model, which ensures repeatable reads.

In a non-ANSI standard application, Microsoft SQL Server and Sybase Adaptive Server transactions are explicit. A logical transaction has to be explicitly started with the statement BEGIN TRANSACTION. The transaction is committed with a COMMIT TRANSACTION or rolled back with a ROLLBACK TRANSACTION statement. The transactions can be named. For example, the following statement starts a transaction named

account_tran.
BEGIN TRANSACTION account_tran

The explicit transaction mode allows nested transactions. However, the nesting is only syntactical. Only outermost BEGIN TRANSACTION and COMMIT TRANSACTION statements actually create and commit the transaction. This could be confusing as the inner COMMIT TRANSACTION does not actually commit.

The following example illustrates the nested transactions:

BEGIN TRANSACTION
        /* T/SQL  Statements */
        BEGIN TRANSACTION
        /* T/SQL  Statements */
                BEGIN TRANSACTION account_tran
                /* T/SQL  Statements */
                IF SUCCESS
                        COMMIT TRANSACTION account_tran
                ELSE
                        ROLLBACK TRANSACTION account_tran
                END IF
        /* T/SQL  Statements */
        IF SUCCESS
                COMMIT TRANSACTION
        ELSE
                ROLLBACK TRANSACTION
        END IF
        /* T/SQL  Statements */
COMMIT TRANSACTION

When BEGIN TRANSACTION and COMMIT TRANSACTION statements are nested, the outermost pair creates and commits the transaction while the inner pairs only keep track of nesting levels. The transaction is not committed until the outermost COMMIT TRANSACTION statement is executed. Normally the nesting of the transaction occurs when stored procedures containing BEGIN TRANSACTION /COMMIT TRANSACTION statements call other procedures with transaction-handling statements. The global variable @@trancount keeps track of the number of currently active transactions for the current user. If you have more than one open transaction, you need to ROLLBACK, then COMMIT.

The named and unnamed inner COMMIT TRANSACTION statements have no effect. The inner ROLLBACK TRANSACTION statements without the name roll back the statements to the outermost BEGIN TRANSACTION statement and the current transaction is canceled. The named inner ROLLBACK TRANSACTION statements cancel the respective named transactions.

Oracle

Oracle applies ANSI-standard implicit transaction methods. A logical transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK, or connection to the database. A transaction ends with a COMMIT, ROLLBACK, or disconnection from the database. An implicit COMMIT statement is issued before and after each DDL statement. The implicit transaction model prevents artificial nesting of transactions because only one logical transaction per session can be in effect. The user can set SAVEPOINT in a transaction and roll back a partial transaction to the SAVEPOINT.

For example:

UPDATE test_table SET col1='value_1';
SAVEPOINT first_sp;
UPDATE test_table SET col1='value_2';
ROLLBACK TO SAVEPOINT first_sp;
COMMIT;   /* col1 is 'value_1'*/

Conversion Preparation Recommendations

Logical transactions are handled differently in Microsoft SQL Server and Sybase Adaptive Server and Oracle. In Microsoft SQL Server and Sybase Adaptive Server, transactions are explicit by default. Oracle implements ANSI-standard implicit transactions. This prevents a direct conversion from T/SQL transaction-handling statements to PL/SQL transaction-handling statements.

Also, Microsoft SQL Server and Sybase Adaptive Server require that transactions in stored procedures be allowed to nest, whereas Oracle does not support transaction nesting.

The following table compares Microsoft SQL Server and Sybase Adaptive Server to Oracle transaction-handling statements:

Table 3-37 Comparison of Transaction-Handling Statements in Oracle and Microsoft SQL Server and Sybase Adaptive Server

BEGIN TRAN

BEGIN TRAN tran_1

 SAVEPOINT tran_1

COMMIT TRAN

 COMMIT

COMMIT TRAN

COMMIT TRAN tran_1

  COMMIT

COMMIT TRAN tran_1

ROLLBACK TRAN

ROLLBACK

ROLLBACK TRAN tran_1

ROLLBACK TO SAVEPOINT tran_1

At the time of conversion, the Migration Workbench cannot determine the nest level of the current transaction-handling statement. The variable @@trancount is a runtime environment variable.

Table 3-38 shows the currently implemented Microsoft SQL Server and Sybase Adaptive Server to Oracle conversion strategy for the transaction-handling statements

Table 3-38 Conversion Strategy for Transaction-Handling Statements  

BEGIN TRAN

/*BEGIN TRAN  >>> statement ignored <<<*/

BEGIN TRAN tran_1

 SAVEPOINT tran_1;

COMMIT TRAN 

 COMMIT WORK;

COMMIT TRAN

 COMMIT WORK;

COMMIT TRAN tran_1

 COMMIT WORK;

COMMIT TRAN tran_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 and Sybase Adaptive Server, make the changes so that at least the outermost transaction is controlled in the client application.

Exception-Handling and Error-Handling Semantics

Microsoft SQL Server and Sybase Adaptive Server

In Microsoft SQL Server and Sybase Adaptive Server, you must check for errors after each SQL statement because control is passed to the next statement regardless of any error conditions generated by the previous statement. The client ERROR_HANDLER routine is invoked as a call-back routine if any server error occurs, and the error conditions can be handled in the call back routine.

Stored procedures use the RAISERROR statement to notify the client of any error condition. This statement does not cause the control to return to the calling routine.

Microsoft SQL Server 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 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 and Sybase Adaptive Server behavior in Oracle, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with the exceptions that need to be trapped for the SQL statement.

See the T/SQL Versus PL/SQL Constructs section in this chapter for more information about the extra code required to simulate Microsoft SQL Server and Sybase Adaptive Server behavior.

If the RAISERROR statement in an Microsoft SQL Server and Sybase Adaptive Server stored procedure is immediately followed by the RETURN statement, these two statements can be converted to the Oracle RAISE_APPLICATION_ERROR statement.

You can customize error messages with the help of a user-defined table. You can write standard routines to add and retrieve error messages to this table. This method serves a two-fold purpose: it ensures that the system is portable, and it gives the administrator centralized control over the error messages.

Special Global Variables

Microsoft SQL Server and Sybase Adaptive Server

The following global variables are particularly useful in the conversion process:

@@error: 

The server error code indicating the execution status of the most recently executed T/SQL statement. For code examples, see the @@Rowcount and @@Error Variables topic.

@@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 the @@Rowcount and @@Error Variables topic.

@@servername:

The name of the local Microsoft SQL Server and Sybase Adaptive Server server.

@@sqlstatus: 

The status information resulting from the last FETCH statements.

@@tranchained: 

The current transaction mode of the T/SQL procedure. If @@tranchained returns 1, the TL/SQL procedure is in chained, or implicit transaction mode.

@@trancount: 

Keeps track of the nesting level for the nested transactions for the current user.

@@transtate: 

The current state of the transaction.

Oracle

SQLCODE:

The server error code indicating the execution status of the most recently executed PL/SQL statement.

SQL%ROWCOUNT:

The variable attached to the implicit cursor associated with each SQL statement executed from within the PL/SQL procedures. This variable contains the number of rows affected by the execution of the SQL statement attached to the implicit cursor.

Recommendations:

The @@error variable has a direct equivalent in Oracle, and that is the SQLCODE function. The SQLCODE function returns the server error code.

The SQL%ROWCOUNT variable in Oracle is functionally equivalent to @@rowcount.

There are many more special global variables available with PL/SQL. Not all those variables are listed here. There are more special global variables available in T/SQL also. Not all those variables are listed here because they do not play a major role in the conversion process.

Operators

See the Data Manipulation Language section in Chapter 2 for a discussion of Microsoft SQL Server and Sybase Adaptive Server and Oracle operators.

Built-in Functions

See the Data Manipulation Language section in Chapter 2 for a discussion of built-in functions in Microsoft SQL Server and Sybase Adaptive Server and Oracle.

Sending Data to the Client: Result Sets

Single Result Set

Microsoft SQL Server and Sybase Adaptive Server stored procedures can return data to the client by means of a Result Set. A SELECT statement that does not assign values to the local variables sends the data to the client in the form of byte-stream.

In a case where a third-party user interface product uses the result set capability of Microsoft SQL Server and Sybase Adaptive Server, consult with the vendor to make sure that the same functionality is available for the Oracle database.

The following example procedure sends the data out as a result set. More appropriately, an OUTPUT parameter holding the value "YES" or "NO" (depending upon the evaluation of <condition>) or a function returning "YES" or "NO" should have been used.

CREATE PROCEDURE x
AS
BEGIN
...
...
IF <condition> THEN
  SELECT "YES"
ELSE
  SELECT "NO"
END

Multiple Result Sets

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

The following procedure returns two different result sets, which the client is responsible for processing:

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 empno.empno = dept.empno
AND emp.empno BETWEEN 1000 and 2000
END

Recommendations

Some alternatives to simulating the result set in PL/SQL procedures are presented below:

• Packaged procedures with PL/SQL tables as output parameters
  
  
• This is an extension of the first method. Instead of fetching one row at a time, now we fetch many rows (ARRAY FETCH) at a time and assign the values to PL/SQL tables. These tables are available to the client after the execution of the procedure.
  
  
• Packaged procedures with a cursor variable as output parameter
  
  
• This alternative is possible in Oracle. Oracle allows you to define a cursor type variable to clearly return query results. This cursor type variable is similar to the user-defined record type and array variable. 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. Since it is a PL/SQL variable, it can be passed into and out of procedures like any other PL/SQL variable. This is a more direct equivalent to the result set in Microsoft SQL Server and Sybase Adaptive Server.
  
  
• Procedure or function that populates a temporary table with result set rows
  
  
• This temporary table has an additional column to hold the SESSION_ID of the current session to keep the rows separate for each session of the user. The client program can then retrieve the rows from this temporary table with a simple SELECT statement.
  
  
• The Migration Workbench adopts the third option to convert the result set.

About Converting a T/SQL Procedure with a Result Set

Method 1

A T/SQL procedure with a result set may need some manual changes after conversion to an Oracle package with a member function. The problems are described in detail below.

For example, consider the following T/SQL procedure:

CREATE PROC test_proc
AS
BEGIN
        T/SQL block1
        T/SQL block2
        SELECT statement corresponding to the result set
END

This procedure executes two T/SQL blocks before executing the SELECT statement associated with the result set. The procedure is converted to an Oracle package as follows:

CREATE OR REPLACE PACKAGE BODY test_proc_pkg
AS
BEGIN
        FUNCTION test_proc;
END;
CREATE OR REPLACE PACKAGE BODY test_proc_pkg
AS
BEGIN
        cursor declaration for the SELECT statement associated with the result 
set in the source T/SQL procedure;
        FUNCTION test_proc
        RETURN INTEGER
        AS
        BEGIN
                PL/SQL version of T/SQL block1;
                PL/SQL version of T/SQL block2;
                FETCH loop for the cursor declared in the package body;
        END;
END;

The two T/SQL blocks in the source T/SQL procedure are executed only once when the procedure is called, and the result set is sent to the client.

In Oracle client, to simulate the fetching of the result set, the TEST_PROC_PKG.TEST_PROC function must be called repeatedly until all the rows from the cursor are fetched. The two PL/SQL blocks in the function are executed with each call to the function. This behavior differs from that in the source application.

You must manually separate the code associated with the FETCH loop for the cursor for the result set from the remaining code in the procedure. Changes to the client have to be made so that the rest of the procedure's code is called in accurate sequence with the repeated calls to the function returning rows from the result set.

The final Oracle package should be as follows:

CREATE OR REPLACE PACKAGE BODY test_proc_pkg
AS
BEGIN
        PROCEDURE proc1;
        FUNCTION test_proc;
END;
CREATE OR REPLACE PACKAGE BODY test_proc_pkg
AS
BEGIN
        cursor declaration for the SELECT statement associated with the result 
set in the source T/SQL procedure;
        PROCEDURE proc1
        AS
        BEGIN
                PL/SQL version of T/SQL block1;
                PL/SQL version of T/SQL block2;
        END;
        FUNCTION test_proc
        RETURN INTEGER
        AS
        BEGIN
                FETCH loop for the cursor declared in the package body;
        END;
END;

The client should call the TEST_PROC_PKG.PROC1 procedure before repeatedly calling the TEST_PROC.PKG.TEXT_PROC function in order to achieve functionality similar to the source T/SQL procedure.

The variables that are common to these two parts should be either declared globally within the package body or should be passed as parameters to the procedure and the function.

DDL Constructs within Microsoft SQL Server and Sybase Adaptive Server Stored Procedures

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

Most of the T/SQL DDL constructs give syntax errors. You must remove the DDL statements from the T/SQL source to convert the T/SQL procedure to PL/SQL using the Migration Workbench.

The following DDL statements are ignored by the Migration Workbench. The statements appear commented in the output with a message "statement ignored."

CREATE TABLE
DROP TABLE
CREATE VIEW
DROP VIEW
CREATE INDEX
DROP INDEX