Advanced Rewrite Challenge

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 02 Aug 2006 14:01:45 -0700
Message-ID: <1154552506.244827@bubbleator.drizzle.com>

Take a look at these two SQL statements:

1. SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = .srvr_id;
2. SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE i.srvr_id = s.srvr_id;

They both run. Then add advanced rewrite to the mixture.

BEGIN
   dbms_advanced_rewrite.declare_rewrite_equivalence('UW','SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id', 'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE s.srvr_id = i.srvr_id)', TRUE, 'TEXT_MATCH'); END;
/

In TEXT_MATCH mode it works.
In GENERAL mode it works.
But choose RECURSIVE and you get the following:

---

SQL> SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE i.srvr_id = s.srvr_id;
SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE i.srvr_id = s.srvr_id

                                               *

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

---

Sorry Oracle but that table does exist. Has anyone seen this before? I know what it is doing ... what I'm trying to figure out is why before I report it. Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Aug 02 2006 - 16:01:45 CDT