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.

 

Data type in Oracle	SSMA conversion
Varchar	sysdb.ssma_oracle.set_pv_varchar
Date	sysdb.ssma_oracle.set_pv_datetime2
Char	sysdb.ssma_oracle.set_pv_varchar
Int	sysdb.ssma_oracle.set_pv_float
Float	sysdb.ssma_oracle.set_pv_float

 

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.

Comments

• Anonymous
  November 23, 2011
  Hi, Thanks for useful info. if after initialization there is called some procedure then how sql initialize proc call that. following are the oracle code need to convert

---

CREATE OR REPLACE package log_log_prune as  function days_to_remember_log_data return NUMBER;  max_run_time_in_seconds NUMBER := 50 * 60; -- 50 minutes  max_run_time_in_days NUMBER := max_run_time_in_seconds / (24 * 3600);  -- Main delete procedure.  procedure delete_old_log_data;  -- I am talking about these two procedure which is called here  procedure delete_main_rows; end;

Thanks

• Anonymous
  November 27, 2011
  hi Neeraj Yadav: You provide a package log_log_prune that contains two procedure declaration you are asking about. Note: they are simple procedure declaration, not precedure call. The two procedure will be converted to two separated procedures in target SQL db. So no procedure will be called on that point.