What is the difference between column_name and qualified_col_name?

Question

The following query on the standard view user_tab_col:

select * from user_tab_cols;

returns on Oracle the columns column_name and qualified_col_name.

What is the difference?

Answer 1

qualified_col_name indicates full column path for XML tables. Basically it stores the expression for this column. For regular columns it will be equal to column_name.

If you run:

select owner, table_name, column_name, data_type, qualified_col_name
  from all_tab_cols
 where column_name <> qualified_col_name;

you will see returned columns from XML tables in XDB schema.

For example:

OWNER  TABLE_NAME       COLUMN_NAME   DATA_TYPE           QUALIFIED_COL_NAME
XDB    XDB$SIMPLE_TYPE  SYS_NC00074$  XDB$APPINFO_LIST_T  "XMLDATA"."RESTRICTION"."MIN_INCLUSIVE"."ANNOTATION"."APPINFO"

Update: For object tables qualified_col_name stores expression of type casting and accessing to attribute. For example:

create or replace type test1_obj as object(
   n1 number,
   n2 number,
   s1 varchar2(10),
   s2 varchar2(20)
)
not final;

create or replace type test2_obj under test1_obj(
  d1 date,
  d2 date
)
not final;

create table object_table of test1_obj;

select column_name, data_type, qualified_col_name 
  from user_tab_cols
where table_name = 'OBJECT_TABLE'
 order by internal_column_id;

For last 2 hidden system columns reserved for instances of type test2_obj we can see:

COLUMN_NAME     DATA_TYPE   QUALIFIED_COL_NAME
SYS_NC00010$    DATE        TREAT(SYS_NC_ROWINFO$ AS "TEST"."TEST2_OBJ")."D1"
SYS_NC00011$    DATE        TREAT(SYS_NC_ROWINFO$ AS "TEST"."TEST2_OBJ")."D2"