array error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: array error

  1. #1
    Join Date
    Nov 2001
    Posts
    55
    CREATE OR REPLACE PACKAGE t_pack AS

    TYPE vAryAssayId IS VARRAY(100) OF NUMBER(10);
    TYPE AIMASSAY_cur IS REF CURSOR;

    procedure ab;

    END t_pack;
    /
    CREATE OR REPLACE PACKAGE BODY t_pack AS

    procedure ab is
    AssayIds vAryAssayId;
    nvar_assayid number(10);
    idx smallint;
    asy_cur AIMASSAY_cur;
    begin
    OPEN ASY_cur FOR
    SELECT DISTINCT ASSAY_ID
    FROM ASSAY_ATTRIBUTE;
    idx := 1;
    LOOP
    FETCH ASY_cur INTO nvar_assayid;
    EXIT WHEN ASY_cur%NOTFOUND;
    AssayIds(idx) := nvar_assayid;
    idx := idx + 1;
    END LOOP;
    end ab;
    END t_pack;
    /
    exec t_pack.ab;

    is giving me error,

    ERROR at line 1:
    ORA-06531: Reference to uninitialized collection
    ORA-06512: at "AMGENAIMDBO.T_PACK", line 16
    ORA-06512: at line 1

    Please help me,

    ThanX in advance

    Raj

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    it is necessary to initialize the values in the type variable. the code snippet below is an example:

    CREATE OR REPLACE PACKAGE body t_pack AS
    procedure ab as
    a vAryAssayId := vAryAssayId(1,2,3,4,5,6,7,8,9,10);
    begin
    for i in 1..10 loop
    dbms_output.put_line(a(i));
    end loop;
    end;
    END t_pack;

    CREATE OR REPLACE PACKAGE t_pack AS
    TYPE vAryAssayId IS VARRAY(100) OF NUMBER(10);
    procedure ab;
    END t_pack;

    hope this helps.
    Cheers!
    OraKid.

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    That's why I used the following line in my explanation for you last thread.

    my_varray definitions.my_varray_type := definitions.my_varray_type();

    http://www.dbasupport.com/forums/sho...threadid=19196

    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Dec 2001
    Location
    Bangalore, India
    Posts
    23
    Hi Tim,

    I tried with () "initialize empty array", itz giving me the following error.

    ERROR at line 1:
    ORA-06533: Subscript beyond count
    ORA-06512: at "SCOTT.T_PACK", line 6
    ORA-06512: at line 1

    Colud you please help us on this.
    Regards,
    Vijay R.

  5. #5
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Rajkartha,

    when you define an array you have to initialize it like:
    AssayIds:=vAryAssayId()
    After that everytime you need to put value into it you have to extend the array by:
    AssayIds.extend(1)--default is 1 you can extend as many as you want in one go or you can put this statement in the loop to do it before inserting any value.
    and then assign any value to it.

    Vijay,

    ERROR at line 1:
    ORA-06533: Subscript beyond count
    ORA-06512: at "SCOTT.T_PACK", line 6
    ORA-06512: at line 1
    The above error means you tried to reference an array which is not initialized or doesn't have any value. For eg you have an array called numarray which has values in array from 1 to 6. if you try to access the numarray(7) you will get this error.
    If you want an empty array you have to set each of them to null.

    For eg.
    initialize
    begin
    AssayIds:=vAryAssayId()
    assayIds.extend(10)
    for i in 1..10
    loop
    assayids(i):=null;
    end loop;
    end;
    Note: there may be error in the above eg, I am just trying to give you an example, I think this way it should work.


    Hope this helps



    KN

  6. #6
    Join Date
    Nov 2001
    Posts
    55
    after using assayids.extend I am still getting

    ERROR at line 1:
    ORA-06532: Subscript outside of limit
    ORA-06512: at "AMGENAIMDBO.T_PACK", line 16
    ORA-06512: at line 1

    here is what I did

    AssayIds.extend(1);
    AssayIds(idx) := nvar_assayid;


    ThanX
    Raj

  7. #7
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    investigate at what the idx value is, if it is referring to a uninitialized member of the array then you will get this error, like if you are referring to arr(2) when you have extended only 1 then you will encounter this error.

    HTH.
    KN

  8. #8
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    Mr. Knarayan,
    It's forbidden to extend array since an array is an array.
    An array has a maximum limit, so it's impossible to
    extend it.


    ohh Rajkartha, if you really want to extend something,
    extend a table type.

    TYPE t_name IS TABLE OF NUMBER;
    -- without 'index by binary_integer'

    v_var t_name := t_name(1,2,3,4,5,6,7,8,9);
    -- dont forget to initialize it.

    v_var.extend(1);
    -- if you want more, extend it.

    F.
    The Master of PL/SQL

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    This is untrue. When you set a maximum limit and initialize a blank array structure it has no elelments. You have to add the elements using extend. You cannot simply reference the element like and INDEX BY TABLE. The EXTEND method is needed in both tables and varrays, unless of course you add all your elements as part of the initialization. See my original varray example:

    http://www.dbasupport.com/forums/sho...threadid=19196

    Rajkartha, this thread is now spread across four different threads. I know your asked different questions but they all relate to using varrays. In future could you try to progress the existing thread rather than split it up.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    I mean, for example:


    TYPE t_array IS VARRAY(10) OF NUMBER;
    v_var1 t_array := t_arrat();

    In this example you can use at maximum 10 elements,
    and cannot expand beyond it because it was defined
    to have at max 10 elements. Am I right? I don`t use
    this, I just read about it.

    Because, why does it exist if you can extend it to more
    than it was specified, if there`s another structure (that
    is type table - that is able to extend no limits) that you
    don`t have to specify a limit?

    There are some many replies that I think we`re talking
    about different things - elements and initialized values.

    F.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width