CODEWITHSUNDEEP
sqloracle-databasedatabase-administration
Hasan Alizada
Hasan Alizada

Reputation: 591

Oracle built-in datatypes system view

Folks!

Does somebody know an Oracle built-in datatypes system dictionary view?

Example:

SELECT DUMP ('HASAN') FROM DUAL;

returns

Typ=96 Len=5: 72,65,83,65,78

Oracle documentation says that it is CHAR or NCHAR datatype.

But is there a view, where I can find this datatype by code returned (Typ=96)?

Does it exist at all?

Upvotes: 0

Views: 292

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

You can also check source code of DBMS_TYPES package

select TEXT 
from dba_source 
where name = 'DBMS_TYPES' 
    and regexp_like(text, 'TYPECODE');


TYPECODE_DATE            PLS_INTEGER :=  12;
TYPECODE_NUMBER          PLS_INTEGER :=   2;
TYPECODE_RAW             PLS_INTEGER :=  95;
TYPECODE_CHAR            PLS_INTEGER :=  96;
TYPECODE_VARCHAR2        PLS_INTEGER :=   9;
TYPECODE_VARCHAR         PLS_INTEGER :=   1;
TYPECODE_MLSLABEL        PLS_INTEGER := 105;
TYPECODE_BLOB            PLS_INTEGER := 113;
TYPECODE_BFILE           PLS_INTEGER := 114;
TYPECODE_CLOB            PLS_INTEGER := 112;
TYPECODE_CFILE           PLS_INTEGER := 115;
TYPECODE_TIMESTAMP       PLS_INTEGER := 187;
TYPECODE_TIMESTAMP_TZ    PLS_INTEGER := 188;
TYPECODE_TIMESTAMP_LTZ   PLS_INTEGER := 232;
TYPECODE_INTERVAL_YM     PLS_INTEGER := 189;
TYPECODE_INTERVAL_DS     PLS_INTEGER := 190;
TYPECODE_REF             PLS_INTEGER := 110;
TYPECODE_OBJECT          PLS_INTEGER := 108;
TYPECODE_VARRAY          PLS_INTEGER := 247;            /* COLLECTION TYPE */
TYPECODE_TABLE           PLS_INTEGER := 248;            /* COLLECTION TYPE */
TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;
TYPECODE_OPAQUE          PLS_INTEGER := 58;                 /* OPAQUE TYPE */
TYPECODE_NCHAR           PLS_INTEGER := 286;
TYPECODE_NVARCHAR2       PLS_INTEGER := 287;
TYPECODE_NCLOB           PLS_INTEGER := 288;
TYPECODE_BFLOAT          PLS_INTEGER := 100;
TYPECODE_BDOUBLE         PLS_INTEGER := 101;
TYPECODE_UROWID          PLS_INTEGER := 104;

Note, numbers in DBMS_TYPES are related to PL/SQL. However, DUMP is a SQL function which you cannot use in PL/SQL. Thus these numbers are slightly different.

Upvotes: 3

fg78nc
fg78nc

Reputation: 5232

  SELECT t.typecode, name
  FROM sys.type$ t, sys.obj$ o
  WHERE t.toid = o.oid$ and t.typecode = 96;

Upvotes: 4

Boneist
Boneist

Reputation: 23578

There's DBA_TYPEs, which has a predefined column, e.g.:

SELECT * FROM dba_types WHERE predefined = 'YES';

That seems to have a few of the obvious types (e.g. NUMBER, VARCHAR2, etc) in it, but I wouldn't like to say that's exclusive (for example, the ANYDATA datatype has predefined = NO).

Anyway, it ought to give you an idea of where to look, although why you'd want to I'm not sure?

N.B. If you want to see a list of the dictionary tables, you can do:

select * from dictionary;

You can filter as appropriate, if you can't remember the exact table name!

Upvotes: 1

Related Questions