SET DOCUMENT OFF

-------------------------------------------------------------------

-- @name: object_capture

-- @since: Dec.20.2010

-- @author: @tohappy

-- @description: user_source source 에서 spool 처리

--    @object_backup sp_name

-- @bugs : 줄바꿈 오류, 복잡한 유형테스트 x

-------------------------------------------------------------------

SET RECSEP OFF

SET VERIFY OFF

SET LINESIZE 200

SET PAGESIZE 1000

SET LONG 30

SET TIME OFF

SET TIMING OFF

SET HEADING OFF

SET FEEDBACK OFF

SET ECHO OFF

ACCEPT object_name PROMPT  'Input object_name : '

PROMPT =====================================

PROMPT ===== Object  Capture To File =====

PROMPT =====================================

SPOOL tmp.sql

 SELECT CASE

           WHEN ROWNUM=1 THEN 'CREATE OR REPLACE '||TEXT

           WHEN ROWNUM=B.MROW THEN TEXT||'

/ '

           WHEN  INSTR(TEXT, 'PACKAGE BODY') = 1 THEN   '/

            CREATE OR REPLACE '||TEXT

           ELSE TEXT

       END AS TEXT2

 FROM USER_SOURCE a, (SELECT MAX(ROWNUM) MROW FROM USER_SOURCE IA WHERE NAME=UPPER('&&object_name') ) b

WHERE NAME=UPPER('&&object_name');

SPOOL OFF

-- 아래는 옵션

/*

ho DATE=`date +%Y%m%d_%H%M`; mv tmp.sql /oracle/DBA/job/object_backup/'&&object_name'_${DATE}.sql

PROMPT =====================================

PROMPT &&object_name is spool out list

PROMPT =====================================

ho ls -al /oracle/DBA/job/object_backup/ | grep &&object_name

CLEAR BREAK

CLEAR COMPUTE

*/