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