Link to home
Start Free TrialLog in
Avatar of Jay Redd
Jay Redd

asked on

List of all Data Types on Oracle

Hello,
I am trying to make a database that has each data type in oracle, is there a system table that contains them all? I want to left join them to my existing tables metadata and see what ones I need to add.
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Here's the official page on the subject: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT012   You may find you don't really want all of them.
select distinct data_type from all_tab_columns

Open in new window

But as Dave said: go for the official docs ;-)
ALL_TAB_COLUMNS or DBA_TAB_COLUMNS, would only contain the data types that are in use.  They would not cover all possible data types.
I agree with johnsone and on top of that, it will exclude PL/SQL and XML datatypes.
Avatar of Jay Redd
Jay Redd

ASKER

yeah i wanted the ones that are not in use already. what i ended up doing was copying the list from the drop down when you "Edit" a table, inserting that into a table and then left joined it.
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just noticed, that I left the following predicate while testing:
AND OBJECT_NAME like 'PL/SQL%'

Remove it from the query:

SELECT REPLACE(decode(instr(object_name,'PL/SQL'),0,object_name,substr(object_name,8)),' ','_') TYPE
 FROM dba_objects x
WHERE object_type = 'TYPE'
UNION
SELECT regexp_substr(upper(text),'( ?SUBTYPE +"?)([^"]+)("? +IS +.+)',1,1,'i',2) SUBTYPE
 FROM dba_source
WHERE NAME = 'STANDARD'
  AND TYPE = 'PACKAGE'
  AND upper(text) LIKE '%SUBTYPE%'
  AND upper(text) NOT LIKE '%/*%'
UNION
SELECT regexp_substr(text,'(QMTXT_)([^ ]+)',1,1,'i',2) xml_type
 FROM ALL_SOURCE
WHERE OWNER = 'SYS'
  AND TYPE = 'PACKAGE'
  AND NAME = 'DBMS_XQUERYINT'
  AND REGEXP_LIKE(text,'^ QMTXT_.+CONSTANT NUMBER := \d+;$')
/


Thx!!