With statement alias problem/10g

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 12 Aug 2005 04:21:41 -0700
Message-ID: <1123845701.117224.249920@o13g2000cwo.googlegroups.com>

All, some Friday afternoon frustration. I have created a script to demonstrate a problem I am currently having and have included it inline below. Despite the clear error message "ORA-00942: table or view does not exist ", I don't understand why it is occurring. Account.orgs is a table. I cannot find anything on metalink, or perhaps I'm not searching on the correct term. Full SQLPlus trace included below. Regards
Mike

TESSELLA Michael.OShea_at_tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL> CONN user1/user1

Connected.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW testView1 AS

  2 WITH
  3 testAlias AS
  4 (

  5      SELECT COUNT(*) X
  6        FROM account.orgs

  7 )
  8 SELECT *
  9 FROM testAlias;

View created.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW testView2 AS

  2 WITH
  3 testAlias AS

  4     (
  5      SELECT 1 X
  6        FROM DUAL

  7 )
  8 SELECT *
  9 FROM testAlias;

View created.

SQL>
SQL>
SQL> GRANT SELECT ON testView1 TO user2;

Grant succeeded.

SQL> GRANT SELECT ON testView2 TO user2;

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CONN user2/user2

Connected.
SQL>
SQL> DESCRIBE user1.testView1

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 X                                                  NUMBER

SQL> DESCRIBE user1.testView2
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 X                                                  NUMBER

SQL>
SQL> SELECT *
  2 FROM user1.testView1;
  FROM user1.testView1

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

SQL>
SQL> SELECT *
  2 FROM user1.testView2;

         X

---

         1

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CONN user1/user1

Connected.
SQL>
SQL> CREATE OR REPLACE VIEW testView1 AS   2 SELECT COUNT(*) X
  3 FROM account.orgs;

View created.

SQL>
SQL> GRANT SELECT ON testView1 TO user2;

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CONN user2/user2

Connected.
SQL>
SQL> DESCRIBE user1.testView1

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 X                                                  NUMBER

SQL>
SQL> SELECT *
  2 FROM user1.testView1;

         X

---

      5745

SQL>
SQL>
SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 -64bi

PL/SQL Release 10.1.0.3.0 - Production

CORE 10.1.0.3.0 Production

TNS for Solaris: Version 10.1.0.3.0 - Production

NLSRTL Version 10.1.0.3.0 - Production         

SQL> 
SQL> 
SQL> spool off

Received on Fri Aug 12 2005 - 06:21:41 CDT