These examples show the DDL that is generated when you
copy PL/SQL procedures 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
PL/SQL procedures from an Oracle Database 11g source database to a DB2® Version 9.7 for Linux, UNIX,
and Windows target database.
Table 1. Examples of copying PL/SQL procedures from Oracle Database
11g to DB2 Version 9.7 for Linux, UNIX, and Windows databaseOracle Database 11g PL/SQL procedure definition |
Generated DDL for DB2 Version 9.7 database |
CREATE OR REPLACE PROCEDURE scott.sample_proc1
AUTHID DEFINER
AS
BEGIN
INSERT INTO detail_proc_insert
VALUES(1,'hello');
END;
/
|
CREATE PROCEDURE scott.sample_proc1
AUTHID DEFINER
AS
BEGIN
INSERT INTO detail_proc_insert
VALUES(1,'hello');
END scott.sample_proc1;
|
CREATE OR REPLACE PROCEDURE scott.sample_proc2
AUTHID DEFINER
AS
x NUMBER(9,2);
BEGIN
x := 65400;
END;
/
|
CREATE PROCEDURE scott.sample_proc2
AUTHID DEFINER
AS
x NUMBER(9,2);
BEGIN
x := 65400;
END scott.sample_proc2;
|
CREATE OR REPLACE PROCEDURE scott.sample_proc3
IS
detected EXCEPTION;
PRAGMA EXCEPTION_INIT(detected, -60);
BEGIN
null; -- some comment
EXCEPTION
WHEN deadlock_detected THEN
null; -- handling error
END;
/
|
CREATE PROCEDURE scott.sample_proc3
IS
detected EXCEPTION;
PRAGMA EXCEPTION_INIT(detected, -60);
BEGIN
null; -- some comment
EXCEPTION
WHEN deadlock_detected THEN
null; -- handling error
END scott.sample_proc3;
|
CREATE OR REPLACE PROCEDURE scott.sample_proc4
IS
num_tables NUMBER;
BEGIN
SELECT COUNT(*)
INTO num_tables
FROM dual;
IF num_tables < 1000 THEN
raise_application_error(-20101,
'Issuing message');
ELSE
NULL; -- comment
END IF;
END;
/
|
CREATE PROCEDURE scott.sample_proc4
IS
num_tables NUMBER;
BEGIN
SELECT COUNT(*)
INTO num_tables
FROM dual;
IF num_tables < 1000 THEN
raise_application_error(-20101, 'Issuing message');
ELSE
NULL; -- comment
END IF;
END scott.sample_proc4;
|
CREATE PROCEDURE scott.sample_proc5
IS
num_tables NUMBER;
dual_rec dual%ROWTYPE;
my_D emp.ename%TYPE;
BEGIN
SELECT COUNT(*) INTO num_tables
FROM dual;
END;
/
|
CREATE PROCEDURE scott.sample_proc5
IS
num_tables NUMBER;
dual_rec dual%ROWTYPE;
my_D emp.ename%TYPE;
BEGIN
SELECT COUNT(*) INTO num_tables
FROM dual;
END scott.sample_proc5;
|
Table 2 shows an example of the DDL that is generated for copying
a PL/SQL procedure from a DB2 Version
9.7 for Linux, UNIX, and Windowssource
database to an Oracle Database 11g target database.
Table 2. Example of copying PL/SQL procedure from DB2 Version 9.7 for Linux, UNIX,
and Windows database to
Oracle Database 11gDB2 Version
9.7 database PL/SQL procedure definition |
Generated DDL for Oracle Database 11g |
CREATE PROCEDURE OM2BSP13 (
v_idno NUMBER,
v_fname VARCHAR2,
v_dept NUMBER,
v_div NUMBER)
AS
BEGIN
INSERT INTO TBOMSP13 VALUES
(v_idno, v_fname, v_dept, v_div);
END;
|
CREATE PROCEDURE OM2BSP13 (
v_idno NUMBER,
v_fname VARCHAR2,
v_dept NUMBER,
v_div NUMBER)
AS
BEGIN
INSERT INTO TBOMSP13 VALUES
(v_idno, v_fname, v_dept, v_div);
END;
|