1 Reply Latest reply on Nov 4, 2004 11:11 PM by 349404

    DB Adapter Publish not working with Oracle Database 7.0

    429908

      I'm trying to publish a message from an Oracle Database 7.0.. I generate the PLSQL script from istudio and it doesn't compile at first in the database.. This is the script generated by studio for a very simple message:

      CREATE OR REPLACE PACKAGE PRUEBASIETE AS
      ------------------------------------------------------------------
      PROCEDURE crMsg_EVENTPRU_OAI_V1
      (
      messageObjectID OUT NUMBER,
      aoID OUT NUMBER,
      CAMPO1 IN LONG,
      CAMPO2 IN LONG
      );
      ------------------------------------------------------------------
      PROCEDURE pub_EVENTPRU_OAI_V1(
      messageObject IN NUMBER,
      srcAppName IN VARCHAR2
      );
      END PRUEBASIETE ;

      /

      -------
      -----------------------------++++++-------------------------------
      -----------------------------++++++-------------------------------
      CREATE OR REPLACE PACKAGE BODY PRUEBASIETE AS
      ------------------------------------------------------------------
      PROCEDURE crMsg_EVENTPRU_OAI_V1
      (
      messageObjectID OUT NUMBER,
      aoID OUT NUMBER,
      CAMPO1 IN LONG,
      CAMPO2 IN LONG
      )
      AS
      BEGIN
      -- create a new message
      messageObjectID := OAI.Agent.createMessageObject('EVENTPRU:OAI/V1','PRUEBASIETE','OAI/V1');
      -- create the message tree
      OAI.Agent.createAttributeObject(messageObjectID,'PRUEBASIETE_EVENTPRU_OAI_V1_P:OAI/V1', aoID);
      OAI.Agent.addAttribute(messageObjectID, aoID, 'CAMPO1', CAMPO1);
      OAI.Agent.addAttribute(messageObjectID, aoID, 'CAMPO2', CAMPO2);
      END crMsg_EVENTPRU_OAI_V1;
      ------------------------------------------------------------------
      PROCEDURE pub_EVENTPRU_OAI_V1(
      messageObject IN NUMBER,
      srcAppName IN VARCHAR2
      )
      AS
      BEGIN
      -- send the message
      OAI.Agent.publish(messageObject, srcAppName);
      END;
      END PRUEBASIETE ;

      /


      The script doesn't compile because it says that messageObjectID and aoID are OUT parameters an cannot be used in the code... so what I did is to use a variable and then assign it, like this:

      PROCEDURE crMsg_EVENTPRU_OAI_V1
      (
      messageObjectID OUT NUMBER,
      aoID OUT NUMBER,
      CAMPO1 IN LONG,
      CAMPO2 IN LONG
      )
      AS
      moi NUMBER;
      oid NUMBER;
      BEGIN
      -- create a new message
      moi := OAI.Agent.createMessageObject('EVENTPRU:OAI/V1','PRUEBASIETE','OAI/V1');
      messageObjectID:=moi;
      -- create the message tree
      OAI.Agent.createAttributeObject(moi,'PRUEBASIETE_EVENTPRU_OAI_V1_P:OAI/V1', oid);
      OAI.Agent.addAttribute(moi, oid, 'CAMPO1', CAMPO1);
      OAI.Agent.addAttribute(moi, oid, 'CAMPO2', CAMPO2);
      aoID:=oid;
      messageObjectID:=moi;
      END crMsg_EVENTPRU_OAI_V1;


      Then I publish a message calling first the PruebaSiete.crMsg_EVENTPRU_OAI_V1 and then PruebaSiete.pub_EVENTPRU_OAI_V1 and it generates an error:

      Wed Oct 27 12:21:13 GMT-05:00 2004: ** Error: Error desconocido cuando db_bridge_reader_1 intentaba leer mensajes de la base de datos. Guarde los archivos log y póngase en contacto con el equipo de integración.
      java.sql.SQLException: Fallo al convertir a representación interna
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)
           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:294)
           at oracle.jdbc.driver.OracleStatement.getLongValue(OracleStatement.java:4254)
           at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:367)
           at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1564)
           at oracle.oai.agent.adapter.database.DBMessageHeader.<init>(DBMessageHeader.java:75)
           at oracle.oai.agent.adapter.database.DBMessageReader.getNextMessageHeader(DBMessageReader.java:364)
           at oracle.oai.agent.adapter.database.DBMessageReader.run(DBMessageReader.java:232)
           at java.lang.Thread.run(Thread.java:534)

      Which means:

      Error Error unknown error when db_bridge_reader_1 was trying to read messages from the database. Keep the log files and put in touch with the integration team. java.sql.SQLException: Failed to convert to internal representation..

      For the infrastructure server I have 10g DB, and App Server 10g.

      PLEASE HELPPPPP!!!

      This adapter, however, can receive events (suscribe), but not publish.. and we need it in a very important project..

      We tried in two 10g infrastructure servers, in both failed..

      Or list alternatives.

      Thanks so much

      Paola Linares
      plinares@i-siglo21.com


      thanks...