Synthetic TABLE types show up in ALL_COLL_TYPES, but OBJECT types don't show in ALL_TYPES in Oracle 18c

user_521799 0
Viewed 160

When creating the following:

create view v (a, b) as select 1, 2 from dual;

create or replace package p as
  type t is table of v%rowtype;
  function f return t pipelined;
end p;
/

I can see some synthetic types show up in the dictionary:

select o.object_name, 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 = 'TEST' 
and o.object_name like 'SYS_PLSQL_%'
order by 1;

Resulting in:

|OBJECT_NAME               |LINE|TEXT                                                                            |
|--------------------------|----|--------------------------------------------------------------------------------|
|SYS_PLSQL_3473F824_9_1    |1   |type        SYS_PLSQL_3473F824_9_1 as table of "TEST"."SYS_PLSQL_56AACD46_15_1";|
|SYS_PLSQL_3473F824_DUMMY_1|1   |type        SYS_PLSQL_3473F824_DUMMY_1 as table of number;                      |
|SYS_PLSQL_56AACD46_15_1   |1   |type        SYS_PLSQL_56AACD46_15_1 as object ("A" NUMBER,                      |
|SYS_PLSQL_56AACD46_15_1   |2   |"B" NUMBER);                                                                    |
|SYS_PLSQL_56AACD46_DUMMY_1|1   |type        SYS_PLSQL_56AACD46_DUMMY_1 as table of number;                      |

These all appear in ALL_OBJECTS, but only the collection types also appear in ALL_COLL_TYPES. The OBJECT type is not to be found in ALL_TYPES:

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

Resulting in

|TYPE_NAME                 |ELEM_TYPE_NAME         |
|--------------------------|-----------------------|
|SYS_PLSQL_3473F824_9_1    |SYS_PLSQL_56AACD46_15_1|
|SYS_PLSQL_3473F824_DUMMY_1|NUMBER                 |
|SYS_PLSQL_56AACD46_DUMMY_1|NUMBER                 |

Is this a bug in the definition of ALL_TYPES, or is there a good reason for the SYS_PLSQL_56AACD46_15_1 not to be listed? After all, it appears elsewhere, specifically referenced from ALL_COLL_TYPES.ELEM_TYPE_NAME for SYS_PLSQL_3473F824_9_1

I'm using Oracle Database 18c Express Edition Release 18.0.0.0.0

oracle18c information-schema plsql oracle
1 Answers

Possibly just because it doesn't need to be. The collection type can be referenced, indirectly, from SQL:

select * from table(p.f);

But the object type can't.

The types don't appear in all_objects, and the collection type doesn't appear in all_coll_types, if you only declare it or only use it in a procedure:

create or replace package p as
  type t is table of v%rowtype;
end p;
/

or

create or replace package p as
  type t is table of v%rowtype;
  procedure p (v_t in out t);
end p;
/

With both of those package specs, your queries return zero rows. Once you add a public reference that might be called from a SQL context, i.e. a function returning that type, the data dictionary needs to know about it.

But you can't refer to the element type of the collection directly - if you have a function returning v%rowtype then that doesn't need to be a synthetic type of its own, and variable of that type is compatible with the synthetic element type.

So SQL needs to be able to handle the collection type, and needs to see it in all_coll_types to do that (presumably - who knows what's happening under the hood); and the link between the collection type and the record type is there via elem_type_name. The record type can't be used, so doesn't need to be listed in all_types - which, as the documentation says, is only for object types, so record types wouldn't really fit there anyway.

Similarly, the collection type appears in all_plsql_types as soon as it is declared, even if it isn't used; but the element (record) type never appears there, since it can't be referred to.

db<>fiddle

user_2663040
Related