020 - [Oracle PL/SQL] Collections - Varray

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

 

 

-- 간단한 샘플

declare
    type t_locations is varray(3) of varchar2(100);
    loc t_locations;
begin
    loc:=t_locations('jordan','uae','Syria');
    
    dbms_output.put_line(loc(1) );
    dbms_output.put_line(loc(2) );
    dbms_output.put_line(loc(3) );
end;
-----------------------------------------------
jordan
uae
Syria

 

 

 

# you can not extend the varray, it will give error

declare
    type t_locations is varray(3) of varchar2(100);
    loc t_locations;
begin
    loc:=t_locations('jordan','uae','Syria');
    loc.extend;
    loc(4):='aa';
    dbms_output.put_line(loc(1) );
    dbms_output.put_line(loc(2) );
    dbms_output.put_line(loc(3) );
end;
-------------
Error report -
ORA-06532: Subscript outside of limit
ORA-06512: at line 9
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
           or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
           if necessary.

 

 

 

declare
    type t_locations is varray(3) of varchar2(100);
    loc t_locations;
begin    
    loc:=t_locations('jordan','uae','Syria');
    loc.trim(1); -- this delete one element from last
    dbms_output.put_line(loc(1) );
    dbms_output.put_line(loc(2) );
    dbms_output.put_line(loc(3) ); # 삭제된 인덱스를 접근해서 에러발생함.
end;

-----------------------------------------------
Error report -
ORA-06533: Subscript beyond count
ORA-06512: at line 12
06533. 00000 -  "Subscript beyond count"
*Cause:    An in-limit subscript was greater than the count of a varray
           or too large for a nested table.
*Action:   Check the program logic and explicitly extend if necessary.

 

 

 

# 샘플코드

drop table x_customer;
drop type t_tel;


create or replace type t_tel as varray(10) of number;
--Type T_TEL compiled


create table x_customer
( cust_id number,
  cust_name varchar2(20),
  tel t_tel
)
# no need for this nested table tel store as t_tel_tbl;
--Table X_CUSTOMER created.


insert into x_customer (cust_id,cust_name,tel)
values (1,'khaled',t_tel(050121,0501245,0589877));


select * from x_customer;
/*
   CUST_ID CUST_NAME            TEL                           
---------- -------------------- ------------------------------
         1 khaled               T_TEL(50121, 501245, 589877) 
*/