1

I am new to Oracle. I am able to execute:

SELECT X, Y, Z. 
FROM SCH2.TAB_A A JOIN SCH2.TAB_B B ON A.CODE = B.CODE
LEFT OUTER JOIN SCH2.MY_V MV ON MV.CODE = B.CODE
WHERE  MV.STATUS = 'A';

But when I am trying to put this SELECT statement in a view, I am getting error:

ORA-00942: table or view does not exist

CREATE OR REPLACE VIEW SCH1.NEW_V AS
SELECT X, Y, Z. 
FROM SCH2.TAB_A A JOIN SCH2.TAB_B B ON A.CODE = B.CODE
LEFT OUTER JOIN SCH2.MY_V MV ON MV.CODE = B.CODE
WHERE  MV.STATUS = 'A';
/

Tried google,

SQL Error: ORA-00942 table or view does not exist

http://www.techonthenet.com/oracle/errors/ora00942.php

But still not able to resolve. If I am able to run the query, why am I getting error when I am trying to put that in a View.

Community
  • 1
  • 1
Brij
  • 11,731
  • 22
  • 78
  • 116

2 Answers2

3

Check that you have direct access privileges, not through a role:

SQL> create user sch1 identified by sch1;

SQL> create role scott_acc;

SQL> grant select on scott.emp to scott_acc;

SQL> grant scott_acc to sch1;

SQL> create or replace view sch1.v_1
  2  as select * from scott.emp;
as select * from scott.emp
                       *
error in line 2:
ORA-00942: table or view does npt exist


SQL> grant select on scott.emp to sch1;


SQL> create or replace view sch1.v_1
  2  as select * from scott.emp;

View created.
Dmitry Nikiforov
  • 2,988
  • 13
  • 12
0

This helped

Oracle Create View issue

GRANT SELECT ON SCH2.TAB_A TO SCH1;

GRANT SELECT ON SCH2.TAB_B TO SCH1

GRANT SELECT ON SCH2.MY_V TO SCH1;
Community
  • 1
  • 1
Brij
  • 11,731
  • 22
  • 78
  • 116