Re: Anyone know nested tables?

From: The Magnet <art_at_unsu.com>
Date: Fri, 17 Jun 2011 13:35:38 -0700 (PDT)
Message-ID: <5a9e2d6a-795f-46c0-8f32-aa81cffe0989_at_28g2000yqu.googlegroups.com>

On Jun 17, 12:17 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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

You know, Oracle makes these so damn hard to work with. Now, I'm trying to get this to work ( I changed some of the names, etc):

CREATE Or Replace TYPE estimate_date_type AS OBJECT (   earnings_estimate_dates DATE);

CREATE Or Replace TYPE nested_estimate_dates AS TABLE OF estimate_date_type;

  OPEN ticker_detail;
  FETCH ticker_detail BULK COLLECT INTO v_read_record;   CLOSE ticker_detail;

 UPDATE TABLE (
    SELECT estimate_dates FROM stock_info_snapshot) est_date   SET VALUE(est_date) = estimate_date_type(SYSDATE,SYSDATE);

SQL> insert into table

(SELECT estimate_dates FROM stock_info_snapshot)
values (estimate_date_type(SYSDATE, SYSDATE));  2    3
values (estimate_date_type(SYSDATE, SYSDATE))
        *

ERROR at line 3:
ORA-02315: incorrect number of arguments for default constructor Received on Fri Jun 17 2011 - 15:35:38 CDT