Migrating Oracle to SQL Server Using SSMA - Emulating Oracle Package Variables

By Bill Ramos and Vishal Soni, Advaiya Inc.

Oracle supports encapsulating variables, types, stored procedures, and functions into a package. When you convert Oracle packages, you need to convert:

• Procedures and functions - both public and private
• Variables
• Cursors
• Initialization routines

This blog describes how SQL Server Migration Assistant (SSMA) for Oracle converts package variables to SQL Server.

Converting Package Variables 

To store package variables, SSMA for Oracle uses stored procedures that reside in a SysDB database along with the ssma_oracle.db_storage table. This table is filtered by SPID (session identifier) and login time. This filtering enables you to distinguish between variables of different sessions.

Few of the special procedures are mentioned below:

· sysdb.ssma_oracle.set_pv_varchar

· sysdb.ssma_oracle.set_pv_datetime2

· sysdb.ssma_oracle.set_pv_float

At the beginning of each procedure SSMA places a call to the sysdb.ssma_oracle.db_check_init_package procedure, which checks if the package is initialized and will initialize it if needed. Each initialization procedure cleans the storage table and sets default values for each package variable.

Consider the following example for converting several package variables.

CREATE OR REPLACE PACKAGE MY_PACKAGE

IS

 space varchar(1) := ' ';

 unitname varchar(128) := 'My Simple Package';

 curd date := sysdate;

END;

The SSMA converts it into the following Transact-SQL code:

CREATE PROCEDURE dbo.MY_PACKAGE$SSMA_Initialize_Package

AS

   EXECUTE sysdb.ssma_oracle.db_clean_storage

   EXECUTE sysdb.ssma_oracle.set_pv_varchar

      'PROSEWARE',

      'DBO',

      'MY_PACKAGE',

      'SPACE',

      ' '

   EXECUTE sysdb.ssma_oracle.set_pv_varchar

      'PROSEWARE',

      'DBO',

      'MY_PACKAGE',

      'UNITNAME',

      'My Simple Package'

   DECLARE

      @temp datetime2

   SET @temp = sysdatetime()

   EXECUTE sysdb.ssma_oracle.set_pv_datetime2

      'PROSEWARE',

      'DBO',

      'MY_PACKAGE',

      'CURD',

      @temp

Emulating Get and Set Package Variable Methods with SSMA

Oracle uses ‘Get’ and ‘Set’ methods for the package variables, to avoid letting other subprograms read and write them directly. If there is a requirement to keep some variables available between subprogram calls in the same session, these variables are treated like global variables.

To overcome this scoping rule, SSMA for Oracle uses stored procedures like sysdb.ssma_oracle.set_pv_varchar for each variable type. For accessing these variables, SSMA uses a set of transaction-independent GET and SET procedures and functions.

To distinguish between variables from different sessions, SSMA stores the variables along with their SPID (session identifier) and the session’s login time. Thus the GET and SET procedures keeps the variables independent from the sessions running them. 

References

For more information, check out the Migrating Oracle to SQL Server 2008 White Paper.

For Oracle Package information, refer to the Oracle Database PL/SQL User’s Guide and Reference – Using PL/SQL Packages.