This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 19, 2012 10:57 AM by user12200443 RSS

why? : ORA-06532: Subscript outside of limit

user12200443 Newbie
Currently Being Moderated
I want to read a number of rows into an array of records.

I keep getting this error (any ideas?):
ERROR at line 1:
ORA-06532: Subscript outside of limit

---
here is the procedure:

CURSOR get_all_locations (type IN VARCHAR2) IS
SELECT
*
FROM group
WHERE type = type;


PROCEDURE fetchAllServerLocations IS
i BINARY_INTEGER := 0;
erec egroup%ROWTYPE;
BEGIN
dbms_output.enable;
dbms_output.put_line('BEGIN: fetchAllServerLocations()');
FOR erec IN get_all_locations('s')
LOOP
-- dbms_output.put_line('i=[' || i || ']');
dbms_output.put_line('server location: [' || erec.group_id || erec.name || ']');
allServerLocations(i) := erec;
i := i + 1;
END LOOP;
dbms_output.put_line('END: fetchAllServerLocations()');
END fetchAllServerLocations;

---
  • 1. Re: why? : ORA-06532: Subscript outside of limit
    BluShadow Guru Moderator
    Currently Being Moderated
    At the point your code comes to this...
    allServerLocations(i) := erec;
    i := i + 1;
    what is the value of "i"?

    I'm guessing it's still 0 the first time it comes to it. Perhaps you need to be incrementing i before using it as an index to a collection.
  • 2. Re: why? : ORA-06532: Subscript outside of limit
    myOra_help Journeyer
    Currently Being Moderated
    Where is the definetion of "allServerLocations".

    You are might be getting error because i is '0' initially in allServerLocations(i) and this value is outside the limit of your neted/varray.

    Legal values for Nested table is 1..2147483647 (the upper limit of PLS_INTEGER) and for varrays 1.. size_limit, where you specify the limit in the declaration (size_limit cannot exceed 2147483647)

    For associative arrays with a numeric key, -2147483648..2147483647 and for associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set
  • 3. Re: why? : ORA-06532: Subscript outside of limit
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    In the passing if you have column name and parameter name see what happens:
    declare
    cursor c1( empno in number)
    is 
    select empno,ename from emp where empno=empno;
    begin
    for rec_set in c1(7369)
    loop
    dbms_output.put_line('empno' || rec_set.empno || ' ename ' || rec_set.ename);
    end loop;
    end;
  • 4. Re: why? : ORA-06532: Subscript outside of limit
    Dom Brooks Guru
    Currently Being Moderated
    Something like:
    DECLARE
      CURSOR get_all_locations (type IN VARCHAR2) IS
      SELECT
      *
      FROM group
      WHERE type = type;
      TYPE t_all_locations is TABLE OF get_all_locations%ROWTYPE INDEX BY PLS_INTEGER;
      v_all_locations t_all_locations;
    BEGIN
      OPEN get_all_locations;
      FETCH get_all_locations BULK COLLECT INTO v_all_locations;
      CLOSE get_all_locations;
    END;
    The question is what's the point of the collection?
  • 5. Re: why? : ORA-06532: Subscript outside of limit
    theoa Pro
    Currently Being Moderated
    On another note, your cursor will always return all rows.
    The parameter has the same name as the column (type). When used without any prefix, the column will be used in both sides of the where clause.
    CURSOR get_all_locations (type IN VARCHAR2) IS
    SELECT
    *
    FROM group
    WHERE *type = type*;
    Oh, and I wouldn't use "type" as name for a column or variable.
  • 6. Re: why? : ORA-06532: Subscript outside of limit
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    I want to read a number of rows into an array of records
    >
    Why? Your procedure doesn't do anything with the data and doesn't have an OUT parameter to return the data so that a caller can do anything with it.

    Why don't you explain what you are REALLY wanting to do so we can help you find the best way to do that. Using PL/SQL may not be the way to go.

    SQL is almost always faster than PL/SQL so if SQL can be used to get your job done that is probably the better approach. Until you tell us what you are trying to do we won't know.
  • 7. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    The value of i at this point should be 0 as it is initialized before. It should be OK being 0. I tried before incrementing it to 1 and had the same problem. Any ideas?
  • 8. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    Here is the definition of 'allServers'

    groupRecord group%ROWTYPE;
    TYPE GroupRecords IS VARRAY (5000) OF group%ROWTYPE;

    allServerLocations GroupRecords := GroupRecords();


    anything that I need to change here to get this to work?

    Edited by: user12200443 on Nov 19, 2012 10:39 AM
  • 9. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    I changed the initial value of (i) to 1 instead of starting at 0 and now get another problem:

    ORA-06533: Subscript beyond count


    any ideas?
  • 10. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    What I want to do is to create a global collection to be read by other methods. This list of locations needs to be iterated through (one column is an id/key into another set of records that I need to process).
  • 11. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    What I am hoping to do is to create a global collection in this one method that will be used elsewhere (iterating through the collection in another procedure in the PL/SQL package).

    I included the very minimum to showcase the problem. What I do with the collection afterwards I can explain if you would like. I simply thought that including the details of what I am doing with the collection would be unnecessary noise to helping solve the problem of why it cannot be created.

    We have a customer that has mucked up their database and I need to move a lot of data around. PL/SQL is probably the best approach here, I need to do some procedural things. I could do it in Java or any other language but there is too much overhead given the amount of data that we need to move around.

    SQL is great but it does not offer me the procedural capabilities of the 20+ things I have to do.

    What am I trying to do: For each server location, I need to query the tree of group locations, and create a device group for each one of those (then 20 other things follow for which this has an implication). I can send you the the analysis document from our architect that would explain more.

    Edited by: user12200443 on Nov 19, 2012 10:20 AM
  • 12. Re: why? : ORA-06532: Subscript outside of limit
    sb92075 Guru
    Currently Being Moderated
    You have a (poor) solution in search of a problem.
    PL/SQL will never be faster than plain SQL to access data within Oracle DB.
  • 13. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    You have a (poor) solution in search of a problem.
    PL/SQL will never be faster than plain SQL to access data within Oracle DB.
    You do not know what the problem is and you do not know the solution.

    The only thing you know is a syntax error that I am having with a very small piece of code that is trying to do a very specific thing.

    You have tried to reach too far and are blinded by trying to solve a big problem when there is really a small problem to solve.

    ---
    <ranton>

    I (we) have a well thought out solution and algorithm that will solve our problem. I am attempting to implement the solution that has already been analyzed over several weeks now by someone that has 10+ years of knowledge of what data we need to move around and where. The problem is unknown to you. The solution is unknown to you, yet you are making statements about both.

    I cannot write procedures, functions and have them called in the order that I need with plain SQL. Also after the implementation is complete in PL/SQL it will be fast enough for what we need to do. The requirement I have is not to have the one time script run as blazingly fast as it possibly can at all costs to providing an implementation. If I were to spend ten weeks writing this in assembly, it would be much faster than SQL but "faster" is not always better nor what is wanted here.

    This plane:
    http://en.wikipedia.org/wiki/Concorde

    is faster than this plane
    http://en.wikipedia.org/wiki/Concorde

    and is far superior in many, many ways, but there is a reason you have never flown one and a reason all airports everywhere do not have them coming and going every minute AND a big reason they were grounded to never fly again. I am not sure why I have to have this same discussion every now and then when someone comes to me and says "this would be faster".

    Right now in another area I am having an unbelievable battle: Do we write our own messaging system that would be much faster than JMS, AMQP or anything we could simply pull off the shelf - I mean it will be faster but only cost $200K in its first year of writing and maintenance.

    Please go home with comments like this and if you are not going to help contribute to the solution do not log on cheez.

    "Fast" is not a requirement nor is it a concern with this problem and solution. It is completely beside the point.

    </rantoff>
  • 14. Re: why? : ORA-06532: Subscript outside of limit
    user12200443 Newbie
    Currently Being Moderated
    thanks, it was not actually type, but I changed it to show the example. will give this a go.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points