Re: diffrence between Select * from tab and Cat

From: AnySQL (d.c.b.a) <anysql_at_gmail.com>
Date: 27 May 2005 06:34:21 -0700
Message-ID: <1117200861.038524.142070@g49g2000cwa.googlegroups.com>

Some old application will use cat to get the object list, and still have the following difference in definition.

SQL> desc tab

NO# NAME                           NULLABLE TYPE
--- ------------------------------ -------- ------------
  1 TNAME                          NOT NULL VARCHAR2(30)
  2 TABTYPE                                 VARCHAR2(7)
  3 CLUSTERID                               NUMBER

SQL> desc CAT

NO# NAME                           NULLABLE TYPE
--- ------------------------------ -------- ------------
  1 TABLE_NAME                     NOT NULL VARCHAR2(30)
  2 TABLE_TYPE                              VARCHAR2(11)

SQL> SOURCE TAB select o.name,

      decode(o.type#, 2, 'TABLE', 3, 'CLUSTER',
             4, 'VIEW', 5, 'SYNONYM'), t.tab#

  from sys.tab$ t, sys.obj$ o
  where o.owner# = userenv('SCHEMAID')

  and o.type# >=2
  and o.type# <=5
  and o.linkname is null
  and o.obj# = t.obj# (+)

SQL> SOURCE CAT select o.name,

       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

'UNDEFINED')

from sys.obj$ o
where o.owner# = userenv('SCHEMAID')
  and ((o.type# in (4, 5, 6))

       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested
tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))

  and o.linkname is null Received on Fri May 27 2005 - 08:34:21 CDT