Skip to Main Content
  • Questions
  • Copy Package to another database with all dependencies

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ram.

Asked: June 15, 2016 - 7:04 am UTC

Last updated: June 15, 2016 - 8:17 am UTC

Version: 4.0.3.16

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am new to oracle and need some suggestions on one of the scenarios.

I need to copy a few packages from one connection to another connection.

Packages are mutually dependent and dependent on other objects.

Example :

PackageA internally calls a procedure from packageB.
PackageB gets data from ViewA.
PackageA refers to SequenceA in the same database.
PackageA updates TableA, and TableA have a trigger which calls PackageC.

Is there any way to copy all dependent objects altogether.

I tried copying them individually but failed, the scenario is much bigger than in example.

Any help is appreciated.

thanks.

and Chris said...

You could use data pump to export/import the objects:

https://oracle-base.com/articles/10g/oracle-data-pump-10g

Or the SQL Developer cart to select which objects you want to copy:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/12c_sqldev/cart_sql_dev/12cCart.html

But I'm guessing the problem is you're struggling to find all the dependencies. You can use the *_dependencies views to find this.

I've modelled something similar to your simplified example (see code at bottom). You can find all the objects PKGA requires with something like:

select distinct referenced_owner, referenced_name, referenced_type
from   all_dependencies u
start  with name = 'PKGA'
and    owner = user
connect by nocycle prior referenced_name = name
           and prior referenced_owner = owner
           and referenced_owner <> 'SYS';

REFERENCED_OWNER               REFERENCED_NAME                REFERENCED_TYPE  
------------------------------ ------------------------------ ------------------
CHRIS                          TB                             TABLE             
CHRIS                          V                              VIEW              
CHRIS                          S                              SEQUENCE          
CHRIS                          TC                             TABLE             
CHRIS                          TA                             TABLE             
CHRIS                          PKGA                           PACKAGE           
CHRIS                          PKGB                           PACKAGE


This finds you all the objects you need to compile PKGA. But not everything you're looking for! PKGA doesn't depend on the trigger. So it's not in the list.

To get this you could go "bottom up", starting with all the tables:

select distinct owner, name, type 
from   all_dependencies
start with referenced_type = 'TABLE'
and   referenced_owner = user
connect by nocycle referenced_name = prior name
and     referenced_owner = prior owner;

OWNER                          NAME                           TYPE             
------------------------------ ------------------------------ ------------------
CHRIS                          V                              VIEW              
CHRIS                          PKGB                           PACKAGE BODY      
CHRIS                          PKGA                           PACKAGE BODY      
CHRIS                          TRIG                           TRIGGER


Now you've got the trigger. But you're still missing PKGC which it requires! So you have to go "top down" from your triggers again...

If you had virtual columns in your tables, any PL/SQL these depended on also wouldn't show.

So unless there's a specific reason you only need to copy some of the objects, you may be better off migrating everything. Also be aware that objects you need may be in another user!

Code for examples:
create table ta (
  x int
);
create table tb (
  x int
);
create table tc (
  x int
);
create or replace view v as 
  select * from tc;
  
create sequence s;

create or replace package pkga as
  procedure p;
end;
/
create or replace package pkgb as
  procedure p;
end;
/
create or replace package pkgc as
  procedure p;
end;
/

create or replace trigger trig 
before insert on ta
begin
  pkgc.p;
end;
/

create or replace package body pkga as
  procedure p is
  begin
    pkgb.p;
    insert into ta
      select s.nextval from tb;
  end;
end;
/
create or replace package body pkgb as
  procedure p is
  begin
    for c in (select * from v) loop
      null;
    end loop;
  end;
end;
/
create or replace package body pkgc as
  procedure p is
  begin
    null;
  end;
end;
/


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.