CODEWITHSUNDEEP
arraysoracle-databaseplsql
Jim Vercoelen
Jim Vercoelen

Reputation: 1077

Plsql adding item to array won't workout

I need to add an item to an array but this isn't working out at all.

Code:

DECLARE
  ..
  TYPE textGroupArray IS VARRAY(6) OF VARCHAR(4);
  textGroups textGroupArray;
  arrayCount  NUMBER;
  ..
BEGIN
  ..
  textGroups := textGroupArray(10);
  arrayCount  := 0;
  ..
  IF textGroup != 0 THEN
     arrayCount := arrayCount + 1;
     textGroups( arrayCount ) := textGroup;
  END IF;

For somereason I can't figure out, Oracle isn't adding anything into the array..

(The error i get) enter image description here

P.S. I already tried a lot tho, no hate for being a newby please. Just started learning plSql.

P.S.S. If you don't approve my post format, please submit a edit so I can learn form that.

P.S.S.S If you need some extra information. I will happily add this for you!

Thanks!

Code snippit

Upvotes: 0

Views: 60

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

The usage of VARRAY is wrong.

First try to change your output like this

DBMS_OUTPUT.PUT_LINE( 'textGroup: <'||textGroup||'>' );

Output is

textGroup: < 3536>
textGroup: < 2029>
textGroup: < 2712>
textGroup: < 3456>
textGroup: < 789>
textGroup: <>

So TYPE textGroupArray IS VARRAY(6) OF VARCHAR(4); is not sufficient, you should use TYPE textGroupArray IS VARRAY(6) OF VARCHAR2(5); or use textGroups( arrayCount ) := TRIM(textGroup);

NB, it is not recommended to use datatype VARCHAR. Use VARCHAR2 instead.

Then textGroups := textGroupArray(10); does not set the size of your varray to 10 (which would not be possible anyway, because the max size is only 6). It sets the first element to '10'.

Proper initialization is for example:

textGroups := textGroupArray(NULL,NULL,NULL,NULL,NULL,NULL);

Upvotes: 1

Related Questions