Differences between sys.col $ and sys.coltype $
create table T
(
ID number,
COL1 SYS.XMLTYPE
)XMLType COLUMN COL1 STORE AS CLOB;
select obj#,col#,intcol#,name,type#
from sys.col$
where obj#='98134'
OBJ# COL# INTCOL# NAME TYPE#
------ ---- ---------- ------- ------
98134 1 1 ID 2
98134 2 2 COL1 58
98134 2 3 SYS_NC00003$ 112
select obj#,col#,intcol#
from sys.coltype$
where obj#=98134
OBJ# COL# INTCOL#
------ ---- ----------
98134 2 2
Why does the first result set contain three columns, but the second result set only contains one column. I want to know more about the differences between these two tables.
Thank.
source to share
COL$
- data dictionary table for all columns. These are all the visible columns of the table plus the invisible columns. So the first query returns three rows for ID, COL1 and SYS_NC00003 $. which is offline storage for the XMLTYPE column (which is why COL # is the same but INTCOL # is different).
COLTYPE$
- data dictionary table for columns declared as complex data type. So COL$.TYPE#
for CO11 is 58; this maps to ANYDATE or XMLTYPE data types, which are complex data types. The other two columns are of the NUMBER ( COL$.TYPE# = 2
) and CLOB ( COL$.TYPE# = 112
) data types , which are both Oracle primitives. So why in the requestCOLTYPE$
source to share