Oracle Cross Schema View
I am creating a cross schema view and am receiving the error 'Table or View does not exist' on the second table of one schema in my view. Here is the View:
CREATE OR REPLACE VIEW WORKMGMT.VIEW_WORKMGMT_USERS(
USER_ID,
USER_VALID_FROM,
USER_VALID_TO,
DEFAULT_UNIT,
LANGUAGE_PREFERENCE)
AS SELECT
SU.ORGANIZATION_USER_ID "USER_ID", TO_TIMESTAMP(TO_CHAR(SU.EFFECTIVE_FROM,'YYYY_MON_DD'),'YYYY-MON-DD HH24:MI:SS') "USER_VALID_FROM", TO_TIMESTAMP(TO_CHAR(SU.EFFECTIVE_TO, 'YYYY_MON_DD'), 'YYYY-MON-DD HH24:MI:SS') "USER_VALID_TO", WM.DEFAULT_UNIT_ID "DEFAULT_UNIT", SA.LANGUAGE_PREFERENCE
FROM
SECEPHS.ORGANIZATION_USER SU, SECEPHS.ACCOUNT_HOLDER SA, WORKMGMT.ORG_USER_WM_PROFILE WM
WHERE
SU.ACCOUNT_HOLDER_ID = SA.ACCOUNT_HOLDER_ID AND SU.ORGANIZATION_USER_ID = WM.ORGANIZATION_USER_ID AND SU.IS_DELETED_IND = '0';
The error occurs on the second table of the SECEPHS schema, no matter the order of the tables. Any advice would be very much appreciated.
Thank-you
Software/Hardware used:
Software/Hardware used:
ASKED:
November 20, 2007 9:08 PM
UPDATED:
November 20, 2007 10:42 PM
Answer Wiki:
Hi,
Make sure the names are spelled correctly and that the view owner (WORKMGMT) has SELECT privileges on the tables. The privileges must be granted directly to WORKMGMT (or PUBLIC); even if WORKMGMT has a role that has all the necessary privileges, and can query the table, that's not sufficient for creating a view.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
