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)