Re: Oracle TYPE# to human readable name

Hi Stefan,

Perhaps you just need dba_types?
Anyway, you can use the following queries to find any type:

1. You can find every sys.obj$.type# in the same table by obj#: select o.type#,t.* from sys.obj$ o ,sys.obj$ t where o.type#=t.obj#; -- or
   • ,dba_objects t where o.type#=t.object_id;
2. Oracle built-in datatypes: select * from dba_types where owner is null; or select typecode, owner#, name from sys.type$ t,sys.obj$ o where o.oid$ = t.tvoid and bitand(t.properties, 64) = 64 -- u.name is null order by typecode;
3. If you you want to find source code of objects and collections even for automatically generated types from packages: select u.name as owner, o.obj# , o.owner# , o.name , o.namespace, decode(t.typecode, 108, 'OBJECT', 122, 'COLLECTION', o.name) type_name, -- c.* (select listagg(source) within group(order by line) from sys.source$ s where o.obj#=s.obj#) src from sys.obj$ o, sys.user$ u,sys.type$ t,sys.collection$ c where 1=1 and o.owner#=u.user# and o.oid$ = t.tvoid(+) and c.toid(+) = t.tvoid and o.flags=1048576 and o.type# in (10,13) /

On Sat, Dec 23, 2017 at 3:50 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> Hi all
>
> Does anyone know of a structure (table, fixed table, anything) that has a
> mapping of all the object types known to the present Oracle version?
>
> What I'm looking for is basically a list, a mapping, of all possible
> values of obj$.type# and their human-readable counterpart. If you look at
> the definition of the DBA_OBJECTS view, it's basically in there with a
> DECODE() on type#. But I'd rather refrain from parsing that if I can help
> it.
>
> Is this data available anywhere within Oracle?
>
> Cheers
>
>
>
> Stefan
>
>
>
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat
> | _at_zztat_oracle
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l