Re: With statement alias problem/10g

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 12 Aug 2005 08:19:28 -0700
Message-ID: <1123859968.815708.7070@g44g2000cwa.googlegroups.com>

Further to previous postings, I have further confirmation that the issue initially documented is not priv related. The following query, performed two slightly different ways, cleanly shows one working whereas the other does not.

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>
SQL> CONN USER1/USER1

Connected.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW testView3 AS

  2 WITH
  3 testAlias AS
  4 (

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

  7 )
  8 SELECT testAlias.X
  9 FROM testAlias;

View created.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW testView4 AS
  2    SELECT testAlias.X
  3     FROM (
  4           SELECT COUNT(*) X
  5             FROM account.orgs
  6          ) testAlias;

View created.

SQL>
SQL> GRANT SELECT ON testView3 TO USER2;

Grant succeeded.

SQL> GRANT SELECT ON testView4 TO USER2;

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL> CONN USER2/USER2

Connected.
SQL>
SQL> SELECT *
  2 FROM USER1.testView3;
  FROM USER1.testView3

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

SQL>
SQL> SELECT *
  2 FROM USER1.testView4;

         X

---

      5745

SQL> SELECT * 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 Received on Fri Aug 12 2005 - 10:19:28 CDT