BatchUpdateException:ORA-00942:TABLE OR VIEW DOES NOT EXIST

Question

This issue occurred in jdbc batch insert. I queried from an Oracle datasource, parsed the resultset and then inserted into another Oracle datasource. I have got the connect metadata and printed the current username along with url, both are invalid. But when it went to batch update, I got the ora-00942 exception. I'm pretty sure all above works fine in database. Has anyone encountered this exception and can you give me some advice?

EDIT: Ok, I got a table named photos for example in REMOTE_USER and I queried from it. It gave me a resultset, then I parse it after that INSERT it to LOCAL_USER.photos. I did query the LOCAL_USER.photos where I logon in from PL/SQL Developer. The interesting thing was I could do the select command but not the insert. Below is some part of code.

     conn = datasource.getConnection(); // notice that it was target datasource
            DatabaseMetaData connMetaData = conn.getMetaData();
            String userName = connMetaData.getUserName();
            resultSet = ds.getResultSet();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();

      String insertSql = generateInsertSql(count, metaData, userName);
      // this was generated through metaData , the output should be 
      // "insert into LOCAL_USER.photos(col1,col2) values(?,...)"

        logger.error("insert clause is {}", insertSql);
        ps = conn.prepareStatement(insertSql);
        conn.setAutoCommit(false);
        while (resultSet.next()) { // this was the original datasource
            stageTotalNum++;
            for (int i = 1; i <= count; i++) {
                Object object = resultSet.getObject(i);
                dealClobColumn(ps, i, object);
            }
            ps.addBatch();

            if (stageTotalNum % 500L == 0L) {
                ps.executeBatch(); // throws batchupdateexception.
                ps.clearBatch();
                conn.commit();
            }
        }

        ps.executeBatch();

        conn.commit();

Could you please provide more information? Also could you check if all tables in your query are exists and all of this tables are accessible from logged user — Vasyl Moskalov
@VasylMoskalov Edit my post,please check it . Thanks for your time. — JXmb
Could you please execute your insert clause using same credentials as in datasource.getConnection()? — Vasyl Moskalov
Check this link if in case it helps you : stackoverflow.com/questions/6561650/… — codeLover
@VasylMoskalov I didn't know if it is the lob problem.My photos table got a blob column which stored the binary data for the images. What I used to query and insert is getObject and setObject. I ran the application locally and it ran pretty well but when I posted it to remote server the application threw the exception. — JXmb

JXmb JXmb · Accepted Answer · 2018-09-03T08:37:29

It should be the blob type column which I didn't handle it the right way. First I queried from original datasource then got the blob column of the resultset by conn.getObject(index) . Next I insert the blob column into target datasource by conn.setObject. Of course that way wasn't working at all, so I changed to the following:

 conn.setBlob(rs.getBlob(index)).

Although it worked fine in my own environemnt, but when the application ran in remote server, it kept annoying about the 'table or view does not exists'.The third version is:

conn.setBinaryStream(rs.getBlob(index).getBinaryStream());

Ok, this time it worked both my pc and remote server. Thanks to @codeLover's advice and link, it really hepled me and saved my time. Appreciated it!

BatchUpdateException:ORA-00942:TABLE OR VIEW DOES NOT EXIST

1 Answers