Solved

List of all Data Types on Oracle

Posted on 2014-03-31
7
615 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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