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

From: A. Mehoela <a.mehoela_at_hoetmeel.com>
Date: Thu, 06 Nov 2014 21:23:04 +0100
Message-ID: <545bd8a8$0$11187$8a7afdce_at_news4.usenet4u.nl>


senthil.teen_at_gmail.com wrote:
> 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?
>

why did you put the "extend" in comments? Received on Thu Nov 06 2014 - 21:23:04 CET

Original text of this message