This discussion is archived
5 Replies Latest reply: Mar 8, 2013 5:17 PM by EJP RSS

ORA-00942: table or view does not exist-why is the missing table not shown?

794648 Newbie
Currently Being Moderated
Hello,

I am wondering why the missing table name isn't shown, when I get this exception:

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)

ojdbc6-version: 11.2.0.3.0.


Best regards

Josef

Edited by: user1149220 on Mar 7, 2013 10:19 AM
  • 1. Re: ORA-00942: table or view does not exist-why is the missing table not shown?
    Joe Weinstein Expert
    Currently Being Moderated
    That might have been a good improvement to the error message,
    but for whatever reason they didn't do it that way. There might
    even be cases where the table is not explicitly referred to by the
    JDBC client, such as in calling a stored procedure, and/or the table
    should not be exposed to the JDBC client. Just a thought, but
    mostly the first sentence is the basic.
    HTH,
    Joe
  • 2. Re: ORA-00942: table or view does not exist-why is the missing table not shown?
    gimbal2 Guru
    Currently Being Moderated
    This is nothing. Try running into a problem with a column in some table such as a constraint violation. The same thing - Oracle shall not name names.
  • 3. Re: ORA-00942: table or view does not exist-why is the missing table not shown?
    rp0428 Guru
    Currently Being Moderated
    >
    I am wondering why the missing table name isn't shown, when I get this exception:
    >
    Because it may not be a 'table' that is missing and Oracle may not be able to determine what it is that should be being referenced.

    The full text is
    >
    ORA-00942: table or view does not exist
    Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.
    Action: Check each of the following:

    the spelling of the table or view name.
    that a view is not specified where a table is required.
    that an existing table or view name exists.
    Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.

    Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.
    >
    The most common reason when questions about this exception are ask in the forums is when a query works when run by the user but throws this exception when executed in a stored procedure or other PL/SQL code.

    For that use case the usual problem is that the required prviliges have not been granted.

    Roles are DISABLED during PL/SQL processing. A simple 'SELECT * FROM SCOTT.EMP' will throw that exception if the query is run in a PL/SQL procedure and the user has not been granted SELECT privileges directly. Granting the privilege to a role and then granting that role to the user will NOT work.
  • 4. Re: ORA-00942: table or view does not exist-why is the missing table not shown?
    794648 Newbie
    Currently Being Moderated
    Tranks for your insights.

    I think an exception should always give you a clue what exactly went wrong and not that there is some problem you have to debug.

    Today I got this exception again than I moved a table to a different schema and it did not find it. The real reason was that I used Hibernate with auto generating id, so a new sequence + permission was required.

    It would be so much easier for troubleshooting if the exception message would contain more information.

    Best regards

    Josef
  • 5. Re: ORA-00942: table or view does not exist-why is the missing table not shown?
    EJP Guru
    Currently Being Moderated
    It's not as simple as you seem to think. If the necessary information isn't available to the client, i.e. isn't provided by the server, it can't be put into the exception message. There are also security concerns in revealing a table name to which the user doesn't have access.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points