Create type and use it in inner query : Select « Object Oriented « Oracle PL/SQL Tutorial

SQL> create or replace type myScalarType as object( cnt number, average number )
  2  /

Type created.

SQL>
SQL> select username, a.data.cnt, a.data.average
  2    from (
  3      select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
  4  /

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS                                  6520   5009.74064
SYSTEM                                422   6095.87678
OUTLN                                   7   1172.57143
DIP                                     0
TSMSYS                                  2       8606.5
INV15                                   2      16237.5
DBSNMP                                 46   9592.65217
INV10                                   2      16227.5
CTXSYS                                338   9877.92012
XDB                                   334   10800.7485
ANONYMOUS                               0

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS                                 458   11667.2009
HR                                     34      12104.5
FLOWS_FILES                            11   12717.2727
FLOWS_020100                         1085    12813.424
JAVA2S                                530   16254.6849
INV11                                   2      16229.5
INV12                                   2      16231.5
INV13                                   2      16233.5
INV14                                   2      16235.5
PLSQL                                   0
INV16                                   2      16239.5

USERNAME                         DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17                                   2      16241.5
INV18                                   2      16243.5
INV19                                   2      16245.5
INV20                                   2      16247.5
DEFINER                                 4      16250.5

27 rows selected.

SQL>
SQL>
SQL>

32.11.Select
	32.11.1.	Selecting Rows from the Table with object type column
	32.11.2.	Select an individual column object from a table
	32.11.3.	Querying Rows from the object Table
	32.11.4.	SELECTing Only One Column in the Composite
	32.11.5.	COLUMN Formatting in SELECT for Object
	32.11.6.	ORA-00932: inconsistent datatypes: expected REF
	32.11.7.	Query table column with user defined type
	32.11.8.	Use table function to convert type to a 'table'
	32.11.9.	Create type and use it in inner query
	32.11.10.	Use user-defined type to combine query logic