Home » SQL & PL/SQL » SQL & PL/SQL » "function in package return array used in SQL" error ..
"function in package return array used in SQL" error .. [message #226602] Sun, 25 March 2007 15:57 Go to next message
m_arafa
Messages: 36
Registered: June 2006
Member
hi all,

i write the following lines :-

create or replace type numarray as varray(50) of number
/

create or replace package pkg as
function return_array return numarray;
end;
/
show errors

create or replace package body pkg as
procedure ret_cur( o_cur out numarray )
is
i integer := 0;
p_cur numarray := numarray();
begin
for i in 1..20 loop -- 20 is the last number in this array
p_cur.extend(i);
p_cur(i) := i;
end loop;

o_cur := p_cur;
end;

function return_array return numarray
is
i_arr numarray;
begin
ret_cur( i_arr );
return i_arr;
end;
end;
/
show errors


select *
from table( cast( pkg.return_array() as numarray ) );

ERROR at line 2:
ORA-06532: Subscript outside of limit
ORA-06512: at "SCOTT.PKG", line 8
ORA-06512: at "SCOTT.PKG", line 19

although "20" is the last number in this array, i receive the erro above , Why???

thanks in advance,
Re: "function in package return array used in SQL" error .. [message #226623 is a reply to message #226602] Sun, 25 March 2007 23:06 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
extends
Quote:
p_cur.extend(i);
So, you are extending the varray with 1+2+3...+20. It is going beyond 60, which is your maximum limit for the varray.

edit: 1. Try using only p_cur.extend;
(Or) 2. p_cur.extend(20); before the loop.

By
Vamsi

[Updated on: Sun, 25 March 2007 23:08]

Report message to a moderator

Previous Topic: Associative array as type
Next Topic: how to retrive this?
Goto Forum:
  


Current Time: Thu Dec 08 04:08:01 CST 2016

Total time taken to generate the page: 0.13022 seconds