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.
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.
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just noticed, that I left the following predicate while testing:
Remove it from the query:
SELECT REPLACE(decode(instr(objec t_name,'PL /SQL'),0,o bject_name ,substr(ob ject_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!!
AND OBJECT_NAME like 'PL/SQL%'
Remove it from the query:
SELECT REPLACE(decode(instr(objec
FROM dba_objects x
WHERE object_type = 'TYPE'
UNION
SELECT regexp_substr(upper(text),
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
FROM ALL_SOURCE
WHERE OWNER = 'SYS'
AND TYPE = 'PACKAGE'
AND NAME = 'DBMS_XQUERYINT'
AND REGEXP_LIKE(text,'^ QMTXT_.+CONSTANT NUMBER := \d+;$')
/
Thx!!