1 / 17

Insert Picture Here

Manual conversion required. Complex triggersReferences to data dictionary tables (systables,?)Recent extensions to T-SQL are usually not supported (e.g. XML extensions). Triggers. SQL Server triggers are statement level but give you access to change rows via the inserted and deleted temp tables. I

farsiris
Download Presentation

Insert Picture Here

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    3. Manual conversion required Complex triggers References to data dictionary tables (systables,…) Recent extensions to T-SQL are usually not supported (e.g. XML extensions)

    4. Triggers SQL Server triggers are statement level but give you access to change rows via the inserted and deleted temp tables. In Oracle exist statement / row level triggers Because SS Triggers are not part of a transaction it is common to do a rollback in the trigger, will be replaced by a RAISE_APPLICATION_ERROR by OMWB which will cause Oracle to roll the transaction back. The trigger will be terminated at this point rather than continuing as in TSQL. The trigger model between SQL Server and Oracle is very different.  SQL Server triggers operate outside the scope of the transaction that fires them. SQL Server triggers are statement level but give you access to change rows via the inserted and deleted temporary tables. COLUMNS_UPDATED bitmap is sometimes used in SS to identify columns that have changed. This bitmap does not exist in Oracle. Oracle provides for columns to be listed in the CREATE TRIGGER statement.  The trigger will only fire if one of the columns listed is changed.  Because SS Triggers are not part of a transaction it is common to do a rollback in the trigger. This will be replaced by a RAISE_APPLICATION_ERROR by the workbench which will cause Oracle to roll the transaction back. The trigger will be terminated at this point rather than continuing as in TSQL. The trigger model between SQL Server and Oracle is very different.  SQL Server triggers operate outside the scope of the transaction that fires them. SQL Server triggers are statement level but give you access to change rows via the inserted and deleted temporary tables. COLUMNS_UPDATED bitmap is sometimes used in SS to identify columns that have changed. This bitmap does not exist in Oracle. Oracle provides for columns to be listed in the CREATE TRIGGER statement.  The trigger will only fire if one of the columns listed is changed.  Because SS Triggers are not part of a transaction it is common to do a rollback in the trigger. This will be replaced by a RAISE_APPLICATION_ERROR by the workbench which will cause Oracle to roll the transaction back. The trigger will be terminated at this point rather than continuing as in TSQL.

    5. Error handling IN TSQL errors can be trapped by the programmer via the @@ERROR variable, do not necessarily stop the execution of the stored module. In PL/SQL all errors that occur will stop the execution of the stored module UNLESS they are trapped via a BEGIN...EXCEPTION...END block. In SQL Server RAISERROR does not stop the execution of a stored module. The Oracle equivalent RAISE_APPLICATION_ERROR does. Error handling Error handling between TSQL and PL/SQL is different. Errors that occur in TSQL do not necessarily stop the execution of the stored module. Errors can be trapped by the programmer via the @@ERROR variable. In PL/SQL all errors that occur will stop the execution of the stored module UNLESS they are trapped via a BEGIN...EXCEPTION...END block. With the parser option (see below) Maximal PL/SQL the workbench puts this code around all SQL statements in a stored module. This option produces a lot of code and should only be used where necessary. In SQL Server RAISERROR does not stop the execution of a stored module. The Oracle equivalent RAISE_APPLICATION_ERROR does. If this is an issue some manual coding will be required. 'Raiserror ( num') Manual Conversion Might be Required num gives ID in SQL Server SYSCOMMENTS table and RAISERROR with LOG -This raises an error based on the SYSCOMMENTS table -Replaced by RAISE_APPLICATION_ERROR -Needs re-coding to get error message from a table Error handling Error handling between TSQL and PL/SQL is different. Errors that occur in TSQL do not necessarily stop the execution of the stored module. Errors can be trapped by the programmer via the @@ERROR variable. In PL/SQL all errors that occur will stop the execution of the stored module UNLESS they are trapped via a BEGIN...EXCEPTION...END block. With the parser option (see below) Maximal PL/SQL the workbench puts this code around all SQL statements in a stored module. This option produces a lot of code and should only be used where necessary. In SQL Server RAISERROR does not stop the execution of a stored module. The Oracle equivalent RAISE_APPLICATION_ERROR does. If this is an issue some manual coding will be required. 'Raiserror ( num') Manual Conversion Might be Required num gives ID in SQL Server SYSCOMMENTS table and RAISERROR with LOG -This raises an error based on the SYSCOMMENTS table -Replaced by RAISE_APPLICATION_ERROR -Needs re-coding to get error message from a table

    6. Error Handling Raiserror (num) Manual conversion will be required Num gives ID in SQL Server SYSCOMMENTS table This raises an error based on the SYSCOMMENTS table Replaced by RAISE_APPLICATION_ERROR Needs re-coding to get error message from a table

    7. Temporary Tables TSQL has 2 types of Temporary Table, ones that you can create/drop using DDL and ones that are created on the fly using the #TABLE syntax. Oracle only has 1 type of temporary table, with a global definition (i.e. it is created outside of any stored procedures and is not normally dropped), with temporary data. Any DDL referencing temporary tables is removed by the workbench. The CREATE TABLE statements are used to build global temporary tables which are then used by the PL/SQL modules. Temporary Tables TSQL has 2 types of Temporary Table, ones that you can create/drop using DDL and ones that are created on the fly using the #TABLE syntax. Oracle only has 1 type of temporary table. This has a global definition (i.e. it is created outside of any stored procedures and is not normally dropped), with temporary data. The workbench does a good job of converting TSQL references to temporary tables to their Oracle equivalent. Any DDL referencing temporary tables is removed by the workbench. The CREATE TABLE statements are used to build global temporary tables which are then used by the PL/SQL modules. Temporary Tables TSQL has 2 types of Temporary Table, ones that you can create/drop using DDL and ones that are created on the fly using the #TABLE syntax. Oracle only has 1 type of temporary table. This has a global definition (i.e. it is created outside of any stored procedures and is not normally dropped), with temporary data. The workbench does a good job of converting TSQL references to temporary tables to their Oracle equivalent. Any DDL referencing temporary tables is removed by the workbench. The CREATE TABLE statements are used to build global temporary tables which are then used by the PL/SQL modules.

    8. SET ROWCOUNT – Recoding needed to implement this NOCOUNT – Not needed and ignored DATEFORMAT - Recoding needed if required Transactions BEGIN TRANS Statement Ignored SET TRANSACTION Statement Ignored Transcount functionality is simulated by Workbench so COMMIT only happens if Transcount = 1 Common Workbench Warnings

    9. Global Variables @@FETCH_STATUS – Simulated by workbench @@SQLSTATUS – Simulated by workbench @@ROWCOUNT – Simulated by workbench @@ERROR – Simulated by workbench @@VERSION – Not supported by Oracle @@SPID – Simulated by workbench as UID @@IDENTITY – Simulated by workbench @@SERVERNAME – Not supported by Oracle – Could use V$INSTANCE @@TRANSTATE – Not supported by Oracle @@ - Any variable will be treated as a local variable by the workbench – could use package variable. Common Workbench Warnings

    10. DDL CREATE TABLE STRING Statement Generated – If temporary converted to GLOBAL Temporary Table ALTER TABLE STRING CREATE INDEX STRING Statement Ignored CREATE VIEW STRING Statement Ignored DROP PROCEDURE STRING Statement Ignored DROP TABLE STRING Statement Ignored DROP VIEW STRING Statement Ignored GRANT Statement Ignored Common Workbench Warnings

    11. Cursor Attributes SCROLL – Not yet supported in Oracle PL/SQL INSENSITIVE – Not required FAST_FORWARD – Not required LOCAL – Not Required READ_ONLY – Not Required Common Workbench Warnings

    12. Common Issues COLLATE The workbench does not understand the COLLATE keyword. Each occurrence of the COLLATE keyword must be identified and removed. Once the SP’s have been converted any manual implementation of the COLLATE keyword must be done.

    13. Dynamic Cursors The workbench cannot handle dynamically defined cursors only static ones Selecting from user defined functions The workbench cannot currently handle selecting from user defined functions Common Issues

    14. Use of XML operators FOR XML and OPENXML The workbench partially supports this construct by commenting out the FOR XML keywords. SQL Server XML handling needs manually converting to use Oracle’s XDK/XDB functionality. Common Issues

    15. Case Insensitivity SQL Server can be set up so all queries are case insensitive. Oracle 10g includes a number of Linguistic sorts that allow this functionality for individual columns/queries. < > = queries are easily indexed REGEXP_LIKE can be used in 10g for CI Like functionality but indexing is difficult. Oracle TEXT index can be used if extensive indexing is required. Common Issues

    16. Emulated Functions Migration workbench provides a set up functions to emulate SQL Server equivalents ATN DATE_ LCASE SGN CDATE DAY_ LEFT SHELL CDBL DBNAME LEN SPACE_ CEILING DDL_MANAGER MID SQR CHAR_LENGTH DEBUG_TABLE MONTH_ STR CSTR FIX NOW STRING DATEADD GETDATE OBJECT_NAME SUBSTRING DATEDIFF INSTR_ REPLICATE TIME_ DATESERIAL INT_ REVERSE_ UCASE DATETOSTR ISNULL RIGHT VAL YEAR_

More Related