[materialized view] ORA-12054: cannot set the ON COMMIT refresh attribute...
Date: Sat, 1 Jun 2002 15:20:22 +0300
Message-ID: <adai1u$va3md$1_at_ID-114658.news.dfncis.de>
Hi all,
[Oracle AS 9i, JDeveloper]
I am developing a web app in which I am intending to implement
a search results "Page by Page Iterator" pattern i.e. when a search
query is requested against the DB I show pages by groups of X rows, each
"move to new page" request derives a new query requesting the
next Nth X rows.
Since the query is quite complex using INNER JOINS I had no chance to use the way of getting the Nth X rows of the view because had no ROWID, I was trying to do it like this as example:
SELECT *
FROM FOGVW_SEARCHCUSTOMERS
WHERE rowid in (
SELECT rowid FROM FOGVW_SEARCHCUSTOMERS
WHERE rownum <= 10
MINUS
SELECT rowid FROM FOGVW_SEARCHCUSTOMERS
WHERE rownum < 1)
I get this error:
"ORA-01445: cannot select ROWID from a join view without a key-preserved
table."
Were the view is defined like this:
CREATE OR REPLACE VIEW FOGVW_SEARCHCUSTOMERS
AS
SELECT PERSON.PERSID, TITLE, PERSON.SALUTATION, EMAIL, LAST_NAME,
FIRST_NAME, DATE_OF_BIRTH, MARITAL_STATUS, RELATED_LASTNAME,
RELATED_FIRSTNAME, PHONE_PRIVAT, PHONE_COMPANY, VIP, PHONE_MOBILE,
PERSON.FAX_NR, POBOX, ACTUAL_CUSTOMER, PROMOTION_UNWELCOME,
DUPLICATE_ADDRESS, LAND.LAND_CODE, HOUSE.ZIP, HOUSE.PLACE,
HOUSE.STREET_NAME, HOUSE.STREET_NR, CUSTOMER_FROM_DATE, OTHER_PHONE,
BRANCH.FILIID, NOTES_TEXT1, NOTES_TEXT2, NOTES_TEXT3, NOTES_TEXT4,
NOTES_TEXT5, RETURNED_BACK_BY_PERSON, RETURNED_BACK_BY_POST
FROM PERSON INNER JOIN (HOUSEHOLD INNER JOIN (HOUSE INNER JOIN (LAND) ON
HOUSE.LANDID=LAND.LANDID)
ON HOUSEHOLD.HOUSID=HOUSE.HOUSID) ON PERSON.HOHDID=HOUSEHOLD.HOHDID
INNER JOIN (BRANCH_PERSON INNER JOIN (BRANCH) ON
BRANCH_PERSON.FILIID=BRANCH.FILIID) ON PERSON.PERSID=BRANCH_PERSON.PERSID
INNER JOIN (NOTES) ON PERSON.PERSID=NOTES.PERSID
INNER JOIN (PROMOTION) ON PERSON.PERSID=PROMOTION.PERSID
ORDER BY PERSID
/
I found later that I could achieve it by creating an SNAPSHOT of the view but the problem of solving the original issue became now that I don't know why I can not have the SNAPSHOT updated in each COMMIT; (REFRESH ON COMMIT option) I reviewed the REFRESH ON COMMIT preconditions and I found that the view was compliant with them but creating the SNAPSHOT still fails...
CREATE SNAPSHOT FOGSS_SEARCHCUSTOMERS
BUILD IMMEDIATE
REFRESH ON COMMIT
WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT * FROM FOGVW_SEARCHCUSTOMERS
/
I get the error
"ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized
view"
Is there anything I am missing? Can anyone help to make an idea of how to achieve the original Nth X rows problem from a JOINS VIEW in either way?
Best Regards,
Giovanni Azua
Received on Sat Jun 01 2002 - 14:20:22 CEST