This content has been marked as final. Show 4 replies
SSU wrote:Did you mean two SCHEMAs or tow databases?
I have a 2 test oracle databases and each of them contain a table called department (dept_no,dept_name, main_dept_no) where each department entry is connected to main department by dept_no in the MAIN_DEPT_NO column.
Please post DB version.
The exact error message you are getting.
output of the below from the DB rom where you are getting the error
Is the query beeing done inside a PL/SQL procedure or package?
If so, it may be that the schema user where it's not working does not have grants to select on that table directly given to them (perhaps only through a role), as role based privileges don't work in stored PL/SQL code by default.
I have 2 computers which running 2 same databases with same schemas.
The version of databases is given below.
1.Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production
2.PL/SQL Release 126.96.36.199.0 - Production
3.CORE 188.8.131.52.0 Production
4.TNS for Linux: Version 184.108.40.206.0 - Production
5.NLSRTL Version 220.127.116.11.0 - Production
Error is ORA-00904 DEPT_NO invalid identifier.
I have checked there not problem with privileges.
If in the query instead of D.*, I write D.DEPT_NO, D.DEPT_NAME, D.MAIN_DEPT_NO the query works fine.
My concern why D.* is not working with connect by clause.