CREATE OR REPLACE TYPE cust_address_t
AS OBJECT (
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR(2));
/
CREATE OR REPLACE TYPE address_book_t AS TABLE OF cust_address_t;
CREATE TABLE cust_address (
custno NUMBER(10),
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(2),
country_id VARCHAR2(2))
INSERT INTO cust_address
VALUES (1,'123 Main St.','98040','Mercer Island','WA','US');
INSERT INTO cust_address
VALUES (2,'1 Broadway','10202','New York','NY','US');
INSERT INTO cust_address
VALUES (3,'2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN');
COMMIT;
CREATE TABLE cust_short (
custno NUMBER(10),
name VARCHAR2(30)
);
CREATE OR REPLACE TYPE cust_short_type AS OBJECT (
custno NUMBER(10),
name VARCHAR2(30)
v_address_book_t address_book_t);
INSERT INTO cust_short VALUES (1,'Morgan');
INSERT INTO cust_short VALUES (2,'Cline');
INSERT INTO cust_short VALUES (3,'Scott');
***************Here is end of object made************************
Now using following query we retrive data
select cust_short_type(c.custno,c.name,
cast(multiset(select a.street_address,a.postal_code,a.city,a.state_prov ince,a.country_id
from cust_address a where a.custno=c.custno) as address_book_t))
from cust_short c ;
But using anonomous block we have to retrive field which is inside nested table address_book_t
AND this
address_book_t is inside object cust_short_type
Please go through the following block and try to retrive field street_address
which is inside address_book_t...I am getting error using following code.Please help
declare
v_cust_short cust_short_type;
v_address_book_t address_book_t;
begin
select cust_short_type(c.custno,c.name,
cast(multiset(select a.street_address,a.postal_code,a.city,a.state_prov ince,a.country_id
from cust_address a where a.custno=c.custno) as address_book_t)) into
v_cust_short,v_address_book_t
from cust_short c where c.custno=1;
dbms_output.put_line(cust_short_type.name);
FOR I IN v_address_book_t.FIRST..v_address_book_t.LAST LOOP
--dbms_output.put_line(v_address_book_t(I).v_cust_sh ort);
--dbms_output.put_line(v_address_book_t(i).street_ad dress);
END LOOP;
end;
/