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 databaseOracle 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 11gDB2 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;
|