?
Solved

List of all Data Types on Oracle

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

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

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 …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

598 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