Hi All, nested table example, what's wrong with this?

From: Trying to learn <learn_at_learn.com>
Date: Wed, 21 Jul 2004 07:48:30 -0600
Message-ID: <10fsp9pcraenj5c@corp.supernews.com>

Hi All:

thanks in advance for any help..

I'm going thru the O'reilly Mastering Oracle SQL book by Mishra. I am using Oracle 9i for all of these examples. The book mentions specifics between 9i and 10g so I know when to expect something not to work due to being 10g. Excellent book.

However, when I try the following query (verbatim from the book and it does not mention that it's 10g only so I suspect this should work with 9i):

select co.order_nbr, co.cust_nbr, co.order_dt from cust_order_c co where co.order_items = line_item_tbl(
 line_item_obj('A675-015', 25), line_item_obj('GX5-2786-A2', 1), line_item_obj('X378-9JT-2', 3)
);

I get the following error:
ORA-22904: invalid reference to a nested table column.

The cust_order_c table is described as following: CREATE TABLE CUST_ORDER_C
(

  ORDER_NBR     NUMBER(8)                       NOT NULL,
  CUST_NBR      NUMBER(6)                       NOT NULL,
  SALES_EMP_ID  NUMBER(6)                       NOT NULL,
  ORDER_DT      DATE                            NOT NULL,

  SALE_PRICE NUMBER(9,2),
  ORDER_ITEMS LINE_ITEM_TBL
)
NESTED TABLE ORDER_ITEMS STORE AS ORDER_ITEMS_TABLE My types are as follows:

type line_item_obj as object(
part_nbr varchar2(20),
quantity number(8,2),
map member function to_string return varchar2);

type body line_item_obj as
 map member function to_string return varchar2 is begin

   return part_nbr||':'||to_char(quantity, '00009'); end to_string;
end;

type line_item_tbl as table of line_item_obj;

I inserted 2 records as follows:
insert into cust_order_c(order_nbr, cust_nbr, sales_emp_id, order_dt, sale_price, order_items)
values(1001, 9679, 275, to_date('15-dec-2003', 'dd-mon-yyyy'), 8645,

       line_item_tbl(line_item_obj('A675-015', 25), line_item_obj('TX-475-A3', 7))

       );

insert into cust_order_c(order_nbr, cust_nbr, sales_emp_id, order_dt, sale_price, order_items)
values(1000, 9568, 275, to_date('21-mar-2001', 'dd-mon-yyyy'), 15753,

       line_item_tbl(line_item_obj('A675-015', 25),
       line_item_obj('GX5-2786-A2', 1),
       line_item_obj('X378-9JT-2', 3)));

Received on Wed Jul 21 2004 - 08:48:30 CDT