1 / 28

Migrating Stored Procedures and Triggers

Migrating Stored Procedures and Triggers. Objectives. After completing this lesson, you should be able to do the following: Identify PL/SQL objects Compare and contrast SQL Server and Oracle triggers Use OMWB to migrate stored procedures and triggers

egil
Download Presentation

Migrating Stored Procedures and Triggers

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

  1. Migrating Stored Procedures and Triggers

  2. Objectives • After completing this lesson, you should be able to do the following: • Identify PL/SQL objects • Compare and contrast SQL Server and Oracle triggers • Use OMWB to migrate stored procedures and triggers • Identify manual conversion tasks to complete PL/SQL code migration • Identify configuration options that affect PL/SQL performance

  3. Oracle PL/SQL • Procedural Language/Structured Query Language (PL/SQL) is a fourth-generation programming language (4GL). PL/SQL provides: • Procedural extensions to SQL • Exception handling • Portability across platforms and products • Support for object-oriented programming • PL/SQL: • Is similar to SQL Server’s Transact-SQL (T-SQL) • Uses different syntax

  4. PL/SQL Objects • There are many types of PL/SQL database objects: • Procedure • Function • Trigger • Package • Package body • Type body

  5. Procedures • Procedures are used to perform a specific action. Procedures: • Transfer values in and out through an argument list • Are called with the CALLcommand

  6. Functions

  7. Triggers

  8. Comparing Triggers

  9. Packages • Packages are collections of functions and procedures. Each package should consist of two objects: • Package specification • Package body

  10. Package Body

  11. SQL Server: Views Triggers Stored procedures Oracle: Views Triggers Procedures Functions Packages Package body Package type Migrating SQL Server Objects

  12. Migrating T-SQL Objects Workflow 1 Map to PL/SQL objects. 2 Resolve parser errors. Oracle Model Source Model Create objects. 3 Perform manual conversion tasks. 4 Destination database PL/SQL editor

  13. Migration Workbench Parser • The Migration Workbench parser converts T-SQL code to PL/SQL code by emulation. It: • Supports majority of the T-SQL language constructs • Converts most of stored procedures, triggers, and views successfully OMWB parser

  14. Example: SQL Server Stored Procedure SQL> CREATE PROCEDURE dbo.example1 2 AS 3 BEGIN 4 RETURN 1 5 END

  15. Example: Generated Stored Procedure in PL/SQL SQL> CREATE OR REPLACE FUNCTION EXAMPLE1 2 RETURN INTEGER 3 AS 4 StoO_selcnt INTEGER; 5 StoO_error INTEGER; 6 StoO_rowcnt INTEGER; 7 StoO_crowcnt INTEGER := 0; 8 StoO_fetchstatus INTEGER := 0; 9 StoO_errmsg VARCHAR2(255); 10 StoO_sqlstatus INTEGER; 11 BEGIN 12 BEGIN 13 RETURN 1; 14 END; 15 END EXAMPLE1; Function Errorhandling variables Extra BEGINand END block

  16. Resolving Parser Errors • To resolve parser errors, perform the following steps: • Locate the error in the T-SQL code. • Change the Parse Options settings and re-parse the object. • Modify the T-SQL code and re-parse the object.

  17. Step 1: Locating the Error

  18. Step 2: Changing Parse Options

  19. Step 3: Modifying T-SQL Code Example Double quotation marks not recognized by parser

  20. Step 3: Modifying T-SQL Code Insert placeholder. 2 Comment out problematic codewith error notation. 1

  21. Generated PL/SQL Object PL/SQL code for cursor

  22. Migrating T-SQL Objects Workflow 1 Map to PL/SQL objects. 2 Resolve parser errors. Oracle Model Source Model Create objects. 3 Perform manual conversion tasks. 4 Destination database PL/SQL editor

  23. Performing Manual Conversion Tasks • Manual tasks to complete PL/SQL migration : • Modify the PL/SQL source code to ensure the following: • Readability and ease of maintenance • Logically correct • Performance improvements • Verify that the procedures compile and execute.

  24. Common Migration Issues • Common T-SQL to PL/SQL migration issues: • SQL syntax • Dynamic SQL • Transactions handling • Error handling • Returning result sets

  25. Example: Modifying the PL/SQL Code Manually

  26. PL/SQL Configuration Options • There are several PL/SQL compiler settings that control PL/SQL performance. • For the fastest performance, set: • PLSQL_CODE_TYPE=NATIVE • PLSQL_DEBUG=FALSE • PLSQL_OPTIMIZE_LEVEL=2 • PLSQL_WARNINGS=DISABLE:ALL

  27. Summary • In this lesson, you should have learned how to: • Identify PL/SQL objects • Compare and contrast SQL Server and Oracle triggers • Use Migration Workbench to migrate stored procedures and triggers • Identify manual conversion tasks to complete PL/SQL code migration • Identify configuration options that affect PL/SQL performance

  28. Practice Overview:Migrating Stored Procedures and Triggers • This practice covers the following topics: • Using OMWB to convert T-SQL objects to PL/SQL objects • Using OMWB to migrate the remaining schema objects to the Oracle database • Using Enterprise Manager to manually modify PL/SQL code • Verifying that the migrated PL/SQL code compiles and executes

More Related