114 - [Oracle PL/SQL] Dependencies - Impact on the Package

 

• 시노님의 원본 테이블이 변경되어도 오류가 발생하지 않는 경우는 아래와 같다

 

 

구조가 다른 테이블 간의 시노님 변경에 대해서 알아보겠습니다.

 

 

• 테스트 준비

select * from products;
drop table products;

--다른 구조의 테이블간에 시노님이 변경되는 경우를 확인해보자
--오류가 발생/미발생 경우가 있다.

drop table all_products;
drop synonym s_products ;


-- 원본 테이블 생성
create table all_products
(producut_id number,
 producut_name varchar2(30),
 producut_category varchar2(20)
);

-- 테이블에 데이터 추가
insert into all_products values (1,'Sony 52 Inch/Led M0014','TV');


-- 시노님 생성
create or replace synonym s_products for all_products;


--원본 테이블 및 시노님을 통한 조회가 동일하다.
select * from all_products;
/*
PRODUCUT_ID PRODUCUT_NAME                  PRODUCUT_CATEGORY   
----------- ------------------------------ --------------------
          1 Sony 52 Inch/Led M0014         TV
*/


select * from s_products;
/*
PRODUCUT_ID PRODUCUT_NAME                  PRODUCUT_CATEGORY   
----------- ------------------------------ --------------------
          1 Sony 52 Inch/Led M0014         TV
*/

 

 

 

• 시노님을 이용하여 뷰와 프로시져 생성

-- 뷰생성(시노님과 연결된)
create or replace view vs_all_products
as
select * from s_products;

-- 프로시져 생성(시노님과 연결된)
create or replace procedure read_from_synonym
is
begin
    for i in (select * from s_products)
    loop
    null;
    end loop;
end;
/

-- 프로시져 생성(시노님과 연결된)
create or replace procedure read_from_synonym2
is
begin
    for i in (select producut_id from s_products)
    loop
    null;
    end loop;
end;


--모든 객체의 상태가 정상이다.
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('all_products','s_products','vs_all_products','read_from_synonym','read_from_synonym2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
ALL_PRODUCTS                   TABLE                   21-MAY-24 VALID  
READ_FROM_SYNONYM              PROCEDURE               21-MAY-24 VALID  
READ_FROM_SYNONYM2             PROCEDURE               21-MAY-24 VALID  
S_PRODUCTS                     SYNONYM                 21-MAY-24 VALID  
VS_ALL_PRODUCTS                VIEW                    21-MAY-24 VALID 
*/

 

 

 

• 시노님을 변경하면 프로시져와 뷰의 상태가 비정상으로 변함

-- 시노님을 다른 테이블을 새로 연결한다. 기존 테이블과 다른 구조의 새로운 테이블임.
create or replace synonym s_products for employees;

-- 원본 테이블과 시노님을 제외한 다른 객체의 상태는 비정상이다.
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('all_products','s_products','vs_all_products','read_from_synonym','read_from_synonym2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
ALL_PRODUCTS                   TABLE                   21-MAY-24 VALID  
READ_FROM_SYNONYM              PROCEDURE               21-MAY-24 INVALID
READ_FROM_SYNONYM2             PROCEDURE               21-MAY-24 INVALID
S_PRODUCTS                     SYNONYM                 21-MAY-24 VALID  
VS_ALL_PRODUCTS                VIEW                    21-MAY-24 INVALID
*/

 

 

 

• 첫번째 프로시져의 경우, * 를 이용하여 전체 컬럼을 가져오므로 실행만 해도 자동 컴파일되며 상태가 정상으로 변경됨, 사실 내부적으로는 처리하는 로직이 없으므로 이부분은 논외입니다

--프로시져에서 시노님을 호출할때, 전체 컬럼을 가져오므로 
--프로시져를 실행하면 자동 컴파일된 후, PROCEDURE 상태가 정상으로 변경된다
exec read_from_synonym;

select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('read_from_synonym');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
READ_FROM_SYNONYM              PROCEDURE               21-MAY-24 VALID
*/

 

 

 

• 두번째 프로시져의 경우, 컬럼이름을 지정했기때문에 실행하면 오류가 발생한다. 아무런 처리를 하지 않지만 컬럼이름이 매칭되지 않아서 오류가 자동 컴파일에서 오류가 발생하고, 그래서 실행이 되지 않는다

--이전에 read_from_synonym 자동으로 컴파일되는것과는 다르게
--read_from_synonym2 프로시져는 오류가 발생한다.
--이유는 해당 프로시져에서 특정 컬럼을 참고하는데, 해당 컬럼이 새로운 시노님에는 없기때문이다.

exec read_from_synonym2;
/*
Error report -
ORA-06550: line 1, column 7:
PLS-00905: object HR.READ_FROM_SYNONYM2 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
*/


--그래서 아래 정보와 마찬가지로 READ_FROM_SYNONYM2 상태는 비정상이다.
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('read_from_synonym2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
READ_FROM_SYNONYM2             PROCEDURE               21-MAY-24 INVALID

 

 

 

• 뷰의 경우는 자동 컴파일이 되지 않아서 직접 컴파일을 다시 하면 정상 동작한다. 여기서도 뷰에서 특정 컬럼을 지정했다면 오류가 발생할수 있다

-- 뷰에서도 오류가 발생하지만 프로시져와는 다르게 수동으로 컴파일을 다시하면, 정상동작한다.
select * from vs_all_products;
/*
Error report -
SQL Error: ORA-04063: view "HR.VS_ALL_PRODUCTS" has errors
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.
*/

create or replace view vs_all_products
as
select * from s_products;

--컴파일후에는 정상동작한다.
select * from vs_all_products;
/*
....
107 rows selected. 
*/

 

 

 

 

이번에는 구조가 같은 테이블 간의 시노님 변경에 대해서 알아보자.

 

 

• 테스트 준비

--이번에는 같은 구조로 복제된 2개의 테이블간에 시노님이 변경되는 경우를 확인해보자.
drop synonym s_products ;
drop table all_products;
drop table all_products2;

create table all_products
(producut_id number,
 producut_name varchar2(30),
 producut_category varchar2(20)
);

insert into all_products values (1,'Sony 52 Inch/Led M0014','TV');


create table all_products2 as select * from all_products;


create or replace synonym s_products for all_products;


select * from all_products;
/*
PRODUCUT_ID PRODUCUT_NAME                  PRODUCUT_CATEGORY   
----------- ------------------------------ --------------------
          1 Sony 52 Inch/Led M0014         TV 
*/

select * from s_products;
/*
PRODUCUT_ID PRODUCUT_NAME                  PRODUCUT_CATEGORY   
----------- ------------------------------ --------------------
          1 Sony 52 Inch/Led M0014         TV 
*/

 

 

 

• 시노님 생성 - 원본 테이블들, 시노님, 뷰, 모두 정상 상태임

--시노님을 참조하는 뷰 생성.
create or replace view vs_all_products
as
select * from s_products;

--모든 객체의 상태는 정상이다.
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('all_products','vs_all_products','s_products','all_products2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
ALL_PRODUCTS                   TABLE                   21-MAY-24 VALID  
ALL_PRODUCTS2                  TABLE                   21-MAY-24 VALID  
S_PRODUCTS                     SYNONYM                 21-MAY-24 VALID  
VS_ALL_PRODUCTS                VIEW                    21-MAY-24 VALID 
*/

 

 

 

• 시노님 변경 - 새로운 테이블로 변경했지만, 구조가 동일한 테이블이라서 관련된 객체들의 상태도 정상

-- 기존 시노님을 새로운 테이블에 연결한다.
create or replace synonym s_products for all_products2;


-- 시노님을 새로 연결했지만, 모든 객체의 상태가 정상이다.
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('all_products','vs_all_products','s_products','all_products2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
ALL_PRODUCTS                   TABLE                   21-MAY-24 VALID  
ALL_PRODUCTS2                  TABLE                   21-MAY-24 VALID  
S_PRODUCTS                     SYNONYM                 21-MAY-24 VALID  
VS_ALL_PRODUCTS                VIEW                    21-MAY-24 VALID 
*/