Examples of copying PL/SQL packages

These examples show the DDL that is generated when you copy PL/SQL packages 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 packages 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 packages from Oracle Database 11g to DB2 Version 9.7 for Linux, UNIX, and Windows database
Oracle Database 11g PL/SQL package definition Generated DDL for DB2 Version 9.7 database
CREATE PACKAGE scott.trans_data AS
-- package with no body
   minimum_balance   CONSTANT REAL := 10.00;
   number_processed   INT;
   insufficient_funds EXCEPTION;
END trans_data;
/
CREATE PACKAGE scott.trans_data AS
-- package with no body
   minimum_balance   CONSTANT REAL := 10.00;
   number_processed   INT;
   insufficient_funds EXCEPTION;
END trans_data;
CREATE OR REPLACE PACKAGE test_pack1 AS
   FUNCTION create_dept
     (department_id NUMBER,
      location_id NUMBER)
      RETURN NUMBER;
END test_pack1;
/

CREATE OR REPLACE PACKAGE BODY test_pack1 AS
   tot_emps NUMBER;
   tot_depts NUMBER;
   FUNCTION create_dept
     (department_id NUMBER,
      location_id NUMBER)
      RETURN NUMBER IS
         new_deptno NUMBER;
      BEGIN
         INSERT INTO nums1 VALUES (1);
         RETURN(new_deptno);
      END create_dept;

BEGIN
   tot_emps := 0;
END test_pack1;
/
CREATE PACKAGE test_pack1 AS
   FUNCTION create_dept
     (department_id NUMBER,
      location_id NUMBER)
      RETURN NUMBER;
END test_pack1;
/

CREATE PACKAGE BODY test_pack1 AS
   tot_emps NUMBER;
   tot_depts NUMBER;
   FUNCTION create_dept
     (department_id NUMBER,
      location_id NUMBER)
      RETURN NUMBER IS
         new_deptno NUMBER;
      BEGIN
         INSERT INTO nums1 VALUES (1);
         RETURN(new_deptno);
      END create_dept;

BEGIN
   tot_emps := 0;
END test_pack1;

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

Table 2. Example of copying PL/SQL package from DB2 Version 9.7 for Linux, UNIX, and Windows database to Oracle Database 11g
DB2 Version 9.7 database PL/SQL package definition Generated DDL for Oracle Database 11g
CREATE OR REPLACE PACKAGE ARITHMETIC AS
   function add (
      first number,
      second number)
   return number;
END ARITHMETIC;


CREATE OR REPLACE PACKAGE BODY ARITHMETIC AS
  function add(
      first number,
      second number)
   return number AS

  BEGIN
   return first + second;
  END add;
END ARITHMETIC;
CREATE OR REPLACE PACKAGE ARITHMETIC AS
   function add (
      first number,
      second number)
   return number;
END ARITHMETIC;


CREATE OR REPLACE PACKAGE BODY ARITHMETIC AS
  function add(
      first number,
      second number)
   return number AS

  BEGIN
   return first + second;
  END add;
END ARITHMETIC;

Feedback