Re: Is User_tab_columns correct?

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Thu, 5 Nov 98 15:54:51 +0200
Message-ID: <AEhuQGsK42@protasov.kiev.ua>

Hi,

I made additional investigations in the problem. It seems the problem is with sys.coltype$ table. The same column is written two times with different types in it:

SVRMGR> drop table test;
Statement processed.
SVRMGR> drop type t_test2;
Statement processed.
SVRMGR> drop type t_test1;
Statement processed.

SVRMGR>
SVRMGR> create or replace type t_test1 as object
     2> (id integer
     3> );

Statement processed.

SVRMGR>
SVRMGR> create or replace type t_test2 as object
     2> (t2 ref t_test1
     3> );

Statement processed.

SVRMGR>
SVRMGR> create table test
     2> (x t_test2
     3> );

Statement processed.

SVRMGR>
SVRMGR> select column_name,data_type
     2> from user_tab_columns
     3> where table_name='TEST';
COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
X                              T_TEST2
X                              T_TEST1

2 rows selected.

SVRMGR>
SVRMGR> select col#,toid from sys.coltype$
     2> where obj#=(select obj# from sys.obj$
     3>             where owner#=userenv('schemaid')
     4>               and name='TEST');
COL#       TOID
---------- --------------------------------
         1 714449D5749F11D2A7CF008048E3A7FA
         1 714449D2749F11D2A7CF008048E3A7FA

2 rows selected.

Is this a correct behaviour? How to find real type of the column?

Andrew Protasov

> Hi,
>
> I found something interesting with user_tab_columns in
> Oracle 8.0.3. Try this:
>
> SVRMGR>
> SVRMGR> drop table test;
> Statement processed.
> SVRMGR> drop type t_test2;
> Statement processed.
> SVRMGR> drop type t_test1;
> Statement processed.
> SVRMGR>
> SVRMGR> create or replace type t_test1 as object
> 2> (id integer
> 3> );
> Statement processed.
> SVRMGR>
> SVRMGR> create or replace type t_test2 as object
> 2> (t2 ref t_test1
> 3> );
> Statement processed.
> SVRMGR>
> SVRMGR> create table test
> 2> (x t_test2
> 3> );
> Statement processed.
> SVRMGR>
> SVRMGR> select column_name,data_type
> 2> from user_tab_columns
> 3> where table_name='TEST';
> COLUMN_NAME DATA_TYPE
> ------------------------------ ------------------------------
> X T_TEST2
> X T_TEST1
> 2 rows selected.
> SVRMGR>
> SVRMGR> spool off
>
> Column x has two different datatypes. Is this correct?
>
> Andrew Protasov
>
>
>
Received on Thu Nov 05 1998 - 07:54:51 CST