[materialized view] ORA-12054: cannot set the ON COMMIT refresh attribute...

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 - 07:20:22 CDT