Sql-server – How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server

migrationoracle-11g-r2sql serversql-server-2008

I want to recreate the table below in SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it.

create Table Listing
(
 NAME                    VARCHAR2(30), 
 VALUES                  NUMBER,       
 LIST_OF_VALUES          LOV,          
 id                      VARCHAR2(30) 
);

I am new at trying to converting this type of data or data type, in general, your help would be appreciated.

Best Answer

User Defined Types (UDT) in Oracle

Many 3rd party software cannot handle UDTs. These include Oracle APEX, and SSIS.

This is only one, of many, problems with using a UDT for a Column's data type.

MS SQL

I don't know if MS-SQL has the same problem with UDTs. I'm guessing "yes".

I'm guessing LOV is a TABLE OF ... data type. If so, create a child table of the type's attributes along with a foreign key to the Listing table.

SSIS

To enable SSIS to see the data, I recommend you CREATE VIEW.

create or replace
view Listing_LOVs
as
select a.id, b.attribute_1, b.attribute_2, ...
from Listing a, a.List_of_values b;

Then:

  • Transfer all Listing columns (except List_of_Values) to the MS-SQL Listing table.
  • Transfer all the Listing_LOVs values the the child table.

XML Version

Per Laughing Vergil's comment: XML Document could be another method.

create or replace
view Listing_VIEW
as
select a.id, a.values, a.id
  ,SYS_XMLGEN( a.List_of_values).GetCLOBVal() List_of_Values_XML
from Listing a;

The column List_of_Values_XML will appear as a CLOB to 3rd party apps (eg SSIS)

Related Question