049 - [Oracle PL/SQL] Package - recompile the package

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

 

 

When & Why to recompile the package?

 

 

# 최초 패키지

create or replace package p_test
is
    c_var1 constant number:=10;
    c_var2 varchar2(100):='welcome';
    
    procedure print;
end;
/
create or replace package body p_test
is
    c_var3 varchar2(100):='hi there'; 
    procedure print
    is 
    c_var4 varchar2(100):='hi';

    begin
        dbms_output.put_line('this variable came from package spec. '||c_var1);
        dbms_output.put_line('this variable came from package spec. '||c_var2);
        dbms_output.put_line('this variable came from package body. '||c_var3);
        dbms_output.put_line('this variable came from print proced. '||c_var4);
    end;

end;

 

 

 

--note that we can update the package body without compile the spec.
# body만 변경된 경우는 spec. 부분을 컴파일할 필요는 없지만 보통 전체 컴파일을 한다.

create or replace package body p_test
is
    c_var3 varchar2(100):='hi there'; 
    
    procedure print
    is 
    c_var4 varchar2(100):='hi';
    begin
        dbms_output.put_line('this variable came from package spec. '||c_var1);
        dbms_output.put_line('this variable came from package spec. '||c_var2);
        dbms_output.put_line('this variable came from package body. '||c_var3);
        dbms_output.put_line('this variable came from print Proc. '||c_var4);
    end;
 
    -- body에 추가된 부분     
begin
    dbms_output.put_line('this is optional');
end;

execute p_test.print;
----------------------------------------------------

# 출력순서는 패키지의 begin 부분이 먼저 실행되고, 그 후에 procedure 실행되는 순서임
this is optional
this variable came from package spec. 10
this variable came from package spec. welcome
this variable came from package body. hi there
this variable came from print Proc. hi

 

 

 

# now let us try to change the package spec
# 이번에는 body에는 변화가 없어서 spec 만 컴파일한 경우

create or replace package p_test
is
    c_var1 constant number:=10;
    c_var2 varchar2(100):='welcome';
    p_n number;
    procedure print;
end;


# 출력결과에는 변화가 없다
execute p_test.print;
----------------------------------------------------
this is optional
this variable came from package spec. 10
this variable came from package spec. welcome
this variable came from package body. hi there
this variable came from print Proc. hi

 

 

 

# now let us try to change the package spec again
# a major change/ add new subprogram
# body에는 변화가 없지만 함수같이 body에서 정의를 해야하는 경우는 
# spec만 컴파일하면 컴파일시 오류는 없지만 실행시 아래처럼 오류가 발생한다.

create or replace package p_test
is
    c_var1 constant number:=10;
    c_var2 varchar2(100):='welcome';
    p_n number;
    procedure print;
    function get_name (p number) return varchar2;
end;

execute p_test.print;
-----------------------------------------
Error report -
ORA-04063: package body "HR.P_TEST" has errors
ORA-06508: PL/SQL: could not find program unit being called: "HR.P_TEST"
ORA-06512: at line 1
04063. 00000 -  "%s has errors"
*Cause:    Attempt to execute a stored procedure or use a view that has
           errors.  For stored procedures, the problem could be syntax errors
           or references to other, non-existent procedures.  For views,
           the problem could be a reference in the view's defining query to
           a non-existent table.
           Can also be a table which has references to non-existent or
           inaccessible types.
*Action:   Fix the errors and/or create referenced objects as necessary.

 

 

 

# spec, body를 함께 컴파일하지 않을때 아래처럼 status 에서 오류가 발생한 것을 확인가능함.

select object_name, object_type, created, status
from user_objects
where oBject_name='P_TEST'
-----------------------------------------
OBJECT_NAM OBJECT_TYPE             CREATED   STATUS 
---------- ----------------------- --------- -------
P_TEST     PACKAGE                 28-FEB-24 VALID  
P_TEST     PACKAGE BODY            28-FEB-24 INVALID

 

 

 

소스코드를 확인하는 방법

 

SELECT * FROM USER_SOURCE
WHERE NAME='P_TEST'
AND TYPE='PACKAGE';

SELECT * FROM USER_SOURCE
WHERE NAME='P_TEST'
AND TYPE='PACKAGE BODY';

 

 

 

--to drop package specification and body   

drop package p_test;

--to drop only package body 

drop package body p_test;