Home » SQL & PL/SQL » SQL & PL/SQL » Regarding VARRAY ...
Regarding VARRAY ... [message #195899] Tue, 03 October 2006 01:49 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I am getting the following error while trying to display the elements in VARRAY. can anyone suggest what could be the problem.

SQL>
SQL> create or replace package mypack
2 as
3 type arr is varray(5) of number;
4 end;
5 /

Package created.

SQL>
SQL> create or replace procedure p1(p_arr OUT mypack.arr)
2 as
3 begin
4 p_arr:=mypack.arr(null);
5 for i in 1 .. 5 loop
6 p_arr.extend;
7 p_arr(p_arr.last):=i;
8 end loop;
9 end;
10 /

Procedure created.

SQL>
SQL>
SQL> declare
2 i number;
3 p_arr mypack.arr;
4 begin
5 p1(p_arr);
6 i := p_arr.FIRST;
7 while i is not null loop
8 dbms_output.put_line(p_arr(i));
9 i := p_arr.NEXT(i);
10 END LOOP;
11 end;
12 /
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SCOTT.P1", line 6
ORA-06512: at line 5


Regards,
frank
Re: Regarding VARRAY ... [message #195907 is a reply to message #195899] Tue, 03 October 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your mistake is the line
p_arr:=mypack.arr(null);
This is not creating an empty Varray, it is creating one with a value of NULL in the first position, in exactly the same way that
p_arr:=mypack.arr(3);
would create a varray with a value of 3 in the first position.
You need to change the line to
p_arr:=mypack.arr();
Re: Regarding VARRAY ... [message #195950 is a reply to message #195907] Tue, 03 October 2006 05:03 Go to previous message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Thank you very much.
Previous Topic: Combining common rows and concatonating their ID
Next Topic: Connect by prior and SUM
Goto Forum:
  


Current Time: Fri Dec 09 13:59:01 CST 2016

Total time taken to generate the page: 0.09520 seconds