Examples of copying user-defined types

These examples show the DDL that is generated when you copy user-defined types (UDTs) between heterogeneous databases.

The generated DDL is shown on the Preview DDL page in the Paste Database Objects wizard.

Table 1 shows examples of the DDL that is generated for copying UDTs from an Oracle Database 11g source database to a DB2® for Linux, UNIX, and Windows, Version 9.7, Fix Pack 1 target database.

Table 1. Examples of copying UDTs from Oracle Database 11g to DB2 for Linux, UNIX, and Windows, Version 9.7, Fix Pack 1 database
Oracle Database 11g UDT definition Generated DDL for DB2 Version 9.7, Fix Pack 1 database
CREATE TYPE phone_list AS VARRAY(10) OF NUMBER
CREATE TYPE PHONE_LIST AS NUMBER ARRAY[10];
Using varrays within a stored procedure:
CREATE OR REPLACE PROCEDURE proc1
AS
   phonenum phone_list;
BEGIN
   SELECT col1 bulk
   COLLECT INTO phonenum
   FROM tab1;
END;
/
Using varrays within a stored procedure:
CREATE OR REPLACE PROCEDURE PROC1
AS
   PHONENUM PHONE_LIST;
BEGIN
   SELECT COL1 BULK
   COLLECT INTO PHONENUM
    FROM TAB1;
END;
/
Using varrays as IN parameters in a stored procedure:
CREATE OR REPLCE PROCEDURE proc1(phonenums IN phone_list)
AS
   phonenum phone_list;
BEGIN
   SELECT col1 bulk
   COLLECT INTO phonenum
   FROM tab1;
END;
/
Using varrays as IN parameters in a stored procedure:
CREATE OR REPLACE PROCEDURE PROC1(PHONENUMS IN PHONE_LIST)
AS
   PHONENUM PHONE_LIST;
BEGIN
   SELECT COL1 BULK
   COLLECT INTO PHONENUM
   FROM TAB1;
END;
/
Using varrays as OUT parameters in a stored procedure:
CREATE OR REPLACE PROCEDURE proc1(phonenums OUT phone_list)
AS
BEGIN
   SELECT col1 bulk
   COLLECT INTO phonenum
   FROM tab1;
END;
/
Using varrays as OUT parameters in stored procedure:
CREATE OR REPLACE PROCEDURE PROC1(PHONENUMS OUT PHONE_LIST)
AS
BEGIN
   SELECT COL1 BULK
   COLLECT INTO PHONENUM
   FROM TAB1;
END;
/

Table 2 shows an example of the DDL that is generated for copying a UDT from a DB2 for Linux, UNIX, and Windows, Version 9.7, Fix Pack 1 source database to an Oracle Database 11g target database.

Table 2. Example of copying a UDT from DB2 for Linux, UNIX, and Windows, Version 9.7, Fix Pack 1 database to Oracle Database 11g
DB2 Version 9.7, Fix Pack 1 database UDT definition Generated DDL for Oracle Database 11g
CREATE TYPE simpleArray AS INTEGER ARRAY[10];
CREATE TYPE SIKPLEARRAY AS VARRAY(10) OF INTEGER;

Feedback