2 Replies Latest reply on Sep 12, 2005 7:41 AM by 436342

    AQ publish - DB subscribe: Error ORA-00942 when starting DB Adapter

    453213
      I am using AQ adapter to publish and DB adapter to subscribe.

      When starting the DB adapter i am receiving the following error:
      ---------------------------------------------------------------------------------------------------------------------
      Fri Sep 09 13:22:50 MDT 2005: ** Error: Unknown error while db_bridge_reader_1 was trying to read messages from the database. Please save log files and contact the integration team.
      java.sql.SQLException: ORA-00942: table or view does not exist

           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
           at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)
           at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:677)
           at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2371)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2660)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:387)
           at oracle.oai.agent.adapter.database.DBMessageReader.getNextMessageHeader(DBMessageReader.java:352)
           at oracle.oai.agent.adapter.database.DBMessageReader.run(DBMessageReader.java:232)
           at java.lang.Thread.run(Thread.java:534)
      -----------------------------------------------------------------------------------------------------------------------

      The table i am inserting into using the DB adapter does exist in the subscribing DB. The only thing i can think of is some table/view is missing within the hub repository itself.

      Please help.

      Thanks!

      -MB
        • 1. Re: AQ publish - DB subscribe: Error ORA-00942 when starting DB Adapter
          416900
          MB,

          Try
          1. taking an export of your current repository using the oaiexport script.
          2. Stop the adapters.
          3. Kill all connections to the repository (iStudio, SQL*Plus etc.)
          4. Stop the repository.
          5. Use the hubschema script, using the -drop argument. (This will drop your user e.g. OAIHUB904). You should see "User dropped"
          6. Use the hubschema script, using the -create argument. (This will recreate the repo user).
          7. Use oaiimport with your recently created *.dmp repository file.
          8. Start the repository
          9. Start your adapters.

          It could be that there was a problem when your AQ's were created, or they have become unstable. The oaiexport / drop / create / oaiimport routine should help clear this problem. If not, let us know.

          HTH
          Yan
          • 2. Re: AQ publish - DB subscribe: Error ORA-00942 when starting DB Adapter
            436342
            Initially I would try calling the subscribe package standalone in SQL*plus or TOAD just to check its not somekind of config problem...

            The subscribing adapter trys calling the subscribing package passing in the message as an oracle data type.
            I would write a small pl/sql wrapper that calls you subscribing package outside of the runtime Interconnect environment. If this all works and you can insert data into the table then it maybe something to do with the metadata in the repository and then i'd go along with Ian's suggestion.

            But usually if you can successfully use iStudio to edi things then usually the repository is OK.
            Also from your log you can see that it has picked up the message and is trying to call the packaged/procedure so it looks like the adapter is working OK.

            Cheers Stuart.