Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: January 08, 2025 - 11:29 am UTC

Last updated: January 14, 2025 - 4:23 am UTC

Version: Oracle Database 19c EE - 19.0.0.0.0

Viewed 100+ times

You Asked

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

and Connor said...

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>


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library