how to export/import procedures,functions,packages
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to export/import procedures,functions,packages

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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 ...

  3. #3
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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'.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Apr 2002
    Posts
    291
    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
    PNRDBA

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width