This discussion is archived
2 Replies Latest reply: Nov 10, 2005 6:38 AM by 6363 RSS

Save an oracle package to a flat file

285785 Newbie
Currently Being Moderated
Hallo,

how can I save an oracle package to a flat file?


Best Regards
Werner
  • 1. Re: Save an oracle package to a flat file
    466941 Newbie
    Currently Being Moderated
    SPOOL dbms_sql.pkg

    BEGIN
    for rec in (select text from ALL_source where name like 'DBMS_SQL') loop
    DBMS_OUTPUT.PUT_LINE(rec.text);
    end loop;
    END;
    /

    spool off

    Hope it will help...
  • 2. Re: Save an oracle package to a flat file
    6363 Guru
    Currently Being Moderated
    Here is a script you can use. You may need to change the references to dba_source to user_source or all_source depending on your access privileges.
    set termout off
    store set sqlplus.cfg replace
    column pkg_spec new_value pkg_spec noprint
    column pkg_body new_value pkg_body noprint
    set pagesize 0
    set linesize 10000
    set trimspool on
    set echo off
    set verify off
    set feedback off
    set timing off
    set recsep off
    set termout on
    accept pkg prompt   'Package name:  '
    accept owner prompt 'Owner:         '
    set termout off

    select lower('&&pkg') || '_spec.sql' pkg_spec,
      lower('&&pkg') || '_body.sql' pkg_body from dual;

    spool &pkg_spec

    select 'create or replace' from dual;

    select text from dba_source
      where owner = upper('&&owner')
      and name = upper('&&pkg')
      and type = 'PACKAGE'
      order by line;

    select '/' || chr(10) || 'sho err' from dual;

    spool off

    spool &pkg_body

    select 'create or replace' from dual;

    select text from dba_source
      where owner = upper('&&owner')
      and name = upper('&&pkg')
      and type = 'PACKAGE BODY'
      order by line;

    select '/' || chr(10) || 'sho err' from dual;

    spool off

    @sqlplus.cfg
    set termout on