1

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.

  • 1
    SQL Server does not have an innate list, array, or similar data type. For SQL Server 2008, you can use an XML data type and have an XML document of the items included, or a second table with an ID / Sequence / Value, where ID is the ID from your base table and sequence is optional in case the order of the elements matters, or a string with a delimited list with a function to parse it out into a values table. – Laughing Vergil Jan 30 at 17:45
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.