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.
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.
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; |