4 Replies Latest reply: Oct 31, 2012 9:24 AM by 722012 RSS

    Getting strange error with connect by clause

    722012
      Hi Everyone,

      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.
      The strange problem is when I execute the query on the 1 ^st^ database I get D.DEPT_NO is not available column (D.* all columns from department table) but it is there and what is more strange this query works fine on 2 ^nd^ database.

      Is there any settings I have to do in the database so that it can work with connect by clause ?
      SELECT SUBSTR(LPAD(' ', 4*(LEVEL))  || '· ' ||DEPT_NAME,1,50)  DEPT_NAME_HY, D.*
      FROM PERSONEL.DEPARTMENT D
      START WITH MAIN_DEPT_NO IS NULL
      CONNECT BY PRIOR DEPT_NO=MAIN_DEPT_NO
      Thank you.
        • 1. Re: Getting strange error with connect by clause
          jeneesh
          SSU wrote:
          Hi Everyone,

          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.
          Did you mean two SCHEMAs or tow databases?

          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
          DESC PERSONEL.DEPARTMENT 
          • 2. Re: Getting strange error with connect by clause
            BluShadow
            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.
            • 3. Re: Getting strange error with connect by clause
              722012
              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 11.2.0.1.0 - 64bit Production
              2.PL/SQL Release 11.2.0.1.0 - Production
              3.CORE 11.2.0.1.0 Production
              4.TNS for Linux: Version 11.2.0.1.0 - Production
              5.NLSRTL Version 11.2.0.1.0 - Production

              Error is ORA-00904 DEPT_NO invalid identifier.
              • 4. Re: Getting strange error with connect by clause
                722012
                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.