create view problem

From: Dmitry Melekhov <dm_at_belkam.com>
Date: 10 Jan 2002 05:00:53 -0800
Message-ID: <48f1f058.0201100500.6a777142@posting.google.com>

I want to create view oracle 8.1.7.0 from user system

Error:

SQL> CREATE VIEW SEL_USERS_ROLES
(USER_NAME,NAME_ROLE,GRANTED_ROLE,ADM_OPT,DEF_ROLE) AS   2 SELECT aaa.*,

  3     decode(NVL(grt.GRANTED_ROLE,'*'),'*',0,1) GRANTED_ROLE,
  4     decode(NVL(grt.ADMIN_OPTION,'*'),'*',0,'YES',1,'NO',0,1) ADM_OPT,
  5     decode(NVL(grt.DEFAULT_ROLE,'*'),'*',0,'YES',1,'NO',0,1) DEF_ROLE

  6 FROM
  7 (
  8 SELECT usr.USERNAME USER_NAME, rol.ROLE NAME_ROLE   9 FROM DBA_ROLES rol, ALL_USERS usr  10 WHERE rol.ROLE LIKE 'TEL_%'
 11 ) aaa,
 12 DBA_ROLE_PRIVS grt
 13 WHERE

 14      grt.GRANTEE(+) = aaa.user_name
 15      AND  grt.GRANTED_ROLE(+) = aaa.name_role

 16 ;
 FROM DBA_ROLES rol, ALL_USERS usr

         *
ERROR at line 9:
ORA-00942: table or view does not exist

But:

SQL> SELECT aaa.*,

  2     decode(NVL(grt.GRANTED_ROLE,'*'),'*',0,1) GRANTED_ROLE,
  3     decode(NVL(grt.ADMIN_OPTION,'*'),'*',0,'YES',1,'NO',0,1) ADM_OPT,
  4     decode(NVL(grt.DEFAULT_ROLE,'*'),'*',0,'YES',1,'NO',0,1) DEF_ROLE

  5 FROM
  6 (
  7 SELECT usr.USERNAME USER_NAME, rol.ROLE NAME_ROLE   8 FROM DBA_ROLES rol, ALL_USERS usr   9 WHERE rol.ROLE LIKE 'TEL_%'
 10 ) aaa,
 11 DBA_ROLE_PRIVS grt
 12 WHERE

 13      grt.GRANTEE(+) = aaa.user_name
 14      AND  grt.GRANTED_ROLE(+) = aaa.name_role

 15 ;

no rows selected

Why? Received on Thu Jan 10 2002 - 07:00:53 CST