Get data type by ID

oracle

I was looking at this answer for checking the data type of a value. It's quite handy and spits out something that looks kind of like this:

Typ=1 Len=12: 48,48,48,48,48,48,49,49,49,49,49,49

The part that bugs me is that Typ=# part. I can look up what it means in the table mentioned by the documentation, but as that's a web page, I can't query it on the fly.

I can pretty easily parse the number out of that output:

SELECT TO_NUMBER(REGEXP_REPLACE('Typ=1 Len=12: 48,48,48,48,48,48,49,49,49,49,49,49', 'Typ=([0-9]+).*', '\1')) AS TYPE_ID FROM DUAL

But is there anywhere I can query what type that ID number goes with? I'd like to JOIN the type name to this dump output.

Best Answer

The typecodes of built-in types are hidden in the dictionary views, but you can query the dictionary directly and find the type based on the OID:

select
  type_name
from
  dba_types dt
  join sys.type$ t on t.tvoid = dt.type_oid
where
  t.typecode = 1
;

NAME
----------
VARCHAR
Related Question