Re: Anyone know nested tables?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 17 Jun 2011 19:17:39 +0200
Message-ID: <4DFB8C33.5000204_at_gmail.com>

On 17.06.2011 17:16, The Magnet wrote:
> Trying this example to give me a start on creating my table with the
> error below:
>
>
> CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15));
> /
> CREATE TYPE Roster AS TABLE OF Worker;
> /
> CREATE TABLE teams (team_no NUMBER, members Roster)
> NESTED TABLE members STORE AS teams_store;
>
>
> DECLARE
> CURSOR crs IS
> SELECT * FROM teams;
>
> TYPE v_teams_table IS TABLE OF crs%ROWTYPE INDEX BY BINARY_INTEGER;
>
> v_teams v_teams_table;
>
> BEGIN
> INSERT INTO teams VALUES (1, Roster(
> Worker('Paul Ocker', 'Accounting'),
> Worker('Gail Chan', 'Sales'),
> Worker('Marie Bello', 'Operations'),
> Worker('Alan Conwright', 'Research')));
>
>
> OPEN crs;
> FETCH crs BULK COLLECT INTO v_teams;
> CLOSE crs;
>
> FOR x IN v_teams.first .. v_teams.last LOOP
> DBMS_OUTPUT.PUT_LINE('HERE: ' || v_teams(x).team_no);
> DBMS_OUTPUT.PUT_LINE('HERE: ' || v_teams(x).members.dept);
> END LOOP;
> END;
> /
>
>
>
> ERROR at line 23:
> ORA-06550: line 23, column 57:
> PLS-00302: component 'DEPT' must be declared
> ORA-06550: line 23, column 5:
> PL/SQL: Statement ignored
>
>
>
> I've tried all the different fields with no luck. Anyone see what I
> am missing?

Your column teams.members is a collection, so you have to iterate over it - so you need a nested loops to iterate firstly over result set itself and secondly over the nested table entries.

SQL> declare

   2 cursor crs is
   3 select * from teams;
   4
   5 type v_teams_table is table of crs%rowtype index by binary_integer;    6
   7 v_teams v_teams_table;
   8
   9 begin
  10 insert into teams
  11 values

  12      (1,
  13       roster(worker('Paul Ocker', 'Accounting'),
  14              worker('Gail Chan', 'Sales'),
  15              worker('Marie Bello', 'Operations'),
  16              worker('Alan Conwright', 'Research')));

  17
  18 open crs;
  19 fetch crs bulk collect
  20 into v_teams;
  21 close crs;
  22
  23 for x in v_teams.first .. v_teams.last loop

  24      dbms_output.put_line('HERE: ' || v_teams(x).team_no);
  25      for y in v_teams(x).members.first .. v_teams(x).members.last loop
  26        dbms_output.put_line('HERE: ' || v_teams(x).members(y).dept);
  27      end loop;

  28 end loop;
  29 end;
  30 /

HERE: 1
HERE: Accounting
HERE: Sales
HERE: Operations
HERE: Research

PL/SQL procedure successfully completed.

Best regards

Maxim Received on Fri Jun 17 2011 - 12:17:39 CDT