Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create view v (a, b) as select 1, 2 from dual;
create or replace package p as
  type t is table of v%rowtype;
end p;
/
select o.object_name, o.object_type, s.line, s.text
from all_objects o
join all_source s on o.owner = s.owner and o.object_name = s.name
where o.owner = user
and o.object_name like 'SYS_PLSQL_%'
order by 1;
select type_name, elem_type_name 
from all_coll_types 
where owner = user and type_name like 'SYS_PLSQL_%'
union all
select type_name, null as elem_type_name 
from all_types 
where owner = user and type_name like 'SYS_PLSQL_%';
select pt.*
from all_plsql_types pt
where pt.owner = user;


OWNER
TYPE_NAME
PACKAGE_NAME
TYPE_OID
TYPECODE
ATTRIBUTES
CONTAINS_PLSQL


FIDDLE_BWLPUYJGQTHPPWCWTYVY
T
P
0xC079D035A6793EDDE055000000000001
COLLECTION
0
YES


create or replace package p as
  type t is table of v%rowtype;
  procedure p (v_t in out t);
end p;
/
select o.object_name, o.object_type, s.line, s.text
from all_objects o
join all_source s on o.owner = s.owner and o.object_name = s.name
where o.owner = user
and o.object_name like 'SYS_PLSQL_%'
order by 1;
select type_name, elem_type_name 
from all_coll_types 
where owner = user and type_name like 'SYS_PLSQL_%'
union all
select type_name, null as elem_type_name 
from all_types 
where owner = user and type_name like 'SYS_PLSQL_%';
select pt.*
from all_plsql_types pt
where pt.owner = user;


OWNER
TYPE_NAME
PACKAGE_NAME
TYPE_OID
TYPECODE
ATTRIBUTES
CONTAINS_PLSQL


FIDDLE_BWLPUYJGQTHPPWCWTYVY
T
P
0xC079D035A6793EDDE055000000000001
COLLECTION
0
YES


create or replace package p as
  type t is table of v%rowtype;
  function f return t pipelined;
end p;
/
select o.object_name, o.object_type, s.line, s.text
from all_objects o
join all_source s on o.owner = s.owner and o.object_name = s.name
where o.owner = user
and o.object_name like 'SYS_PLSQL_%'
order by 1;


OBJECT_NAME
OBJECT_TYPE
LINE
TEXT


SYS_PLSQL_1EA01E34_9_1
TYPE
1
type                               SYS_PLSQL_1EA01E34_9_1 as table of "FIDDLE_BWLPUYJGQTHPPWCWTYVY"."SYS_PLSQL_415546E4_15_1";

SYS_PLSQL_1EA01E34_DUMMY_1
TYPE
1
type                               SYS_PLSQL_1EA01E34_DUMMY_1 as table of number;

SYS_PLSQL_415546E4_15_1
TYPE
1
type                               SYS_PLSQL_415546E4_15_1 as object ("A" NUMBER,


SYS_PLSQL_415546E4_15_1
TYPE
2
"B" NUMBER);

SYS_PLSQL_415546E4_DUMMY_1
TYPE
1
type                               SYS_PLSQL_415546E4_DUMMY_1 as table of number;


select type_name, elem_type_name 
from all_coll_types 
where owner = user and type_name like 'SYS_PLSQL_%'
union all
select type_name, null as elem_type_name 
from all_types 
where owner = user and type_name like 'SYS_PLSQL_%';


TYPE_NAME
ELEM_TYPE_NAME


SYS_PLSQL_1EA01E34_9_1
SYS_PLSQL_415546E4_15_1

SYS_PLSQL_1EA01E34_DUMMY_1
NUMBER

SYS_PLSQL_415546E4_DUMMY_1
NUMBER


select pt.*
from all_plsql_types pt
where pt.owner = user;


OWNER
TYPE_NAME
PACKAGE_NAME
TYPE_OID
TYPECODE
ATTRIBUTES
CONTAINS_PLSQL


FIDDLE_BWLPUYJGQTHPPWCWTYVY
T
P
0xC079D035A6793EDDE055000000000001
COLLECTION
0
YES


create or replace package body p as
  function f return t pipelined is
    r v%rowtype;
  begin
    r.a := 9;
    r.b := 8;
    pipe row (r);
  end;
end p;
/
select type_name, elem_type_name 
from all_coll_types 
where owner = user and type_name like 'SYS_PLSQL_%'
union all
select type_name, null as elem_type_name 
from all_types 
where owner = user and type_name like 'SYS_PLSQL_%';


TYPE_NAME
ELEM_TYPE_NAME


SYS_PLSQL_1EA01E34_9_1
SYS_PLSQL_415546E4_15_1

SYS_PLSQL_1EA01E34_DUMMY_1
NUMBER

SYS_PLSQL_415546E4_DUMMY_1
NUMBER


select * from table(p.f);


A
B


9
8


declare
  r v%rowtype;
begin
  select * into r from table(p.f);
end;
/
1 rows affected

OBJECT_NAME	OBJECT_TYPE	LINE	TEXT
SYS_PLSQL_1EA01E34_9_1	TYPE	1	type SYS_PLSQL_1EA01E34_9_1 as table of "FIDDLE_BWLPUYJGQTHPPWCWTYVY"."SYS_PLSQL_415546E4_15_1";
SYS_PLSQL_1EA01E34_DUMMY_1	TYPE	1	type SYS_PLSQL_1EA01E34_DUMMY_1 as table of number;
SYS_PLSQL_415546E4_15_1	TYPE	1	type SYS_PLSQL_415546E4_15_1 as object ("A" NUMBER,
SYS_PLSQL_415546E4_15_1	TYPE	2	"B" NUMBER);
SYS_PLSQL_415546E4_DUMMY_1	TYPE	1	type SYS_PLSQL_415546E4_DUMMY_1 as table of number;