Migrating Oracle to SQL Server using SSMA - Error O2SS0260 Wrapped package cannot be converted

By Bill Ramos and Ankit Matta, Advaiya Inc.

This blog post covers the reason why SQL Server Migration Assistant (SSMA) for Oracle does not convert the wrapped code. Wrapping (encrypting) is a process of hiding the source code. Wrapping helps to protect the source code from the competitors and others who might misuse it. In Oracle, one can wrap the source code using DBMS_DDL subprograms.

Error O2SS0260 Wrapped package cannot be converted

Background

Whenever you try to convert the WRAPed (encrypted) PL/SQL code, SSMA gives a warning message saying that the “Required code is wrapped”. When you skip this warning by clicking Continue button, SSMA generates an error message, “Error O2SS0260 Wrapped package cannot be converted”. Also, you will be able to view only the Conversion statistics and not the code in the Assessment Report window, as the code is wrapped.

Possible Remedies

Consider the below example, where we have declared two variables, one to store the Package specification and another to store Package body as strings. These variables are then passed to a predefined stored procedure “DBMS_DDL.CREATE_WRAPPED”, which will create a wrapped package.

DECLARE

  src_pkg_header VARCHAR2(32767);

  src_pkg_body VARCHAR2(32767);

BEGIN

  src_pkg_header

      := 'CREATE OR REPLACE PACKAGE Wrapped_PKG IS '

      || 'x pls_integer; '

      || 'END Wrapped_PKG; ';

  src_pkg_body

      := 'CREATE OR REPLACE PACKAGE BODY Wrapped_PKG IS '

      || 'BEGIN '

      || 'x := 10; '

      || 'END Wrapped_PKG;';

  SYS.DBMS_DDL.CREATE_WRAPPED(ddl => src_pkg_header);

  SYS.DBMS_DDL.CREATE_WRAPPED(ddl => src_pkg_body);

END;

When you execute the above code in Oracle, a package with name “Wrapped_PKG” is created. This package is created in encrypted format, so you cannot view the text/code inside this package.

When you try to convert the package “Wrapped_PKG” code using SSMA, you will get Error O2SS0260.

The solution of the above error can be divided into steps, mentioned below:

Step 1 – Create and execute only the package code in Oracle. Then convert this code into corresponding SQL Server code using SSMA.

CREATE OR REPLACE PACKAGE Wrapped_PKG IS

x pls_integer;

END Wrapped_PKG;

CREATE OR REPLACE PACKAGE BODY Wrapped_PKG IS

BEGIN

x := 10;

END Wrapped_PKG;

Below is the corresponding SQL Server code for the above Package.

CREATE PROCEDURE dbo.WRAPPED_PKG$SSMA_Initialize_Package

AS

   EXECUTE sysdb.ssma_oracle.db_clean_storage

   EXECUTE sysdb.ssma_oracle.set_pv_int

      'PROSEWARE',

      'DBO',

      'WRAPPED_PKG',

      'X',

      10

GO   

Step 2 – Copy this codeand Just add “WITH ENCRYPTION” keyword as highlighted in the code below. This will encrypt your code in SQL Server.

CREATE PROCEDURE dbo.WRAPPED_PKG$SSMA_Initialize_Package

WITH ENCRYPTION

AS

   EXECUTE sysdb.ssma_oracle.db_clean_storage

   EXECUTE sysdb.ssma_oracle.set_pv_int

      'PROSEWARE',

      'DBO',

      'WRAPPED_PKG',

      'X',

      10

GO

For more information regarding the ENCRYPTION clause, please refer to the MSDN help topic – CREATE PROCEDURE (Transact-SQL). In SSMA, when you try to MODIFY the procedure by right clicking the procedure name, the MODIFY option is disabled automatically.

References

For more information, check out the following references:

· Migrating Oracle to SQL Server 2008 White Paper