Solved

List of all Data Types on Oracle

Posted on 2014-03-31
7
606 Views
Last Modified: 2014-04-01
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.
0
Comment
Question by:Jay Redd
7 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39967700
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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39967756
select distinct data_type from all_tab_columns

Open in new window

But as Dave said: go for the official docs ;-)
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39967777
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:paquicuba
ID: 39967806
I agree with johnsone and on top of that, it will exclude PL/SQL and XML datatypes.
0
 

Author Comment

by:Jay Redd
ID: 39967823
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.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 39969453
A query like the one below, should be close to getting all data types, subtypes and xml types used in your database:

SELECT REPLACE(decode(instr(object_name,'PL/SQL'),0,object_name,substr(object_name,8)),' ','_') TYPE
 FROM dba_objects x
WHERE object_type = 'TYPE'
AND OBJECT_NAME like 'PL/SQL%'
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+;$')
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39970000
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!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now