Converting Oracle UDT to SQL Server TVP

Oracle supports User Defined Type (UDT) which include object type and collection. This data type is not supported in SQL Server and SSMA does not support conversion of Oracle UDT. You may consider using SQL Server TVP when migrating your Oracle database to SQL Server.

The following provides use scenarios of UDT and examples on how to recreate the statements in SQL Server:

PL/SQL user defined type is converted to user defined table type

PL/SQL	T-SQL
CREATE TYPE person_ot AS OBJECT (    firstname VARCHAR(100),    lastname VARCHAR(100),    hiredate DATE );	CREATE TYPE person_ot AS TABLE (    rowid uniqueidentifier DEFAULT NEWID(),    firstname VARCHAR(100),    lastname VARCHAR(100),    hiredate DATETIME2,    PRIMARY KEY (rowid) )
CREATE TYPE productcategory_ot AS OBJECT (    category VARCHAR2(50),    owner person_ot );	CREATE TYPE productcategory_ot AS TABLE (    rowid uniqueidentifier DEFAULT NEWID(),    category VARCHAR(50),    owner uniqueidentifier )

Oracle variable declared as user defined type is converted to sql server variable of user defined table type

PL/SQL	T-SQL
DECLARE person_var person_ot;	DECLARE @person_var person_ot

Input argument as user defined type will be converted to sql server table value parameter (TVP)

PL/SQL	T-SQL
CREATE PROCEDURE showname(person_in IN person_ot, fullname OUT VARCHAR2) IS BEGIN    Fullname := person_in.firstname  || ' '                person_in.lastname; END;	CREATE PROCEDURE showname(@person_in person_ot READONLY, @fullname VARCHAR(200)) AS SELECT @fullname = firstname + ' ' + lastname FROM @person_in;

Output argument as user defined type is converted to retuned data set 

PL/SQL

T-SQL

CREATE OR REPLACE PROCEDURE createperson (firstname IN VARCHAR2, lastname in VARCHAR2, person_out OUT person_ot) IS BEGIN   person_out := person_ot(firstname, lastname, SYSDATE); END;   -- sample statement to use the stored procedure DECLARE    person person_ot; BEGIN    createperson ('fname', 'lname', person);    DBMS_OUTPUT.PUT_LINE(person.firstname); END;

CREATE PROCEDURE createperson (@firstname VARCHAR(100), @lastname VARCHAR(100)) AS DECLARE @person_out person_ot INSERT INTO @person_out (firstname, lastname, hiredate) VALUES (@firstname, @lastname, GETDATE()) -- return the object type output as result set SELECT * FROM @person_out GO -- sample statement to use the stored procedure DECLARE @person person_ot INSERT INTO @person EXECUTE createperson 'fname','lname' SELECT firstname FROM @person

Object table is created out of schema definition of the user defined table type 

PL/SQL	T-SQL
CREATE TABLE obtblperson OF person_ot;	DECLARE @person_ot person_ot SELECT * INTO obtblperson FROM @person_ot

Oracle table column with user defined type is converted into a seperate table. For object type, the main table column is converted into uniqueidentifier column with foreign key relationship to the sub table. For collection, the sub table is created with a foreign key column referring to the primary key of the main table.

PL/SQL	T-SQL
CREATE TABLE tblemployee_ot (    id NUMBER,    employee person_ot,    role VARCHAR2(100),    CONSTRAINT tblemployee_ot_pk PRIMARY KEY (id) );	CREATE TABLE tblemployee_ot (    id INT,    employee uniqueidentifier,    role VARCHAR(100),    CONSTRAINT tblemployee_ot_pk PRIMARY KEY (id) ); CREATE TABLE tblemployee_ot$employee (    rowid uniqueidentifier DEFAULT NEWSEQUENTIALID(),    firstname VARCHAR(100),    lastname VARCHAR(100),    hiredate VARCHAR(20),    PRIMARY KEY (rowid) ) ALTER TABLE tblemployee_ot ADD CONSTRAINT fk_employee FOREIGN KEY (employee) REFERENCES t85575343$employee (rowid)
CREATE TABLE tblemployee_nt (    id NUMBER,    employee person_nt,    role VARCHAR2(100),    CONSTRAINT tblemployee_nt_pk PRIMARY KEY (id) ); ALTER TABLE tblemployee_ot ADD CONSTRAINT fk_employee FOREIGN KEY (employee) REFERENCES t85575343$employee (rowid)	CREATE TABLE tblemployee_nt (    id INT,    role VARCHAR(100),    CONSTRAINT tblemployee_nt_pk PRIMARY KEY (id) ); CREATE TABLE tblemployee_nt$employee (    rowid uniqueidentifier DEFAULT NEWSEQUENTIALID(),    firstname VARCHAR(100),    lastname VARCHAR(100),    hiredate VARCHAR(20),    employee$id INT REFERENCES tblemployee_nt (id),    PRIMARY KEY (rowid) )

Member method is converted into procedure or function

PL/SQL

T-SQL

CREATE OR REPLACE TYPE BODY person_ot AS    MEMBER PROCEDURE update_hiredate (SELF IN OUT NOCOPY person_ot) IS    BEGIN      SELF.hiredate := SYSDATE;    END; END; -- sample statement using the type member method DECLARE    person person_ot; BEGIN    person := person_ot('fname','lname',NULL);    DBMS_OUTPUT.PUT_LINE('hiredate: ' || to_char(person.hiredate));    person.update_hiredate;    DBMS_OUTPUT.PUT_LINE('hiredate: ' || to_char(person.hiredate)); END;

ALTER TYPE person_ot ADD MEMBER PROCEDURE update_hiredate (SELF IN OUT NOCOPY person_ot); CREATE PROCEDURE person_ot$proc_update_hiredate (@person_ot person_OT READONLY) AS DECLARE @person_ot_out person_ot INSERT INTO @person_ot_out SELECT * FROM @person_ot UPDATE @person_ot_out SET hiredate = getdate() SELECT * FROM @person_ot_out GO -- sample statement to use the stored procedure DECLARE   @person person_ot; BEGIN   INSERT INTO @person (firstname, lastname, hiredate)      VALUES ('fname','lname', NULL)   SELECT 'hiredate: ' + hiredate FROM @person   DECLARE @person_ot$proc_update_hiredate person_ot   INSERT INTO @person_ot$proc_update_hiredate   EXECUTE person_ot$proc_update_hiredate @person   UPDATE old SET old.hiredate = new.hiredate   FROM @person old   JOIN @person_ot$proc_update_hiredate new on old.rowid = new.rowid   SELECT 'hiredate: ' + hiredate FROM @person END

Constructor method is converted into procedure

PL/SQL	T-SQL
CONSTRUCTOR FUNCTION person_ot (    firstname IN VARCHAR2,    lastname IN VARCHAR2 ) RETURN SELF AS RESULT IS BEGIN   SELF.firstname := firstname;   SELF.lastname := lastname;   SELF.hiredate := SYSDATE; END;	CREATE PROCEDURE person_ot$constructor (@firstname VARCHAR(100), @lastname VARCHAR(100)) DECLARE @self person_ot INSERT INTO @self (firstname, lastname, hiredate) VALUES (@firstname, @lastname, GETDATE()) SELECT * FROM @self; GO -- sample statement to use the stored procedure DECLARE @person person_ot INSERT INTO @person EXECUTE person_ot$constructor 'Anton', 'Okrut' SELECT * FROM @person

Comments

• Anonymous
  June 04, 2011
  is there any option to get oracle udt's through linked server ? i got the linked servers working but it shows only tables and views.. what about the rest of the object such as UDT's , SP's an so on ? tzvi.kaidanov@gmail.com