how to export/import procedures,functions,packages

[pnrdba]

Hi Gurus,

I want to export my procedures,functions,packages only and import them on other box. Can any one help me how to do this? this is most urgent please. My OS is linux 7.1 and oracle9.0.1

Thanks

[Shestakov]

If u have Oracle 9.0.1 u can use (in my mind this way is better) package:
DBMS_METADATA for reingenering store procedures, functions, packages ...

[pnrdba]

Hi Shestakov,
Thanks a lot for your reply. But i've got near about 150 procedures,packages,functions in all. If there are 1 or 2 ..10, i think then we can use this DBMS_METADATA. For this huge numbers, isn't it a cumbersome task. Please advice me.

Thanks

[Shestakov]

How many owners u have.
U can write programm script that generate script for unload store objects like:

spool unload_store.sql

select 'dbms_metadata.....(' ||owner||'.'||object_name||');'
from all_objects where owner = 'UR_OWNER'
and object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNKTION');

spool off;

@unload_store

i spent 2-3 minutes on this example

[jmodic]

If you are going to use DBMS_METADATA.GET_DDL() function you won't be able to get the code of your package bodies, only package specifications. At least I couldn't make it work with OBJECT_TYPE => 'PACKAGE BODY'.

[pnrdba]

Thanks Shestakov,
I used almost the similar query what you gave.
Jmodic,
The object_type 'PACKAGE' will itself take the DDL for Package body also.

Please correct me if i'm wrong shestakov.

Thanks

[Shestakov]

Juriy right. for reingenering package code not needed
use object PACKAGE BODY (only PACKAGE).

SQL> set pages 9999
SQL> set lines 120
SQL> set long 20000
SQL> create or replace package abc is
2 function f(a number) return number;
3 end;
4 /

Package created.

SQL> create or replace package body abc is
2 function f(a number) return number is
3 begin
4 return 1;
5 end;
6 end;
7 /

Package body created.

SQL> select dbms_metadata.get_ddl('PACKAGE', 'ABC', 'TRAIN') dual;

DBMS_METADATA.GET_DDL('PACKAGE','ABC','TRAIN')
---------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE "TRAIN"."ABC" is
function f(a number) return number;
end;
CREATE OR REPLACE PACKAGE BODY "TRAIN"."ABC" is
function f(a number) return number is
begin
return 1;
end;
end;