Migrating Oracle to SQL Server using SSMA - Error O2SS0350 Conversion of packaged variable (constant) data type is not supported in this release
By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the PL/SQL block when package variable holds an unsupported datatype such as INTERVAL datatype.
In Oracle INTERVAL datatype defines a period of time that specifies the difference in terms of years and months, or in terms of days, hours, minutes, and seconds.
Error O2SS0350 Conversion of packaged variable (constant) data type is not supported in this release
Background
Whenever package variable holds an INTERVAL data type or any other datatype that is unsupported by SSMA, it generate following error: ‘Error O2SS0350 Conversion of packaged variable (constant) data type is not supported in this release’
Possible Remedies
Consider the example below, where we have created a package that contains an INTERVAL data type.
CREATE OR REPLACE PACKAGE PKG_VAR_TEST IS
start_d date;
end_d date;
v_interval INTERVAL YEAR(1) TO MONTH;
i_year NUMBER;
i_month NUMBER;
END PKG_VAR_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_VAR_TEST is
Begin
start_d := to_date('12-sep-2004', 'dd-mon-yyyy');
end_d := to_date('12-sep-2009', 'dd-mon-yyyy');
v_interval := (end_d - start_d) YEAR TO MONTH;
i_year := extract(YEAR FROM v_interval);
i_month := extract(MONTH FROM v_interval);
dbms_output.put_line('year: ' || i_year);
dbms_output.put_line('month: ' || i_month);
END PKG_VAR_TEST;
/
When SSMA tries to convert the above code, it generates following error: ‘Error O2SS0350 Conversion of packaged variable (constant) data type is not supported in this release’.
The solution of the above problem error is to use ‘DATEDIFF’ function in SQL Server. ‘DATEDIFF’ function returns the number of date and time boundaries crossed between two specified dates. For this, we need to update the SQL Server code as follows:
CREATE PROCEDURE dbo.PKG_VAR_TEST$SSMA_Initialize_Package_new
AS
EXECUTE sysdb.ssma_oracle.db_clean_storage
DECLARE
@temp datetime2
SET @temp = sysdb.ssma_oracle.to_date2('12-05-2004', 'dd-mm-yyyy')
EXECUTE sysdb.ssma_oracle.set_pv_datetime2
'PROSEWARE',
'DBO',
'PKG_VAR_TEST',
'START_D',
@temp
DECLARE
@temp$2 datetime2
SET @temp$2 = sysdb.ssma_oracle.to_date2('01-09-2009', 'dd-mm-yyyy')
EXECUTE sysdb.ssma_oracle.set_pv_datetime2
'PROSEWARE',
'DBO',
'PKG_VAR_TEST',
'END_D',
@temp$2
PRINT( ( right('00' +
rtrim(ltrim(str(DATEDIFF(yy,@temp, @temp$2)))),2) + ':' +
right('00' +
rtrim(ltrim(str(DATEDIFF(MM,@temp, @temp$2)%12))),2)))
GO
Exec dbo.PKG_VAR_TEST$SSMA_Initialize_Package_new;
References
For more information, check out the following references: