3.1 Triggers

Microsoft SQL Server or Sybase Adaptive Server database triggers are AFTER triggers. This means that triggers are fired after the specific operation is performed. For example, the INSERT trigger fires after the rows are inserted into the database. If the trigger fails, the operation is rolled back.

Microsoft SQL Server or Sybase Adaptive Server allows INSERT, UPDATE, and DELETE triggers. Triggers typically need access to the before image and after image of the data that is being changed. Microsoft SQL Server or Sybase Adaptive Server achieves this with two temporary tables called INSERTED and DELETED. These two tables exist during the execution of the trigger. These tables and the table for which the trigger is written have the exact same structure. The DELETED table holds the before image of the rows that are undergoing change because of the UPDATE/DELETE operation, and the INSERTED table holds the after image of the rows for the INSERT/UPDATE operation. If there is an error, the triggers can issue a rollback statement.

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

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

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

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

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

BEFORE STATEMENT trigger

BEFORE ROW trigger

AFTER STATEMENT trigger

AFTER ROW trigger

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

The triggering code reads from its own table (mutating).

The triggering code contains group functions.

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

3.2 Stored Procedures

Microsoft SQL Server or Sybase Adaptive Server stores triggers and stored procedures with the server. Oracle stores triggers and stored subprograms with the server. Oracle has three different kinds of stored subprograms, namely functions, stored procedures, and packages. For detailed discussion on all these objects, see Oracle Database PL/SQL Language Reference.

Stored procedures provide a powerful way to code the application logic that can be stored with the server. Microsoft SQL Server or Sybase Adaptive Server and Oracle all provide stored procedures.

The language used to code these objects is a database-specific procedural extension to SQL. In Oracle it is PL/SQL and in Microsoft SQL Server or Sybase Adaptive Server it is Transact SQL (T/SQL). These languages differ to a considerable extent. The individual SQL statements and the procedural constructs, such as if-then-else, are similar in both versions of the procedural SQL. Considerable differences can be found in the following areas discussed in these sections:

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

3.2.1 Individual SQL Statements

In individual SQL statements, you should try to follow ANSI-standard SQL whenever possible. However, there are cases where you need to use database-specific SQL constructs, mostly for ease of use, simplicity of coding, and performance enhancement. For example, Microsoft SQL Server or Sybase Adaptive Server constructs such as the following are SQL Server or Sybase Adaptive Server-specific:

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

The conversion performed by the automatic conversion utilities for statements such as this can be seen in the following examples:

3.2.1.4 Oracle:

All the ANSI-standard SQL statements can be converted from one database to another using automatic conversion utilities.

3.2.2 Logical Transaction Handling

In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit by definition. This implies that an individual SQL statement is not part of a logical transaction by default. A SQL statement belongs to a logical transaction if the transaction explicitly initiated by the user with a BEGIN TRANSACTION (or BEGIN TRAN) statement is still in effect. The logical transaction ends with a corresponding COMMIT TRANSACTION (or COMMIT TRAN) or ROLLBACK TRANSACTION (or ROLLBACK TRAN) statement. Each SQL statement that is not part of a logical transaction is committed on completion.

In Oracle, transactions are implicit as set by the ANSI standard. The implicit transaction model requires that each SQL statement is part of a logical transaction. A new logical transaction is automatically initiated when a COMMIT or ROLLBACK command is executed. This also implies that data changes from an individual SQL statement are not committed to the database after execution. The changes are committed to the database only when a COMMIT statement is run. The differences in the transaction models impact the coding of application procedures.

3.2.2.1 Transaction-Handling Statements

For client/server applications, it is recommended that you make the transaction-handling constructs part of the client procedures. The logical transaction is always defined by client users, and they should control it. This strategy is also more suitable for distributed transactions, where the two-phase commit operations are necessary. Making the transaction-handling statements a part of the client code serves a two-fold purpose; the server code is more portable, and the distributed transactions can be independent of the server code. Try to avoid using the BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN statements in the stored procedures. In Microsoft SQL Server or Sybase Adaptive Server, transactions are explicit. In Oracle, transactions are implicit. If the transactions are handled by the client, the application code residing on the server can be independent of the transaction model.

3.2.3 Error Handling Within the Stored Procedure

Oracle PL/SQL checks each SQL statement for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler. This avoids you having to check the status of every SQL statement. For example, if a SELECT statement does not find any rows in the database, an exception is raised, and the code to deal with this error is executed.

In Microsoft SQL Server or Sybase Adaptive Server, you need not check for errors after each SQL statement. Control is passed to the next statement, irrespective of the error conditions generated by the previous statement. It is your responsibility to check for errors after the execution of each SQL statement. Failure to do so may result in erroneous results.

In Oracle, to simulate the behavior of Microsoft SQL Server or Sybase Adaptive Server and to pass the control to the next statement regardless of the status of execution of the previous SQL statement, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with all possible exceptions for that SQL statement. This coding style is required only to simulate Microsoft SQL Server or Sybase Adaptive Server behavior. An Oracle PL/SQL procedure ideally has only one exception block, and all error conditions are handled in that block.

Consider the following code in a Microsoft SQL Server or Sybase Adaptive Server stored procedure:

In this code example, if the first SELECT statement does not return any rows, the value of @x could be UNDEFINED. If the control is passed on to the next statement without raising an exception, the second statement returns incorrect results because it requires the value of @x to be set by an earlier statement. In a similar situation, Oracle PL/SQL raises a NO_DATA_FOUND exception if the first statement fails.

3.2.3.1RAISERROR Statement

The Microsoft SQL Server or Sybase Adaptive Server RAISERROR statement does not return to the calling routine. The error code and message is passed to the client, and the execution of the stored procedure continues further. The Oracle RAISE_APPLICATION_ERROR statement returns to the calling routine. As a standard, a RETURN statement must appear after the RAISERROR statement in Microsoft SQL Server or Sybase Adaptive Server, so that it can be converted to the Oracle RAISE_APPLICATION_ERROR statement.

3.2.3.2Customized Error Messages

Microsoft SQL Server or Sybase Adaptive Server allows you to customize the error messages using a system table. The system procedures allow the user to add error messages to the system. Adding error messages to the Microsoft SQL Server or Sybase Adaptive Server system table is not desirable because there is no equivalent on the Oracle system. This can be avoided by maintaining a user-defined error messages table, located in the centralized database. Standard routines can be written to add the error message to the table and retrieve it whenever necessary. This method serves a two-fold purpose: it ensures that the system is more portable across different types of database servers, and it gives the administrator centralized control over the error messages.

3.3 Data Types

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

The CREATE PROCEDURE statement creates the named stored procedure in the database.

You can optionally specify the parameters passed to the procedure as OUTPUT. Values of OUTPUT variables are available to the calling routine after the procedure is executed. The parameters specified without the OUTPUT keyword are considered as input parameters.

The keyword AS indicates the start of the body of the procedure.

The BEGIN and END keywords that enclose the stored procedure body are optional; all the procedural statements contained in the file after AS are considered part of the stored procedure if BEGIN and END are not used to mark blocks.

See the T/SQL and PL/SQL Language Elements section of this chapter for more information about the constructs allowed in T/SQL procedures.

Description:

The OR REPLACE keywords replace the procedure by the new definition if it already exists.

The parameters passed to the PL/SQL procedure can be specified as IN (input), OUT (output only), or IN OUT (input and output). In the absence of these keywords, the parameter is assumed to be the "IN" parameter.

The keyword IS or AS indicates the start of the procedure. The local variables are declared after the keyword IS or AS and before the keyword BEGIN.

The BEGIN and END keywords enclose the body of the procedure.

Permissions:

You must have the CREATE PROCEDURE system privilege to create the stored procedures. For Microsoft SQL Server 2005, you must also have ALTER permission on the schema in which the function is being created.

Permissions:

To create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create a procedure in another user's schema, you must have the CREATE ANY PROCEDURE system privilege.

Recommendations:

Functionally identical parts can be identified in the T/SQL procedure and PL/SQL procedure structure. Therefore, you can automate the conversion of most of the constructs from Microsoft SQL Server or Sybase Adaptive Server to Oracle.

OR REPLACE keywords in an Oracle CREATE PROCEDURE statement provide an elegant way of recreating the procedure. In Microsoft SQL Server or Sybase Adaptive Server, the procedure must be dropped explicitly before replacing it.

Microsoft SQL Server or Sybase Adaptive Server stored procedures can only return integer values to the calling routine using the RETURN statement. In the absence of a RETURN statement, the stored procedure still returns a return status to the calling routine. This value can be captured in the "return_value" variable.

The formal_parameter is the parameter in the procedure definition. The actual_parameter is defined in the local block which calls the procedure supplying the value of the actual parameter for the respective formal parameter. The association between an actual parameter and formal parameter can be indicated using either positional or named notation.

Description:

Oracle PL/SQL procedures send data back to the calling routine by means of OUT parameters. Oracle offers functions that are a different type of schema objects. Functions can return an atomic value to the calling routine using the RETURN statement. The RETURN statement can return value of any data type.

The formal_parameter is the parameter in the procedure definition. The actual_parameter is defined in the local block which calls the procedure supplying the value of the actual parameter for the respective formal parameter. The association between an actual parameter and formal parameter can be indicated using either positional or named notation.

Positional notation: The actual parameters are supplied to the procedure in the same order as the formal parameters in the procedure definition.

Named notation: The actual parameters are supplied to the procedure in an order different than that of the formal parameters in the procedure definition by using the name of the formal parameter as:

@formal_parameter = @actual_parameter

A constant literal can be specified in the place of the following:

'@actual_parameter ' as:
@formal_parameter = 10

@formal_parameter = 10

The keyword OUTPUT should be specified if the procedure has to return the value of that parameter to the calling routine as OUTPUT.

Positional notation: The actual parameters are supplied to the procedure in the same order as the formal parameters in the procedure definition.

Named notation: The actual parameters are supplied to the procedure in an order different than that of the formal parameters in the procedure definition by using the name of the formal parameter as:

formal_parameter => actual_parameter

A constant literal can be specified in the place of the following:

as:
formal_parameter => 10

If the formal_parameter is specified as OUT or IN OUT in the procedure definition, the value is made available to the calling routine after the execution of the procedure.

Permissions:

The user should have the EXECUTE permission on the stored procedure. The user need not have explicit privileges to access the underlying objects referred to within the stored procedure.

Permissions

The user should have the EXECUTE privilege on the named procedure. The user need not have explicit privileges to access the underlying objects referred to within the PL/SQL procedure

To recompile a procedure using the sp_recompile system stored procedure:

sp_recompile my_proc

Syntax:

ALTER PROCEDURE [schema.]procedure COMPILE

Description:

This command causes the recompilation of the procedure. Procedures that become invalid for some reason should be recompiled explicitly using this command.

Description:

This command causes the recompilation of the procedure. Procedures that become invalid for some reason should be recompiled explicitly using this command. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead

Permissions:

The owner of the procedure can issue this command

Permissions:

The procedure must be in the user's schema or the user must have the ALTER ANY PROCEDURE privilege to use this command

Example:

sp_recompile my_proc

Example:

ALTER PROCEDURE sam.credit COMPILE;

3.4.2Function

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

3.4.2.1 Create

In Microsoft SQL Server or Sybase Adaptive Server, you can convert a stored procedure to a function in Oracle because the stored procedure in Microsoft SQL Server or Sybase Adaptive Server can RETURN an integer value to the calling routine using a RETURN statement. A stored procedure returns a status value to the calling routine even in the absence of a RETURN statement. The returned status is equal to ZERO if the procedure execution is successful or NON-ZERO if the procedure fails for some reason. The RETURN statement can return only integer values

Functions can return an atomic value to the calling routine using the RETURN statement.

A function can be called as part of an expression. This is a very powerful concept. All the Microsoft SQL Server or Sybase Adaptive Server built-in functions can be coded using PL/SQL, and these functions can be called like any other built-in functions in an expression, starting with Oracle.

N/A

Permissions:

You should have the EXECUTE privilege on the function to execute the named function. You need not have explicit privileges to access the underlying objects that are referred to within the PL/SQL function.

This command causes the recompilation of a function. Functions become invalid if the objects that are referenced from within the function are dropped or altered. Functions that become invalid for some reason should be recompiled explicitly using this command. Explicit recompilation eliminates the need for implicit recompilation and prevents associated runtime compilation errors and performance overhead.

N/A

Permissions:

The function must be in the user's schema or the user must have the ALTER ANY PROCEDURE privilege to use this command

N/A

Example:

ALTER FUNCTION sam.credit COMPILE

3.4.3Package

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

CREATE PACKAGE sets up the specification for a PL/SQL package which can be a group of procedures, functions, exception, variables, constants, and cursors.

Functions and procedures of the package can share data through variables, constants, and cursors.

The OR REPLACE keywords replace the package by the new definition if it already exists. This requires recompilation of the package and any objects that depend on its specification.

N/A

Permissions:

To create a package in the user's own schema, the user must have the CREATE PROCEDURE system privilege. To create a package in another user's schema, the user must have the CREATE ANY PROCEDURE system privilege.

3.4.3.2 Drop

Microsoft SQL Server or Sybase Adaptive Server does not support this concept.

Syntax:

DROP PACKAGE [BODY] [schema.]package

N/A

Description:

The BODY option drops only the body of the package. If you omit BODY, Oracle drops both the body and specification of the package. If you drop the body and specification of the package, Oracle invalidates any local objects that depend on the package specification.

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

When a package is dropped, Oracle invalidates all the local objects that reference the dropped package.

N/A

Permissions:

The package must be in the schema of the user or the user must have the DROP ANY PROCEDURE system privilege to execute this command.

3.4.3.3 Alter

Microsoft SQL Server or Sybase Adaptive Server does not support this concept.

Syntax:

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

N/A

Description:

Packages that become invalid for some reason should be recompiled explicitly using this command.

This command causes the recompilation of all package objects together. You cannot use the ALTER PROCEDURE or ALTER FUNCTION commands to individually recompile a procedure or function that is part of a package.

PACKAGE, the default option, recompiles the package body and specification.

3.4.4.2 Drop

Microsoft SQL Server or Sybase Adaptive Server does not support this concept.

Syntax:

DROP PACKAGE [BODY] [schema.]package

N/A

Description:

The BODY option drops only the body of the package. If you omit BODY, Oracle drops both the body and specification of the package. If you drop the body and specification of the package, Oracle invalidates any local objects that depend on the package specification.

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

When a package is dropped, Oracle invalidates all the local objects that reference the dropped package.

N/A

Permissions:

The package must be in the your own schema or you must have the DROP ANY PROCEDURE system privilege to execute this command.

3.4.4.3 Alter

Microsoft SQL Server or Sybase Adaptive Server does not support this concept.

Syntax:

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

N/A

Description:

Packages that become invalid for some reason should be recompiled explicitly using this command.

This command causes the recompilation of all package objects together. You cannot use the ALTER PROCEDURE or ALTER FUNCTION commands to individually recompile a procedure or function that is part of a package.

PACKAGE, the default option, recompiles the package body and specification.

The package must be your own schema or you must have the ALTER ANY PROCEDURE privilege to use this command.

N/A

Example:

ALTER PACKAGE emp_actions COMPILE BODY

3.5T/SQL Versus PL/SQL Constructs

This section provides information about the Microsoft SQL Server or Sybase Adaptive Server constructs and equivalent Oracle constructs generated by SQL Developer. The conversions of the following constructs are discussed in detail:

Listed is the syntax for the Microsoft SQL Server or Sybase Adaptive Server constructs and their Oracle equivalents, as well as comments about conversion considerations.

The procedures in the Oracle column are the direct output of SQL Developer. In general, SQL Developer deals with the Microsoft SQL Server or Sybase Adaptive Server T/SQL constructs in one of the following ways:

The ANSI-standard SQL statements are converted to PL/SQL because it supports ANSI-standard SQL.

Microsoft SQL Server or Sybase Adaptive Server-specific constructs are converted into PL/SQL constructs if the equivalent constructs are available in PL/SQL.

Some Microsoft SQL Server or Sybase Adaptive Server-specific constructs are ignored and appropriate comments are incorporated in the output file.

Constructs that require manual conversion are wrapped around with proper comments in the output file.

For Microsoft SQL Server or Sybase Adaptive Server-specific constructs that result in syntax errors, an appropriate error message is displayed including the line number.

Parameter passing is almost the same in Microsoft SQL Server or Sybase Adaptive Server and Oracle. By default, all the parameters are INPUT parameters, if not specified otherwise.

The value of the INPUT parameter cannot be changed from within the PL/SQL procedure. Thus, an INPUT parameter cannot be assigned any values nor can it be passed to another procedure as an OUT parameter. In Oracle, only IN parameters can be assigned a default value.

The @ sign in a parameter name declaration is removed in Oracle.

In Oracle, the parameter data type definition does not include length/size.

Microsoft SQL Server or Sybase Adaptive Server data types are converted to Oracle base data types. For example, all Microsoft SQL Server or Sybase Adaptive Server numeric data types are converted to NUMBER and all alphanumeric data types are converted to VARCHAR2 and CHAR in Oracle.

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

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

IF (SELECT... ) with comparison does not have an equivalent PL/SQL construct. Therefore, it is converted to a SELECT INTO...WHERE... clause, as shown in Example 4 in Table 3-19.

A RETURN statement is used to return a single value back to the calling program and works the same in both databases. Microsoft SQL Server or Sybase Adaptive Server can return only the numeric data type, while Oracle can return any of the server data types or the PL/SQL data types.

In a PL/SQL procedure, a RETURN statement can only return the control back to the calling program without returning any data. SQL Developer translates procedures returning values to functions automatically.

Microsoft SQL Server or Sybase Adaptive Server uses RAISERROR to notify the client program of any error that occurred. This statement does not end the execution of the procedure, and the control is passed to the next statement.

PL/SQL provides similar functionality with RAISE_APPLICATION_ERROR statements.However, it ends the execution of the stored subprogram and returns the control to the calling program. It is equivalent to a combination of RAISERROR and a RETURN statement.

SQL Developer can convert most WHILE constructs. However, the CONTINUE within a WHILE loop in Microsoft SQL Server or Sybase Adaptive Server does not have a direct equivalent in PL/SQL. It is simulated using the GOTO statement with a label.

@@error is converted to v_sys_error, which contains the value returned by the SQLCODE function. The value returned by SQLCODE should only be assigned within an exception block; otherwise, it returns a value of zero.

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

PL/SQL assigns values to a variable as follows:

It uses the assignment statement to assign the value of a variable or an expression to a local variable. It assigns a value from a database using the SELECT..INTO clause. This requires that the SQL returns only one row.

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

Microsoft SQL Server or Sybase Adaptive Server and Oracle can all pass data to the client using output parameters in the stored procedures. Microsoft SQL Server or Sybase Adaptive Server 6.5 uses another method known as result sets to transfer the data from the server to client.

3.5.13SELECT Statement with GROUP BY Clause

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

In PL/SQL, deallocation of cursors is not required because it happens automatically.

3.6T/SQL and PL/SQL Language Elements

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

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

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

account_tran.
BEGIN TRANSACTION account_tran

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

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.

Because of the difference in the way the two databases handle transactions, you may want to consider some reorganization of the transactions.

Try to design client/server applications so that the transaction-handling statements are part of the client code rather than the stored procedure code. This strategy should work because the logical transactions are almost always designed by the user and should be controlled by the user.

For the conversion of stored procedures, consider setting a SAVEPOINT at the beginning of the procedures, and roll back only to the SAVEPOINT. In Microsoft SQL Server or Sybase Adaptive Server, make the changes so that at least the outermost transaction is controlled in the client application.

3.6.2Exception-Handling and Error-Handling Semantics

Microsoft SQL Server or Sybase Adaptive Server

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

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

Microsoft SQL Server or Sybase Adaptive Server allows you to customize the error messages using a system table. The system procedures allow the user to add error messages to this table.

Oracle

In Oracle, each SQL statement is automatically checked for errors before proceeding with the next statement. If an error occurs, control immediately jumps to an exception handler if one exists. This frees you from needing to check the status of every SQL statement. For example, if a SELECT statement does not find any row in the database, an exception is raised. The corresponding exception handler part of the block should include the code to deal with this error. The built-in RAISE_APPLICATION_ERROR procedure notifies the client of the server error condition and returns immediately to the calling routine.

Oracle places an implicit SAVEPOINT at the beginning of a procedure. The built-in RAISE_APPLICATION_ERROR procedure rolls back to this SAVEPOINT or the last committed transaction within the procedure. The control is returned to the calling routine.

The Oracle RAISE_APPLICATION_ERROR statement allows the user to customize the error message. If an exception is raised, SQLCODE is returned automatically by PL/SQL to the caller. It keeps propagating until it is handled.

Recommendations

To simulate Microsoft SQL Server or Sybase Adaptive Server behavior in Oracle, you must enclose each SQL statement in an equivalent PL/SQL block. This block must deal with the exceptions that need to be trapped for the SQL statement.

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

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

3.6.3Special Global Variables

Microsoft SQL Server or Sybase Adaptive Server

In Microsoft SQL Server or Sybase Adaptive Server, the following global variables are particularly useful in the conversion process:

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

@@sqlstatus:

The status information resulting from the last FETCH statements.

@@tranchained:

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

@@trancount:

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

@@transtate:

The current state of the transaction.

Oracle

SQLCODE:

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

SQL%ROWCOUNT:

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

Recommendations:

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

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

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

Microsoft SQL Server or Sybase Adaptive Server allows DDL constructs to be part of the stored procedures. Oracle allows DDL statements as part of the dynamic SQL. Oracle issues an implicit COMMIT statement after each DDL statement.

SQL Developer currently converts DDL constructs within stored procedures into standalone DDL constructs. The DDL is removed from the body of the stored procedure and is created before the procedure. Support for dynamic SQL is planned for a future release.

Scripting on this page enhances content navigation, but does not change the content in any way.