069 - [Oracle PL/SQL] NOCOPY 예외상황

 

 

 

# nocopy will have no effects
# case 1

create or replace procedure pass_by_value3
    ( P_id IN out nocopy number)
is
begin
    P_id:=P_id*10;
      if P_id>100 then
          raise VALUE_ERROR;
      end if;
end;
/


# 값에 사이즈를 지정하면 nocopy 옵션이 동작하지 않는다
declare
    --if the actual has size(FOR NUMBER ONLY ),nocopy not working
    v number(10):=20; 
begin
    pass_by_value3(V);
    dbms_output.put_line('NO ISSUE '||v);

    EXCEPTION
        WHEN VALUE_ERROR THEN 
        dbms_output.put_line('EXCEPTION '||v);
end;
------------------------------------------------
EXCEPTION 20

 

 

 

# case 2, if the actual NOT NULL,nocopy not working

create or replace procedure pass_by_value4
( P_id IN out nocopy number)
is
begin
P_id:=P_id*10;
  if P_id>100 then
  raise VALUE_ERROR;
  end if;
end;
/


declare
    v number NOT NULL :=20; --if the actual NOT NULL,nocopy not working
begin
    pass_by_value4(V);
    dbms_output.put_line('NO ISSUE '||v);

    EXCEPTION
        WHEN VALUE_ERROR THEN 
        dbms_output.put_line('EXCEPTION '||v);
end;
------------------------------------------
EXCEPTION 20

 

 

 

# case 3, 파라미터의 타입이 변경된 경우, nocopy 옵션은 무시된다.

create or replace procedure pass_by_value5
    ( P_id IN out nocopy VARCHAR2)
is
begin
    P_id:=P_id*10;
      if P_id>100 then
      raise VALUE_ERROR;
      end if;
end;
/
declare
    v number :=20; --if the actual NEED IMPLICIT CONV ,nocopy not working
begin
    pass_by_value5(V);
    dbms_output.put_line('NO ISSUE '||v);

    EXCEPTION
        WHEN VALUE_ERROR THEN 
        dbms_output.put_line('EXCEPTION '||v);
end;
------------------------------------------
EXCEPTION 20
PL/SQL procedure successfully completed.

 

 

 

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