How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server?

Question

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.

Answer 1

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)