Home » SQL & PL/SQL » SQL & PL/SQL » Error Pl/SQL collection. (Oracle 10.2.0.4.0 - 64bi, Windows Xp Sp2)
Error Pl/SQL collection. [message #366178] Fri, 12 December 2008 00:17 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hello Everyone.

I am doing some POC for table fucntion. When I tried to execute the following package I found the following exception.

ORA-06533: Subscript beyond count
ORA-06512: at "FUN_TAB_TEST", line 13

The Package Code is like

CREATE OR REPLACE PACKAGE fun_tab_test
IS
  TYPE rec1 IS RECORD (
    seq   NUMBER (10)
  );

  TYPE tab1 IS TABLE OF rec1;

  FUNCTION test1
    RETURN tab1 PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY fun_tab_test
IS
  FUNCTION test1
    RETURN tab1 PIPELINED
  IS
    v_tab1   tab1        := tab1 ();
    num   NUMBER (10) := 1;
  BEGIN
    FOR j IN (SELECT     LEVEL lvl
                    FROM DUAL
              CONNECT BY LEVEL <= 10)
    LOOP
      v_tab1 (num).seq := j.lvl;
      num := num + 1;
      PIPE ROW (v_tab1 (num));
    END LOOP;

    RETURN;
  END;
END;
/

SELECT * FROM TABLE (fun_tab_test.test1);

ORA-06533: Subscript beyond count
ORA-06512: at "FUN_TAB_TEST", line 13


Please correct me where I am wrong.

Thanks
Trivendra
Re: Error Pl/SQL collection. [message #366179 is a reply to message #366178] Fri, 12 December 2008 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must allocate each element of the table before using it: v_tab1.extend.

Regards
Michel
Re: Error Pl/SQL collection. [message #366184 is a reply to message #366178] Fri, 12 December 2008 00:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am guessing your problem lies somewhere around line 13. That said, let me paint a picture for you:

1) you have an array
2) it has 100 elements in it 1..100
3) you execute this piece of code

avar := myarray_v(101);

? what will happen ?

You array subscript = 101.
The last element in your array is 100.

your subscript of 101 is beyond the count of elements in the array eg. 101 > 100 ergo "subscript beyond count".

Additionally consider this plsql code snippet:

myarray_v(1) := 1;
myarray_v(2) := 2;
myarray_v(5) := 5;

avar := myarray_v (4);

Now what will happen? Try it and tell us.

Good luck, Kevin
Re: Error Pl/SQL collection. [message #366191 is a reply to message #366178] Fri, 12 December 2008 01:17 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Kevin/Michel,

Great help, Little logical mistake. I have changed the code to..

LOOP
      v_tab1.EXTEND (num);
      v_tab1 (num).seq := j.lvl;
      PIPE ROW (v_tab1 (num));
      num := num + 1;
 END LOOP;


Kevin,

Great Example and illustration.

Here at
avar := myarray_v (4);


Will raise the same error as I have got.

Thanks
Trivenda
Re: Error Pl/SQL collection. [message #366193 is a reply to message #366191] Fri, 12 December 2008 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v_tab1.EXTEND (num);

Extend with num elements more.
Just use "extend" to add one element.

Regards
Michel
Re: Error Pl/SQL collection. [message #366202 is a reply to message #366178] Fri, 12 December 2008 02:20 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Ok. Done.

Thanks Michel.

Previous Topic: Is there a better way to handle null parameters?
Next Topic: delete cascade
Goto Forum:
  


Current Time: Sun Dec 04 08:58:12 CST 2016

Total time taken to generate the page: 0.07534 seconds