9 Replies Latest reply on Apr 4, 2012 4:08 PM by 761373

    ojdbc ORA-00942 + ORA-02063

    761373

      Hello.

      First of all i want to describe our system architecture. It includes 2 machines: 1rst with Oracle 11g Database and 2nd with Oracle 10g Database.
      1rst machine has 2 instances and 2nd machine has 1 instance. 1rst instance has no data, it has only synonyms with db-links to 2nd instance of 1rst machine.
      All data are stored on 2nd machine. 2nd instance of 1st machine has db-links to 2nd machine.

      When i try to execute "select * from table1" i get errors:

      ORA-00942: Table or view does not exist
      ORA-02063: preceding line from <DBLINK NAME>
      ...


      where table1 is synonym to schema from 2nd instance of first machine.


      Please help to understand is it bag and how i can fix it?

      Thank you.

        • 1. Re: ojdbc ORA-00942 + ORA-02063
          Joe Weinstein-Oracle
          Does the same thing happen from SQL-PLUS? I don't think this is JDBC-related....
          • 2. Re: ojdbc ORA-00942 + ORA-02063
            761373
            No, SQLPLUS work fine, PL/SQL DEVELOPER works fine too, ORACLE SQL DEVELOPER works fine too.

            Problem just only is in queries from Java through JDBC.

            Sorry, i forget to say that i use package function in database that return REF CURSOR to java. And my error happens in line

            OPEN CUR FOR

            where CUR is varible that has type REF CURSOR.
            • 3. Re: ojdbc ORA-00942 + ORA-02063
              Joe Weinstein-Oracle
              So, where is the JDBc client running? Does this work:

              Statement s = c.createStatement();
              ResultSet r= s.executeQuery("select * from table1");
              while (r.next()) {}
              • 4. Re: ojdbc ORA-00942 + ORA-02063
                761373
                JDBC is running under weblogic 10.3.5 + Sherman Patch with ADF 11.1.2. But after fail i tried to use it on my notebook with Windows XP and SUN JVM x86 and i get the same problem.

                In my application i use this code:

                String query = "BEGIN ? := REPORT_22_SNG.GET_TABLE1_DATA(?,?); END;";
                CallableStatement stmt = m_conn.prepareCall(query);
                stmt.registerOutParameter(1, OracleTypes.CURSOR);
                stmt.setString(2, "2011");
                stmt.setInt(3, 20);
                stmt.execute();
                ResultSet rs = (ResultSet)stmt.getObject(1);
                if (rs.next()) { ....   }
                • 5. Re: ojdbc ORA-00942 + ORA-02063
                  Joe Weinstein-Oracle
                  Open an official SR and show them that, and what happens if you do the code I suggested as well.
                  • 6. Re: ojdbc ORA-00942 + ORA-02063
                    rp0428
                    >
                    ORA-00942: Table or view does not exist
                    ORA-02063: preceding line from <DBLINK NAME>
                    ...
                    where table1 is synonym to schema from 2nd instance of first machine.
                    . . .
                    No, SQLPLUS work fine, PL/SQL DEVELOPER works fine too, ORACLE SQL DEVELOPER works fine too.
                    . . .
                    Sorry, i forget to say that i use package function in database that return REF CURSOR to java.
                    >
                    I doubt if you need to file an SR.

                    That error means the user you are logged in as is trying to access an object that the user does not have privileges to.
                    Most likely the user was granted the privileges thru a role. Roles are disabled in PL/SQL packages and functions. The object grants need to be made directly to the user.

                    I would guess that when you say sqlplus and the other tools work fine you are not using the package function when you tested them. Either than or you were logging on as a different user.

                    Check with your DBA and make sure that SELECT privileges (and any others needed) are granted directly to the user and not thru a role.

                    For example suppose that USER1 owns TABLE1 and grants SELECT privilege to the role SELECT_TABLE1_ROLE and that USER2 has been granted the SELECT_TABLE1_ROLE.
                    Then for USER2 this will work
                    SELECT * FROM USER1.TABLE1
                    But the same select by USER2 from a stored procedure will fail wth the same error you got. If the SELECT privilege is granted to USER2 directly a select by USER2 from a stored procedure will work.
                    • 8. Re: ojdbc ORA-00942 + ORA-02063
                      761373
                      Thank you, rp0428. I will try your advice.

                      But want to say that when i used sqlplus and the other tools and all work fine, I really used the package function throw PL/SQL. I returned CURSOR and than wrote it into a test table.

                      But i will check the object grants.
                      • 9. Re: ojdbc ORA-00942 + ORA-02063
                        761373
                        The problem was solved.

                        I droped and recreated synonyms. Some of them was written in lowercase, some of them was written in uppercase. All of them was created using uppercase and after that my query started working fine.