Unable to add data to Oracle Custom Record - Error - Subscript beyond count

From: <senthil.teen_at_gmail.com>
Date: Wed, 5 Nov 2014 09:53:08 -0800 (PST)
Message-ID: <cc61a24a-842e-4c50-a4a4-b3ccf7e1d15e_at_googlegroups.com>

I have created Custom Oracle Record Type and iam trying to populate with some values and trying to access the same in another stored procedure(actually i will be calling from java-Iam getting the same error in java too.) but iam getting "Subscript beyond count" error message.

Iam just a beginner to Stored Procedure. Below is what i have done

Oracle Custome Record Type

create or replace type learnerMapCustomRecord as object(activityName varchar2(20),activityDescn varchar2(20));

create or replace type learnerMapCustomRecordTable as table of learnerMapCustomRecord; Stored Procedure to populate values to record type

create or replace PROCEDURE getLearnerMapDetails(learnerMapCustomRecord out learnerMapCustomRecordTable) as cursor c1 is select object_name,status from user_objects where rownum <= 2; c c1%rowtype;
i number:=1;
  learnerMapCustomRecord := learnerMapCustomRecordTable();   open c1;
    fetch c1 into c;

  • learnerMapCustomRecord.extend; learnerMapCustomRecord(I).activityName:=C.OBJECT_NAME; learnerMapCustomRecord(i).activityDescn:=c.status; i:=i+1; end loop; end; Stored proc from where iam invoking the above SP to access the list of Custom Record Types.

create or replace procedure data_collection_extract as learnerMapCustomRecord learnerMapCustomRecordTable; begin
  getLearnerMapDetails(learnerMapCustomRecord);   for i in learnerMapCustomRecord.first..learnerMapCustomRecord.last   LOOP
   dbms_output.put_line(learnerMapCustomRecord(i).activityName||'==>'||learnerMapCustomRecord(i).activityDescn);   end loop;

set serveroutput on;
exec data_collection_extract();
show error;
Error report -

ORA-06533: Subscript beyond count
ORA-06512: at line 1

06533. 00000 - "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.
Any Takers? Received on Wed Nov 05 2014 - 18:53:08 CET

Original text of this message