Oracle Objects and PL/SQL in SQL Server

As a continuation of one of my primary themes at the MSSQLDUDE blog here, I would like to provide some more info for those Oracle DBAs and developers out there reading this. In this instance today, I’ll touch a little bit on a few things that you should be aware of when converting from Oracle databases to SQL Server. Keeping in mind that at its core, both are indeed relational databases and have grown up together. When Microsoft or Oracle adds capabilities to their database product, you can be the other will do the same shorlty thereafter.

There are 2 primary links that I would like to point you toward to kick things off: the first one here is for the SQL Server Migration Assistant (SSMA) tool which is a free download from Microsoft. It is a great tool and has been around for quite awhile. The original makers of that tool, DB Best, are excellent Microsoft partners and the IP was sold to Microsoft years ago and is now available for you as a free download to convert Oracle PL/SQL, objects and data into T-SQL and SQL Server. It does many other things as well such as estimate what will or won’t automatically convert, how much effort is needed to complete the migration and many options, too many to note here, that you can configure to control how similar, but not identical, features will convert from Oracle to SQL Server.

The other link here is to a 160+ page document from the DB Best team and is an invaluable tool to have in your toolbox in addition to the SSMA tool for your migration projects. Of course, the Help documentation that is part of the SSMA is also useful, including details on how to use the data testing capabilities in SSMA which are invaluable before you decide to enact the code & object changes on the target SQL Server schema. Both the free guide and free tool, though, I use quite a bit on these projects.

Here, then are a few highlights that I would like to point out for both DBAs and developers to keep in mind when converting from Oracle to SQL Server. The links above are the tools and documents that list the full set that you should be aware of when embarking on an Oracle migration project. But I run into these a lot and picked out a few for you as gotchas and that should be of interest to you:

1. Oracle Packages – To emulate Oracle’s “packages”, which allow Oracle developers to package-up Oracle database objects like variables, functions, procedures, etc, you need to place those objects into the SQL Server “programmability” category separately in SQL Server. The SSMA tool and DB Best documents use naming conventions and schema names to categorize the package contents.

2. Oracle Sequences – A long-time favorite of database pros has been arguing over table-agnostic sequences vs. SQL Server/Sybase style identity columns (look for sequences in SQL 11!). To convert from Oracle sequences to SQL Server, you can either convert to identity columns in your target SQL Server tables or use the SSMA option which generates a SQL Server stored proc to emulate the Oracle sequence NEXTVAL and CURRVAL functionality is in this example from the DB Best doc:

Oracle

INSERT INTO employees (id, name)

VALUES(employees_seq.nextval, ‘David Miller’);

Transact-SQL

DECLARE @nextval numeric(38, 0)

EXECUTE sysdb.ssma_oracle.db_sp_get_next_sequence_value ‘customers’,’dbo’,’employees_seq’, @nextval OUTPUT

INSERT employees (id, name) VALUES(@nextval, ‘David Miller’)

3. CURSORS – The dreaded cursor. This is where my earlier posts here @ my blog around the evil cusror originated. I used to cursors a LOT when I was an Oracle developer. And I ran several Oracle-to-SQL migration projects which lead to quite a bit a dealing with cursors. T-SQL does not require the use of cursors the way that PL/SQL procedure do. And SQL Server T-SQL does not optimize cursors very well or in the same manner that sets returned from selects are optimized. Therfore, if you can, rewrite cursors into new T-SQL statements. But if you cannot and want to convert, use SSMA to convert the PL/SQL code to T-SQL and then consult the document I’ve reference in this posting about how to emulate Oracle’s cursor arguments and remember that SQL Server supports different types of cursors such as FORWARD, READ ONLY, etc., and go back and understand which one is best for each use of the cursor to ensure optimal performance of the cursor T-SQL code.

Advertisements

One response to “Oracle Objects and PL/SQL in SQL Server

  1. Pingback: Oracle Objects and PL/SQL in SQL Server « Blog Home for MSSQLDUDE - sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s