Getting an exception ORA-00942: table or view does not exist - when inserting into an existing table

user_321008 0
Viewed 80330

I am getting below exception, when trying to insert a batch of rows to an existing table

ORA-00942: table or view does not exist

I can confirm that the table exists in db and I can insert data to that table using oracle sql developer. But when I try to insert rows using preparedstatement in java, its throwing table does not exist error.

Please find the stack trace of error below

java.sql.SQLException: ORA-00942: table or view does not exist

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) 
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) 
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1889)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940) 
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout>>(OracleStatement.java:2709)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
    at quotecopy.DbConnection.insertIntoDestinationDb(DbConnection.java:591)
    at quotecopy.QuoteCopier.main(QuoteCopier.java:72)

Can anyone suggest the reasons for this error ?

Update : Issue solved

There was no problem with my database connection properties or with my table or view name. The solution to the problem was very strange. One of the columns that I was trying insert was of Clob type. As I had a lot of trouble handling clob data in oracle db before, gave a try by replacing the clob setter with a temporary string setter and the same code executed with out any problems and all the rows were correctly inserted!!!.

ie. peparedstatement.setClob(columnIndex, clob)

was replaced with

peparedstatement.setString(columnIndex, "String")

Why an error table or view does exist error was throws for error in inserting clob data. Could anyone of you please explain ?

Thanks a lot for your answers and comments.

ora-00942 jdbc oracle sql java
10 Answers

I found how to solve this problem without using JDBC's setString() method which limits the data to 4K.

What you need to do is to use preparedStatement.setClob(int parameterIndex, Reader reader). At least this is what that worked for me. Thought Oracle drivers converts data to character stream to insert, seems like not. Or something specific causing an error.

Using a characterStream seems to work for me. I am reading tables from one db and writing to another one using jdbc. And i was getting table not found error just like it is mentioned above. So this is how i solved the problem:

case Types.CLOB:   //Using a switch statement for all columns, this is for CLOB columns
        Clob clobData = resultSet.getClob(columnIndex); // The source db
        if (clobData != null) {
            preparedStatement.setClob(columnIndex, clobData.getCharacterStream());
        } else {
            preparedStatement.setClob(columnIndex, clobData);
        }
        clobData = null;
        return;

All good now.

user_10816980

Is it possible that you are doing INSERT for VARCHAR but doing an INSERT then an UPDATE for CLOB?

If so, you'll need to grant UPDATE permissions to the table in addition to INSERT.

See https://stackoverflow.com/a/64352414/1089967

user_10899670

Here I got the solution for the question. The problem is on glass fish if you are using it. When you create JNDI name make sure pool name is correct and pool name is the name of connection pool name that you are created.

enter image description here

user_89512090
Related