Question

Oracle Cross Schema View

Oracle, Oracle Views, cross schema, SQL, Oracle error messages

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

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.