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’.

 

clip_image001

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:

·         Migrating Oracle to SQL Server 2008 White Paper