Oracle JDBC Clob insert issue ORA-00942

Question

I am reading a set of fields from an Oracle source and insert retrieved data into another Oracle Source, when I attempt to insert into clob area, I happen to experience "java.sql.SQLException: ORA-00942: table or view does not exist" exception, all the fields are inserted successfully but the clob area. The below I'll add the fields of the target table and my sample code. I've also found out this solution on stackoverflow which did not helped me out. The target table is oracle exadata

Table PODS.RAP_AUDIT
Column name: EVENT_ID
Column type: VARCHAR2

Column name: RUN_DATE
Column type: DATE

Column name: USER_NAME
Column type: VARCHAR2

Column name: STATUS
Column type: VARCHAR2

Column name: UNIVERSE_NAME
Column type: VARCHAR2

Column name: REPORT_NAME
Column type: VARCHAR2

Column name: CUI_ID
Column type: VARCHAR2

Column name: QUERY_NAME
Column type: VARCHAR2

Column name: REPORT_DURATION
Column type: NUMBER

Column name: ROW_COUNT
Column type: VARCHAR2

Column name: SQL
Column type: CLOB

code:

private static void retrieveClobArea() {

        String sql = "select E.START_TIME as dateval, 5087472590231472273 as idval,  D.EVENT_DETAIL_VALUE as clobval  from BO41AUD.ADS_EVENT_DETAIL D, BO41AUD.ADS_EVENT E where E.EVENT_ID=D.EVENT_ID and     E.START_TIME >= to_date('28.07.2015 12:19:14','DD.MM.YYYY HH:MI:SS')";

        try {
            Class.forName(jdbcDriver);
            connection = DriverManager.getConnection(sourceDBConnection,
                    sourceDBUser, sourceDBPass);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {

                insertSampleCLOB(resultSet.getString(2), resultSet.getClob(3));
            }
        } catch (SQLException e) {
            System.err.println(e);
        } catch (ClassNotFoundException e) {
            System.err.println(e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println(e);
            }
        }

    }


private synchronized static void insertSampleCLOB(String eventId, Clob sql) {
        StringBuilder insertQuery = new StringBuilder();
        insertQuery.append("INSERT INTO PODS.RAP_AUDIT ");
        insertQuery.append("(");
        insertQuery.append("EVENT_ID, ");
        insertQuery.append("SQL");
        insertQuery.append(")  VALUES(?,?)");

        try {
            Class.forName(jdbcDriver);
            connection = DriverManager.getConnection(targetDBConnection,
                    targetDBUser, targetDBPass);
            preparedStatement = connection.prepareStatement(insertQuery
                    .toString());

            preparedStatement.setString(1, eventId);

            preparedStatement.setClob(2, sql);


            preparedStatement.executeUpdate();
            System.out.println("Inserted " + eventId + "  " + new java.util.Date());

        } catch (ClassNotFoundException e) {
            logger.error("insertInRAP_AUDIT exception cnfe " + e);
            System.err.println(e);
        } catch (SQLException e) {
            logger.error("insertInRAP_AUDIT exception sqle " + e);
            System.err.println(e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error("insertInRAP_AUDIT exception connection close e "
                        + e);
                System.err.println(e);
            }
        }
    }

Why didn't the solutions in the linked question help? Did you still get ORA-00942 using `setString` or a character stream, which would suggest the user you're connected as just doesn't have access to the table and it's nothing to do with CLOBs? Or something else? — Alex Poole, Jul 28 '15 at 15:03
The solution in the link offers to use the setString, whereas the data being hold within those fields are enormous which cannot be stored as a String type since it will limit the data up to 4K. — Tugrul ASLAN, Jul 29 '15 at 06:05

score -1 · Answer 1 · answered Jul 29 '15 at 13:58

-1

Before call the method insertSampleClob do this:

Clob clob = CLOB.createTemporary(connection, false, oracle.sql.CLOB.DURATION_SESSION);
clob= resultSet.getClob(3);

and then:

insertSampleCLOB(resultSet.getString(2),clob);

It should resolve your problem.

answered Jul 29 '15 at 13:58

Fabio Cardoso

1,181
1
14
37

I think this will just leak a temporary LOB until the session is closed – gpeche Oct 08 '15 at 13:13
you are creating a temporary LOB and then discarding its locator to store the one that comes from `resultSet`. – gpeche Oct 09 '15 at 06:26

Oracle JDBC Clob insert issue ORA-00942

1 Answers1