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?

On 17.06.2011 17:16, The Magnet wrote:
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

On Jun 17, 12:17*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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

On Jun 17, 3:35*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:

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.