2 Replies Latest reply: Nov 10, 2005 8:38 AM by 6363 RSS

    Save an oracle package to a flat file

    285785
      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
          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
            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