[OTN] 프로시저, 트리거 소스를 파일로 남기기

출처 :   http://kr.forums.oracle.com/forums/thread.jspa?threadID=477227&tstart=30




제품 : PL/SQL

작성날짜 : 2000-07-26



PROCEDURE, TRIGGER SOURCE을 FILE로 남기기
==============================================================================================



PURPOSE
----------------------------------------------------------------------------------------------

Procdure, Trigger등의 source을 보고자 할 경우와 그 source를 file로 남기기 위한 방법에 대해 설명한다.




Explanation
----------------------------------------------------------------------------------------------

SQLPLUS로 연결하여 procdure, trigger등의 source을 보고자 할 경우에는 <아래 1>과 같이 하였으며
하나의 procedure나 trigger등의 source을 file로 남기고자 할때는 spool 명령으로 간단히 해결할 수 있었다.
그러나 하나 이상일 때는 각각의 file로 남기려면 따로따로 query을 해야 하므로 번거로움을 피하기 위해
utl_file package을 이용하여 한번에 file로 남기는 procedure <아래 2>을 구성하였다.



Example
----------------------------------------------------------------------------------------------

<아래 1>

-- Procedure Source 보기

set pagesize 9999
break on name on type
column name format a10
column type format a10
column text format a58

select name, type, text from user_source order by name, type, line;

select text from user_source
where name = 'function name' and type = 'FUNCTION' order by line;

select text from user_source
where name = 'procedure name' and type = 'PROCEDURE' order by line;


-- Trigger Source보기

set long 4000;
set pagesize 1000;

select description, trigger_body from user_triggers;




<아래 2>

-- Procedure Source보기

create or replace procedure cr_proc_script(p_pn varchar2)
is
file_id utl_file.file_type;
cursor c1(v_pn varchar2) is select name, text, line from user_source
where upper(name) like v_pn
and type = 'PROCEDURE' order by name,line;
v_name varchar2(30) := ' ';
r_name varchar2(30);
r_text varchar2(4000);
r_line number;
v_line number := 0;

begin

open c1(p_pn);
loop
fetch c1 into r_name, r_text, r_line;
exit when c1%notfound;

if r_name != v_name then

file_id :=
utl_file.fopen('/mnt3/rctest80/tool','cr_'||r_name||'.sql', 'w');
utl_file.put(file_id, 'create or replace ');

select max(line) into v_line from user_source
where name = r_name and type = 'PROCEDURE';

end if;

utl_file.put_line(file_id, r_text);
v_name := r_name;

if (r_name = v_name) and (r_line = v_line) then
utl_file.put_line(file_id, '/');
utl_file.fclose(file_id);
end if;

end loop;
close c1;

end;
/

-- TEST001 이라는 procedure의 source을 보고자 할때

SQL> exec cr_proc_script('TEST001');

PL/SQL procedure successfully completed.

$ls -al
-rw-r--r-- 1 rctest80 dba 122 Jul 27 16:58 cr_TEST001.sql



-- user가 가지고 있는 모든 procedure의 source을 보고자 할때

SQL> exec cr_proc_script('%');

PL/SQL procedure successfully completed.

$ls -al
-rw-r--r-- 1 rctest80 dba 1033 Jul 27 16:58 cr_CR_PROC_SCRIPT.sql
-rw-r--r-- 1 rctest80 dba 122 Jul 27 16:58 cr_TEST001.sql






-- Trigger Source보기

create or replace procedure cr_trg_script(p_tn varchar2)
is
file_id utl_file.file_type;
cursor c1(v_tn varchar2) is select trigger_name, description, trigger_body
from user_triggers
where upper(trigger_name) like v_tn ;
r_trg_name varchar2(30);
r_description varchar2(2000);
r_trg_body varchar2(32000);

begin

open c1(p_tn);
loop
fetch c1 into r_trg_name, r_description, r_trg_body;
exit when c1%notfound;

file_id :=
utl_file.fopen('/mnt3/rctest80/tool', 'cr_'||r_trg_name||'.sql', 'w');
utl_file.put(file_id, 'create or replace trigger ');

utl_file.put_line(file_id, r_description);
utl_file.put_line(file_id, r_trg_body );

utl_file.put_line(file_id, '/');
utl_file.fclose(file_id);

end loop;
close c1;

end;
/

-- EMP_BEF_STM_ALL 이라는 trigger의 source을 보고자 할때

SQL> exec cr_trg_script('EMP_BEF_STM_ALL');

PL/SQL procedure successfully completed.

$ls -al
-rw-r--r-- 1 rctest80 dba 143 Jul 27 16:01 cr_EMP_BEF_STM_ALL.sql



-- user가 가지고 있는 모든 trigger의 source을 보고자 할때

SQL> exec cr_trg_script('%');

PL/SQL procedure successfully completed.

$ls -al
-rw-r--r-- 1 rctest80 dba 549 Jul 27 16:03 cr_EMP_AFT_ROW_TRIGGER.sql
-rw-r--r-- 1 rctest80 dba 143 Jul 27 16:01 cr_EMP_BEF_STM_ALL.sql





Reference Ducumment
----------------------------------------------------------------------------------------------

PL/SQL User's Guide