create or replace procedure test(tab_name in varchar2) as
cursor cur_ref_cons_info(tab_name varchar2) is select b.COLUMN_NAME from user_constraints a,user_cons_columns b where
a.TABLE_NAME=tab_name and a.CONSTRAINT_TYPE='R' and b.CONSTRAINT_NAME=a.R_CONSTRAINT_NAME;
v_index number(1):=0;
type refered_columns is table of user_cons_columns.COLUMN_NAME%type;
v_refered_columns_array refered_columns:=refered_columns();
begin
open cur_ref_cons_info(tab_name);
loop
v_index:=v_index+1;
fetch cur_ref_cons_info into v_refered_columns_array(v_index);
/* if cur_ref_cons_info%rowcount=0 then
dbms_output.put_line('no results');
else
dbms_output.put_line('have results');
end if; */
end loop;
close cur_ref_cons_info;
exception
when others then
dbms_output.put_line(sqlerrm);
end test;
--ORA-06533: Subscript beyond count
ORA-06533: 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.
可以考虑在“v_index:=v_index+1;”这句后加一行:
v_refered_columns_array.extend;
试试。
avaialable in both PLSQL *and* SQL.
example:
2 type array is table of number index by binary_integer;
3 data array;
4 begin
5 data(1000) := 1;
6 end;
7 /
anything else. There is nothing in slots 1, 2, … 999 (or 1001 or -1, -2,….
)
tables/varrays do not behave that way:
2 type array is table of number ;
3 data array := array();
4 begin
5 data(1000) := 1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5
the .extend attribute:
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1000);
6 data(1000) := 1;
7 end;
8 /
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1);
6 data(1000) := 1;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6
are a little faster, a little more flexible. It is when I need to use the table
type in SQL that I use a nested table (see
for an example of what I mean by that)….
for storage in a database table. For example — varrays maintain their “order”.
Nested tables do not. Varrays are stored in lobs — nested tables in separate
tables. There are certain DML operations available for nested tables that are
not avaialable for varrays.