Re: Is User_tab_columns correct?

Hi,

I found the solution of the problem. In Oracle 8.0.3 view user_tab_columns has a bug. They join table coltype$ using columns obj#,col# instead of obj#,intcol#. This problem is already solved in Oracle 8.0.4.

Andrew Protasov

> 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 Sat Nov 14 1998 - 12:39:19 CST