Menu
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.
339
check the spelling of the table or view name. check that an existing table or view name exists. contact the DBA if the table needs to be created or if user or application privileges are required to access the table.
It means exactly what it says, the table or view you are executing your query on does not exist in your schema. To explain, whenever you execute a query which includes a table, view, synonym or a cluster which does not exist into the schema with which you are connected the SQL engine will show you this error.
ORA-01031: insufficient privileges Solution: Go to Your System User. then Write This Code: SQL> grant dba to UserName; //Put This username which user show this error message. Grant succeeded.
Oracle will also report this error if the table exists, but you don't have any privileges on it. So if you are sure that the table is there, check the grants.
172
There seems to be some issue with setCLOB() that causes an ORA-00942 under some circumstances when the target table does exist and is correctly privileged. I'm having this exact issue now, I can make the ORA-00942 go away by simply not binding the CLOB into the same table.
I've tried setClob() with a java.sql.Clob and setCLOB() with an oracle.jdbc.CLOB but with the same result.
As you say, if you bind as a string the problem goes away - but this then limits your data size to 4k.
From testing it seems to be triggered when a transaction is open on the session prior to binding the CLOB. I'll feed back when I've solved this...checking Oracle support.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
