SQL Server Migration Assistant (SSMA) Team's Blog

How-to articles, tips-and-tricks, and guidelines for migrating from Oracle/Sybase/MySQL/Access to SQL Server and SQL Azure

Converting Oracle UDT to SQL Server TVP

Converting Oracle UDT to SQL Server TVP

  • Comments 1

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

  • 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

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post