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;
