Re: Wield create view error, need help !!!!!

aaa wrote:

> I have ORACLE 8.1.7 on SUn Server. I have wield problem on create view. If I
> only use "select ..", I don't have error. But if I use "createor replace view
> ..select ...", I got error and I have DBA right. The statement are:
>
> SQL> CREATE OR REPLACE VIEW DBE_SYNONYMS ( OWNER,
> 2 SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK,
> 3 CREATED, STATUS ) AS select u.name, o.name, s.owner, s.name, s.node,
> o.ctime,
> 4 from sys.user$ u, sys.syn$ s, sys.obj$ o
> 5 where o.obj# = s.obj#
> 6 and o.type#=5
> 7 and o.owner#=u.user#
> 8 .
> SQL> /
> from sys.user$ u, sys.syn$ s, sys.obj$ o
> *
> ERROR at line 4:
> ORA-00942: table or view does not exist
>
> SQL> select u.name, o.name, s.owner, s.name, s.node, o.ctime, o.status
> 2 from sys.user$ u, sys.syn$ s, sys.obj$ o
> 3 where o.obj# = s.obj#
> 4 and o.type#=5
> 5 and o.owner#=u.user#
> 6 .
> SQL> /
>
> NAME NAME
> ------------------------------ ------------------------------
> OWNER NAME
> ------------------------------ ------------------------------
> NODE
> --------------------------------------------------------------------------------
> CTIME STATUS
> --------- ----------
> PUBLIC DUAL
> SYS DUAL
>
> 20-SEP-02 1
>
> Does anyone know why?
>
> Thanks.

You don't have appropriate privileges to access the underlying data dictionary tables.

But why are you reinventing the wheel? What's wrong with DBA_SYNONYMS?

Daniel Morgan Received on Thu Oct 24 2002 - 10:29:00 CDT