ORA-06533: subscript beyond count

Create or replace procedure Test (tab_name in varchar2)
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.

You can add a line after "v_index: = v_index + 1:
V_refered_columns_array.extend;
Try.

 

Index-by-tables, nested table, varray differences asktom has a post about these three types of differences, I think it is good, review the major difference between :( index by tables) and (nested tables/varrays) Is that index by tables are only available in PLSQL, nested tables/varrays are
Avaialable in both PLSQL * and * SQL. index by tables are basically "sparse" arrays that need no allocation.
Example: ops $ tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number index by binary_integer;
3 Data array;
4 begin
5 Data (1000): = 1;
6 end;
7/PL/SQL procedure successfully completed. Here PLSQL gladly accepts an entry in the 1,000 'th slot-without doing
Anything else. There is nothing in slots 1, 2 ,... 999 (or 1001 or-1,-2 ,....
) That array has allocated space for 1 element in the 1, 000th position. nested
Tables/varrays do not behave that way: ops $ tkyte@ORA817DEV.US.ORACLE.COM> declare
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 here it is saying "you haven't allocated me any space up there ...". So, we use
The. Extend attribute: ops $ tkyte@ORA817DEV.US.ORACLE.COM> declare
2 type array is table of number;
3 Data array: = array ();
4 begin
5 Data. Extend (1000 );
6 data (1000): = 1;
7 end;
8/PL/SQL procedure successfully completed. And we can do so. Note that we do have to allocate 1,000 entries: ops $ tkyte@ORA817DEV.US.ORACLE.COM> declare
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 6The nested table/varray type isn't "sparse" like the index by table. day to day, in PLSQL code, I generally use index by tables exclusively. They
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 (seehttp: // asktom.oracle.com/pls/ask/f? P = 4950: 8: f4950_p8_displayid: 110612348061
For an example of what I mean by that ).... There are other differences between varrays and nested tables when you use them
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.