-- varray2.sql
-- varray - Example of storing 10 records from a table into a varray in Oracle8

DECLARE
   -- tr stands for type record
   type trClient is record 
   (nClientID                 NUMBER(10),
    vcFirstName               VARCHAR2(30),
    vcLastName                VARCHAR2(30)
   );

   -- tv for type of varray of record
   TYPE tvrClient IS VARRAY(10) OF trClient;

   -- v for varray
   vClient                    tvrClient;

   -- 
   iCounter                   integer:=1;

   CURSOR client_cursor IS
   SELECT client_id, first_name, last_name
   FROM	client
   WHERE rownum < 11;

BEGIN
   dbms_output.enable(100000);

   -- The vClient varray must have each of its records initialised
   vClient:=tvrClient(null,null,null,null,null,null,null,null,null,null) ;
   
   FOR client_rec IN client_cursor LOOP
      vClient(iCounter).nClientId:=client_rec.client_id;
      vClient(iCounter).vcFirstName:=client_rec.first_name;
      vClient(iCounter).vcLastName:=client_rec.last_name;
      iCounter:=iCounter+1;
   END LOOP;

   FOR I IN 1..10 LOOP
       dbms_output.put_line(to_char(I)||' '||vClient(I).vcFirstName||' '||
          vClient(I).vcLastName);
   END LOOP;
END ;
/