063 - [Oracle PL/SQL] Dynamic SQL - to compile packages

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

• Execute immediate 를 이용한 다양한 샘플 코드

# we can also use dynamic sql to execute anonymous-block

declare
    v_code varchar2(100):=
        'begin
        dbms_output.put_line(''welcome'');
        end;
        ';
begin
    execute immediate v_code;
end;
----------------------------------------------



# procedure 컴파일
alter procedure ADD_ROWS compile;
----------------------------------------------
Procedure ADD_ROWS altered.



# function 컴파일
alter function GET_SAL compile;
----------------------------------------------
Function GET_SAL altered.



# package 컴파일
alter package AREA compile specification;
----------------------------------------------
Package AREA altered.



# package body 컴파일
alter package AREA compile body;
----------------------------------------------
Package AREA altered.

 

 

 

• 컴파일용 패키지 샘플 코드

create or replace procedure compile_any_plsql
    (p_name varchar2, p_type varchar2, p_option varchar2 default null)
is
    v_comp_code varchar2(1000):=
    'alter '||p_type||'  '||p_name||' compile '||p_option;
begin
    execute immediate v_comp_code;
    dbms_output.put_line(v_comp_code);
exception
    when others then
        dbms_output.put_line('error: ['||v_comp_code||']');
end;


 
execute compile_any_plsql ('AREA','package','specification');
execute compile_any_plsql ('AREA','package','body');
execute compile_any_plsql ('ADD_ROWS','procedure');
execute compile_any_plsql ('GET_SAL','function');
---------------------------------------------
alter package  AREA compile specification
alter package  AREA compile body
alter procedure  ADD_ROWS compile 
alter function  GET_SAL compile