Oracle Cross Schema View

20 pts.
Tags:
cross schema
Oracle
Oracle error messages
Oracle Views
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following