Hi,
CREATE TYPE phone_typ AS OBJECT (
country_code VARCHAR2(2),
area_code VARCHAR2(3),
ph_number VARCHAR2(7));
/
CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
/
CREATE TABLE dept_phone_list (
dept_no NUMBER(5),
phone_list phone_varray_typ);
INSERT INTO dept_phone_list VALUES (
100,
phone_varray_typ( phone_typ ('01', '650', '5550123'),
phone_typ ('01', '650', '5550148'),
phone_typ ('01', '650', '5550192')));
/
INSERT INTO dept_phone_list VALUES (
200,
phone_varray_typ( phone_typ ('02', '750', '5550122'),
phone_typ ('02', '750', '5550141'),
phone_typ ('02', '750', '5550195')));
/
I can execute below query
select * from table(select phone_list from dept_phone_list where dept_no=100)
I can't execute as below Is there a way to do this ?
select * from table(select phone_list from dept_phone_list)
ORA-01427: single-row subquery returns more than one row
Thanks,
Girish
This should help
SQL> CREATE or replace TYPE phone_typ AS OBJECT (
2 country_code VARCHAR2(2),
3 area_code VARCHAR2(3),
4 ph_number VARCHAR2(7));
5 /
Type created.
SQL> CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
2 /
Type created.
SQL> CREATE TABLE dept_phone_list (
2 dept_no NUMBER(5),
3 phone_list phone_varray_typ);
Table created.
SQL>
SQL> INSERT INTO dept_phone_list VALUES (
2 100,
3 phone_varray_typ( phone_typ ('01', '650', '5550123'),
4 phone_typ ('01', '650', '5550148'),
5 phone_typ ('01', '650', '5550192')));
1 row created.
SQL> /
1 row created.
SQL> INSERT INTO dept_phone_list VALUES (
2 200,
3 phone_varray_typ( phone_typ ('02', '750', '5550122'),
4 phone_typ ('02', '750', '5550141'),
5 phone_typ ('02', '750', '5550195')));
1 row created.
SQL> /
1 row created.
SQL>
SQL> select t.dept_no, p.ph_number
2 from dept_phone_list t,
3 table(t.phone_list) p;
DEPT_NO PH_NUMB
---------- -------
100 5550123
100 5550148
100 5550192
100 5550123
100 5550148
100 5550192
200 5550122
200 5550141
200 5550195
200 5550122
200 5550141
200 5550195
12 rows selected.
SQL>