VARRAY 사용시 ORA-6533 ERROR

============================

원본출처 : http://www.cyberlab.pe.kr/oracle_metalink/read.neo?id=1&cn=1&tn=1&ln=9&pn=20&lv=0&topic=

PURPOSE

---------

VARRAY를 사용하다 보면 ORA-6533 이라는 error 를 만나게 된다

이는 VARRAY는 default 로 3개의 element 이상을 가져 올수 없기 때문이다

이 경우에는 EXTEND method를 이용하여 해결할 수 있다.

Problem Description

-------------------

bul 12280을 예로 들겠다.

declare

type tax_list is record(

tcode varchar2(5),

tval1 emp.empno%type);

type tax_array is varray(10) of tax_list;

tax_rule tax_array:=tax_array(null,null);

begin

tax_rule(1).tcode:='213';

tax_rule(1).tval1:=0;

dbms_output.put_line('tax_rule(1).tcode:'|| tax_rule(1).tcode);

dbms_output.put_line('tax_rule(1).tval1:'||tax_rule(1).tval1);

tax_rule(2).tcode:='213';

tax_rule(2).tval1:=0;

dbms_output.put_line('tax_rule(2).tcode:'|| tax_rule(2).tcode);

dbms_output.put_line('tax_rule(2).tval1:'||tax_rule(2).tval1);

tax_rule(3).tcode:='213';

tax_rule(3).tval1:=0;

dbms_output.put_line('tax_rule(3).tcode:'|| tax_rule(3).tcode);

dbms_output.put_line('tax_rule(3).tval1:'||tax_rule(3).tval1);

tax_rule(4).tcode:='213';

tax_rule(4).tval1:=0;

dbms_output.put_line('tax_rule(4).tcode:'|| tax_rule(4).tcode);

dbms_output.put_line('tax_rule(4).tval1:'||tax_rule(4).tval1);

tax_rule(5).tcode:='213';

tax_rule(5).tval1:=0;

dbms_output.put_line('tax_rule(5).tcode:'|| tax_rule(5).tcode);

dbms_output.put_line('tax_rule(5).tval1:'||tax_rule(5).tval1);

end;

/

->error

SQL> /

tax_rule(1).tcode:213

tax_rule(1).tval1:0

tax_rule(2).tcode:213

tax_rule(2).tval1:0

declare

*

ERROR at line 1:

ORA-06533: Subscript beyond count

ORA-06512: at line 17

Workaround

-----------

tax_rule.extend(6);를 추가하면 됩니다.

다음과 같이 ...

dbms_output.put_line('tax_rule(2).tcode:'|| tax_rule(2).tcode);

dbms_output.put_line('tax_rule(2).tval1:'||tax_rule(2).tval1);

tax_rule.extend(6);

tax_rule(3).tcode:='213';

tax_rule(3).tval1:=0;

Solution Description:

---------------------

extend는 varray가 얼마나 더 추가 될수 있는지에 따라 값을 주면 됩니다.

Reference Ducument

------------------

Note:1065346.6

SCR(sample code repository) 1312번 varrayExtend.sql 참조.