105 - [Oracle PL/SQL] Managing Code - dbms_ddl

 

 

• 일반적인 컴파일 방법

create or replace function get_sum_sal_dept( dept_id number )  
    return number
is
    v_sal number;
begin
    select sum(salary)
    into v_sal
    from
    employees
    where department_id =dept_id;
    return v_sal;
    
    exception 
    when others then return null;
end;

 

 

 

• 함수 테스트 및 소스 보기

select get_sum_sal_dept(90) from dual;
/*
GET_SUM_SAL_DEPT(90)
--------------------
               58160
*/


--소스코드읽기
select line,text from user_source
where lower(name)='get_sum_sal_dept';
/*
      LINE TEXT                                                                                                
---------- ---------------------------------------------------------
         1 function get_sum_sal_dept( dept_id number )                                                         
         2     return number                                                                                   
         3 is                                                                                                  
         4     v_sal number;                                                                                   
         5 begin                                                                                               
         6     select sum(salary)                                                                              
         7     into v_sal                                                                                      
         8     from                                                                                            
         9     employees                                                                                       
        10     where department_id =dept_id;                                                                   
        11     return v_sal;                                                                                   
        12                                                                                                     
        13     exception                                                                                       
        14     when others then return null;                                                                   
        15 end;                                                                                                
15 rows selected. 
*/

 

 

 

• 소스코드 암호화

--dbms_ddl.create_wrapped 을 이용한 소스코드 암호화

begin
dbms_ddl.create_wrapped
(
      'create or replace function get_sum_sal_dept
      ( dept_id number )  
      return number
      is
      v_sal number;
      begin
        select sum(salary)
        into v_sal
        from
        employees
        where department_id =dept_id;
        return v_sal;
       
       exception 
       when others then return null;
      end;'
);
end;

 

 

 

• 테스트 결과 동일

--동작에는 이상이 없음 확인,
select get_sum_sal_dept(90) from dual;
/*
GET_SUM_SAL_DEPT(90)
--------------------
               58160
*/

 

 

 

• 소스코드 확인

--소스코드를 읽을수 없는 코드로 변화되어 있음,
select line,text from user_source
where lower(name)='get_sum_sal_dept';
/*
function get_sum_sal_dept wrapped 
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
13e 10f
99DjMoWj2sjSNqSiz+6h1OJtNrUwg+nI154VfC9GAP4+M7QihAhQTzs58S6h3/lurHAIUcqg
vJ15VdRuG5p0ahRMDkCNbDB0XnYkZFcZQQgiNLB0PLeEbQCAPeFEXozW4WdWB3Z2vR/oNONe
0o1vZI4rhjX3EabQq/05LFkigNW/YvFj481kqGv/k7fRESN5ZOMi907zpNFe1cY53qQ0nEMB
8voGDb5H31+cycvRqshzBedWcpAcMLMpJqMgeVp5vvjhITlaHQ==
*/

 

 

 

• 다른 방식 샘플

--조금 더 읽기 편하게 코드를 수정하는 방법,

declare
v1 varchar2(1000):='create or replace function get_sum_sal_dept(dept_id number)  
                        return number
                    is
                        v_sal number;
                    begin
                        select sum(salary)
                        into v_sal
                        from
                        employees
                        where department_id =dept_id;
                        return v_sal;
                        
                        exception 
                        when others then return null;
                    end;';
begin
dbms_ddl.create_wrapped(v1);
end;