Re: Anyone know nested tables?

From: The Magnet <art_at_unsu.com>
Date: Fri, 17 Jun 2011 13:51:44 -0700 (PDT)
Message-ID: <054b38f7-34b5-4b82-bca8-f4291dd1cc4f_at_m17g2000yqm.googlegroups.com>

On Jun 17, 3:35 pm, The Magnet <a..._at_unsu.com> wrote:
> 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

Actually I think I am going to give up on this. I mean, Oracle just makes to so difficult. All I want:

I have a cursor and a type defined as ROWTYPE of that cursor where the nested table is part of that. BULK COLLECT into that record type, and then update those nested table elements. How card can it be?

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;

CREATE TABLE DATA_HOLDER.STOCK_INFO_SNAPSHOT (

  TICKER                     VARCHAR2(15 BYTE),
  M_TICKER                   VARCHAR2(10 BYTE),
  HIGH_52W                   NUMBER,
  LOW_52W                    NUMBER,
  VOLUME                     NUMBER,
  estimate_dates              nested_estimate_dates

)
NESTED TABLE estimate_dates STORE AS nested_earnings_dates

CURSOR ticker_detail IS
  SELECT mt.ticker, mt.m_ticker,

         DECODE(sd.high_52w,       -9999, NULL, -99999, NULL,
sd.high_52w)        high_52w,
         DECODE(sd.low_52w,        -9999, NULL, -99999, NULL,
sd.low_52w)         low_52w,
         DECODE(sd.volume,         -9999, NULL, -99999, NULL,
sd.volume)          volume,
         NULL                   estimate_dates

   FROM stock_data sd;

TYPE v_ticker_table IS TABLE OF ticker_detail%ROWTYPE INDEX BY
BINARY_INTEGER;
v_read_record    v_ticker_table;

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

  FORALL y IN v_read_record.FIRST .. v_read_record.LAST     INSERT INTO stock_info_snapshot VALUES v_read_record(y);

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

  COMMIT; I mean really Oracle, why will that not work. I've been at this 9 hours already and have no progress at all. Received on Fri Jun 17 2011 - 15:51:44 CDT