Script to take backup of Package specification and Package Body

 Script for Package Specification -package_def.sql

COL owner NEW_VALUE owner FORMAT A30;
COL name NEW_VALUE name FORMAT A30;
COL type NEW_VALUE type FORMAT A12;

prompt 
prompt Spool the defintion onto a file
prompt
accept owner prompt 'enter owner ( default - BOOKER ) :- ' default BOOKER
accept obj_name prompt 'enter object name :- '

SET VER OFF FEED OFF;

SELECT owner owner, object_name name, object_type type
  FROM dba_objects
 WHERE owner LIKE RTRIM(UPPER('&&owner'))||'%'
   AND object_name LIKE RTRIM(UPPER('&&obj_name'))||'%'
   AND object_type IN ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
   AND ROWNUM = 1;

PRO
PRO Generating spool file for &&type &&name
PRO

SET TERM OFF PAGES 0 LIN 4000 TRIMS ON;
SPO DEF_&&owner..&&name..SQL;
SELECT DECODE(line,1,'CREATE OR REPLACE ',NULL)||
       text
  FROM dba_source
 WHERE owner = '&&owner'
   AND name = '&&name'
   AND type = '&&type'
 ORDER BY
       line;
PRO /
SPO OFF;
SET TERM ON PAGES 200 LIN 200 VER ON FEED ON DOC ON;
prompt ---------------------------------------------------------------;
prompt See : DEF_&&owner..&&name..SQL
prompt ---------------------------------------------------------------;
prompt
UNDEF 1 name type;
CLE COL;


Script for Package Body  -package_body.sql

COL owner NEW_VALUE owner FORMAT A30;
COL name NEW_VALUE name FORMAT A30;
COL type NEW_VALUE type FORMAT A12;

prompt 
prompt Spool the defintion onto a file
prompt
accept owner prompt 'enter owner ( default - TIMEINC ) :- ' default TIMEINC
accept obj_name prompt 'enter object name :- '

SET VER OFF FEED OFF;

SELECT owner owner, object_name name, object_type type
  FROM dba_objects
 WHERE owner LIKE RTRIM(UPPER('&&owner'))||'%'
   AND object_name LIKE RTRIM(UPPER('&&obj_name'))||'%'
   AND object_type IN ('PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
   AND ROWNUM = 1;

PRO
PRO Generating spool file for &&type &&name
PRO

SET TERM OFF PAGES 0 LIN 4000 TRIMS ON;
SPO BODY_&&owner..&&name..SQL;
SELECT DECODE(line,1,'CREATE OR REPLACE ',NULL)||
       text
  FROM dba_source
 WHERE owner = '&&owner'
   AND name = '&&name'
   AND type = '&&type'
 ORDER BY
       line;
PRO /
SPO OFF;
SET TERM ON PAGES 200 LIN 200 VER ON FEED ON DOC ON;
prompt ---------------------------------------------------------------;
prompt See : BODY_&&owner..&&name..SQL
prompt ---------------------------------------------------------------;
prompt
UNDEF 1 name type;
CLE COL;