How to initialize object of record as array in plsql?

Question

I have following pl/SQL block for getting records from record type,and i am using object of record as array.It gives wrong number or types of argument error where i initialize array.kindly give me written solution for initializing object of record as array.

DECLARE
 TYPE loc IS RECORD (
 loc_id    number(4),
 str_add VARCHAR2(40),
 p_code    VARCHAR2(12),
 city       VARCHAR2(30),
 st_pro VARCHAR2(25),
 c_id     CHAR(2),             
 Oper varchar2(1));


--names namesarray;
--loc_rec loc IS VARRAY(8);
type loc_rec IS VARRAY(8) OF loc;

total number(4);
tot number(3);
loc_id1    number(4);
i number(2);


begin

i:=1;
loc_rec(1).loc_id:=1;
loc_rec(1).str_add:='chikhli';
loc_rec(1).p_code:='396521';
loc_rec(1).city:='chikhli';
loc_rec(1).st_pro:='Gujarat';
loc_rec(1).c_id:='G1';
loc_rec(1).Oper:='I';

loc_rec(2).loc_id:=2;
loc_rec(2).str_add:='chikhli';
loc_rec(2).p_code:='396521';
loc_rec(2).city:='chikhli';
loc_rec(2).st_pro:='Gujarat';
loc_rec(2).c_id:='G1';
loc_rec(2).Oper:='U';


loc_rec(3).loc_id:=3;
loc_rec(3).str_add:='chikhli';
loc_rec(3).p_code:='396521';
loc_rec(3).city:='chikhli';
loc_rec(3).st_pro:='Gujarat';
loc_rec(3).c_id:='G1';
loc_rec(3).Oper:='D';


--names := --namesarray(loc_rec.loc_id,loc_rec.str_add,loc_rec.p_code,
--loc_rec.city,loc_rec.st_pro,loc_rec.c_id,loc_rec.Oper);

LOOP

--total := names.count;

if loc_rec(i).Oper='I' then

insert into locations values(loc_rec(i).loc_id,
loc_rec(i).str_add,
loc_rec(i).p_code,
loc_rec(i).city,
loc_rec(i).st_pro,
loc_rec(i).c_id
);

dbms_output.put_line('Record Inserted Successfully');

elsif loc_rec(i).Oper='D' then

delete from locations where location_id=loc_rec(i).loc_id;
dbms_output.put_line('Record deleted Successfully');

if(sql%NOTFOUND) then
dbms_output.put_line('Input location ID Not Found');
end if;

elsif loc_rec(i).Oper='U' then


update locations 
set 
street_address=loc_rec(i).str_add,
postal_code=loc_rec(i).p_code,
city=loc_rec(i).city,
state_province=loc_rec(i).st_pro,
country_id=loc_rec(i).c_id
where location_id=loc_rec(i).loc_id;

dbms_outt.put_line('Record Updated Successfully');

if(sql%NOTFOUND) then

insert into locations values(loc_rec(i).loc_id,
loc_rec(i).str_add,
loc_rec(i).p_code,
loc_rec(i).city,
loc_rec(i).st_pro,
loc_rec(i).c_id
);

end if;


else
dbms_output.put_line('Kindly give proper Input: I:insert U:update D:delete');
end if;

commit;
i:=i+1;
END LOOP;
end;
/

Answer 1

There are several things wrong with your code.

1. You haven't declared a variable to hold your array
2. You haven't initialized the collection
3. You haven't extended the collection to hold a new row
4. dbms_outt.put_line... typo; I think you mean dbms_output.put_line
5. You're not looping through the collection correctly - you've more or less coded an infinite loop, except eventually you'll run out of items in your collection and will end up with a subscript out of range error eventually.

Your code should be something like:

declare
  type loc is record (loc_id number (4),
                      str_add varchar2 (40),
                      p_code varchar2 (12),
                      city varchar2 (30),
                      st_pro varchar2 (25),
                      c_id char (2),
                      oper varchar2 (1));


  --names namesarray;
  --loc_rec loc IS VARRAY(8);
  type loc_varray is varray (8) of loc;

  total number (4);
  tot number (3);
  loc_id1 number (4);
  loc_rec loc_varray := loc_varray();
begin
  loc_rec.extend;
  loc_rec (1).loc_id := 1;
  loc_rec (1).str_add := 'chikhli';
  loc_rec (1).p_code := '396521';
  loc_rec (1).city := 'chikhli';
  loc_rec (1).st_pro := 'Gujarat';
  loc_rec (1).c_id := 'G1';
  loc_rec (1).oper := 'I';

  loc_rec.extend;
  loc_rec (2).loc_id := 2;
  loc_rec (2).str_add := 'chikhli';
  loc_rec (2).p_code := '396521';
  loc_rec (2).city := 'chikhli';
  loc_rec (2).st_pro := 'Gujarat';
  loc_rec (2).c_id := 'G1';
  loc_rec (2).oper := 'U';

  loc_rec.extend;
  loc_rec (3).loc_id := 3;
  loc_rec (3).str_add := 'chikhli';
  loc_rec (3).p_code := '396521';
  loc_rec (3).city := 'chikhli';
  loc_rec (3).st_pro := 'Gujarat';
  loc_rec (3).c_id := 'G1';
  loc_rec (3).oper := 'D';


  --names := --namesarray(loc_rec.loc_id,loc_rec.str_add,loc_rec.p_code,
  --loc_rec.city,loc_rec.st_pro,loc_rec.c_id,loc_rec.Oper);

  for i in loc_rec.first..loc_rec.last
  loop
    --total := names.count;

    if loc_rec (i).oper = 'I'
    then
      insert Into Locations
      Values      (Loc_Rec (I).Loc_Id,
                   Loc_Rec (I).Str_Add,
                   Loc_Rec (I).P_Code,
                   Loc_Rec (I).City,
                   Loc_Rec (I).St_Pro,
                   Loc_Rec (I).C_Id);

      dbms_output.put_line ('Record Inserted Successfully');
    elsif loc_rec (i).oper = 'D'
    then
      delete from locations
      where       location_id = loc_rec (i).loc_id;

      dbms_output.put_line ('Record deleted Successfully');

      if (sql%notfound)
      then
        dbms_output.put_line ('Input location ID Not Found');
      end if;
    elsif loc_rec (i).oper = 'U'
    then
      UPDATE locations
      SET    street_address = loc_rec (i).str_add,
             postal_code = loc_rec (i).p_code,
             city = loc_rec (i).city,
             state_province = loc_rec (i).st_pro,
             country_id = loc_rec (i).c_id
      WHERE  location_id = loc_rec (i).loc_id;

      dbms_output.put_line ('Record Updated Successfully');

      if (sql%notfound)
      then
        insert into locations
        values      (loc_rec (i).loc_id,
                     loc_rec (i).str_add,
                     loc_rec (i).p_code,
                     loc_rec (i).city,
                     loc_rec (i).st_pro,
                     loc_rec (i).c_id);
      end if;
    else
      dbms_output.put_line (
        'Kindly give proper Input: I:insert U:update D:delete');
    end if;

    commit;
  end loop;
end;
/