Unable to add data to Oracle Custom Record - Error - Subscript beyond count
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;
begin
learnerMapCustomRecord := learnerMapCustomRecordTable();
open c1;
loop
fetch c1 into c;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(c.object_name||'==>'||c.status);
- 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;
end;
set serveroutput on;
exec data_collection_extract();
/
show error;
Error report -
ORA-06533: Subscript beyond count ORA-06512: at "FOL_DEV.GETLEARNERMAPDETAILS", line 13 ORA-06512: at "FOL_DEV.DATA_COLLECTION_EXTRACT", line 4 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