?
Solved

List of all Data Types on Oracle

Posted on 2014-03-31
7
Medium Priority
?
624 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 84

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

765 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