Connor McDonald wrote:
> DA Morgan wrote:
>> Take a look at these two SQL statements:
>>
>> A) SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id
>> = .srvr_id;
>>
>> B) 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
>
> if you whack a trace on, does the -942 pop up against a recursive query
> or against your original SQL ?
Problem solved. DBMS_ADVANCED_REWRITE will only work with specific
NLS Character Sets.
I have resolved the matter and posted the solution in Morgan's Library
at www.psoug.org under the title: "Character Sets."
But please be aware that following these directions MAY cause data loss
depending on starting character set and what has been stored.
Also, though it wasn't my issue, be aware that 10gR2, by default, comes
with query_rewrite_integrity = 'ENFORCED'. This must be changed to
query_rewrite_integrity = 'TRUSTED'.
Thanks Connor for your suggestion but, as you can see, it didn't get
me there by itself. However I am pleased to report that the metalink
support was fast and competent. A very pleasant turn of events.
--
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 Tue Aug 08 2006 - 11:32:02 CDT
