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.
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.
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.
:= '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;';
SYS.DBMS_DDL.CREATE_WRAPPED(ddl => src_pkg_header);
SYS.DBMS_DDL.CREATE_WRAPPED(ddl => src_pkg_body);
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
CREATE OR REPLACE PACKAGE BODY Wrapped_PKG IS
x := 10;
Below is the corresponding SQL Server code for the above Package.
CREATE PROCEDURE dbo.WRAPPED_PKG$SSMA_Initialize_Package
Step 2 – Copy this code and Just add “WITH ENCRYPTION” keyword as highlighted in the code below. This will encrypt your code in SQL Server.
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.
For more information, check out the following references:
· Migrating Oracle to SQL Server 2008 White Paper