Migrating Oracle to SQL Server using SSMA - Error O2SS0179 Packaged Variable (constant) data type not recognized
By Bill Ramos and Vishal Soni Advaiya Inc.
[Updated 2/7/2012 Jiafei Yuan - Microsoft SQL Server Migration Assistant (SSMA) for Oracle v5.2. The information provided below is still valid for SSMA for Oracle v5.2. Users should download the lastest SSMA for Oracle]
This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert few of the Oracle data types (like MLSLABEL) when a variable of that type is used in a package.
MLSLABEL data type is used by Trusted Oracle and is used to store the binary format of an operating-system label. Trusted Oracle uses a label to control access to information. The maximum width of an MLSLABEL column is 255 bytes. Any labels that are valid on your operating system can be inserted into an MLSLABEL column. When you insert a label into an MLSLABEL column, Trusted Oracle implicitly converts the data into the binary format of the label.
Error O2SS0179 Packaged Variable (constant) data type not recognized
Background
To store packaged variables, SSMA for Oracle v4.2 [Updated: Please obtain the lastest SSMA for Oracle] uses special procedures (like sysdb.ssma_oracle.set_pv_varchar) that reside in a SysDB database in an ssma_oracle.db_storage table. For more information on how SSMA converts package variables, see the blog post “Emulating Oracle Package Variables on SQL Server with SSMA”.
Whenever SSMA tries to convert unsupported data types it gives an error because SSMA cannot find its corresponding object in the SQL Server.
Possible Remedies
When SSMA tries to convert the following PL/SQL block, it generates the following error:
“Error O2SS0179 Packaged Variable (constant) data type not recognized”
CREATE OR REPLACE package mlslabel_pkg
is
label mlslabel;
end mlslabel_pkg;
…
CREATE OR REPLACE package body mlslabel_pkg
IS
Begin
label := null;
End mlslabel_pkg;
Since the MLSLABEL contains NULL values, you should consider replacing the unsupported data types with any other supported data types (like VARCHAR) as shown below.
CREATE OR REPLACE package mlslabel_pkg
IS
label Varchar(255);
End mlslabel_pkg;
…
CREATE OR REPLACE package body mlslabel_pkg
IS
Begin
label := null;
End mlslabel_pkg;
Related Errors
There are several other errors related to package variables that you may encounter. These include the following:
- O2SS0180: Packaged variable(constant) data type not recognized
- O2SS0350: Conversion of packaged variable(constant) data type is not supported in this release
References
For more information, check out the Migrating Oracle to SQL Server 2008 White Paper and the blog post Migrating Oracle to SQL Server Using SSMA - Emulating Oracle Package Variables.