Anyone know nested tables?

From: The Magnet <art_at_unsu.com>
Date: Fri, 17 Jun 2011 08:16:09 -0700 (PDT)
Message-ID: <a73f7a11-be28-4e98-bcb7-04652aafeea5_at_v10g2000yqn.googlegroups.com>

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? Received on Fri Jun 17 2011 - 10:16:09 CDT