4 Replies Latest reply on Jul 4, 2006 1:23 PM by 509407

    Messages stuck in OAI schema

    509407
      Hi,

      Adapter is up and running but 3k messages are stuck in OAI tables and are not published to the adapter. Records exist in messageobjecttable and aotable.

      Stopping and starting adapter doesn't reslove this - bouncing db doesn't either.

      I can publish current messages successfully - but these 3k still sit in the tables.

      Anyone know how to get these messages moving?

      Regards
      smudger
        • 1. Re: Messages stuck in OAI schema
          439096
          Have you checked the log file of the adapter If it even received the messages?
          It could be that the first message is in error and blocking the queue.

          Are thes messages in hub-queue or in error-queue.

          I don't know if you know ICManager;
          You can use ICManager (a shell-tool) to check the queue state, and even delete (in some cases) the messages in the queue, even by selection.
          • 2. Re: Messages stuck in OAI schema
            159563
            If you are shure the new messages published correctly with this adapter - simple delete from messageobjecttable and don't take care.

            If you are not shure - check if there is any error messages that block others.
            Use: select * from oai.messageobjecttable t where t.status =1

            If there is any error messages - delete them. Sometimes it happens when you change or drop your publish in iStudio, but stil publish old event from PL/SQL (from adapter) - in this case message are marked as bad and block all other messages in the adapter.
            • 3. Re: Messages stuck in OAI schema
              416900
              Hi,

              Be sure to set the "srcAppName" as your Publishing Adapter name when you call the "Pub_" procedure.

              Failure to do this will mean that your Publishing Adapter will not pick up any messages in the OAI schema.

              E.g.

              DECLARE

              p_emp_num VARCHAR2(10) := '1234X';
              p_srcAppName VARCHAR2(20) := 'MY_PUB_DB_ADAPTER';
              aoID NUMBER := NULL;
              messageObjectID NUMBER := NULL;

              BEGIN

              -- Call the crMsg_ (Create Message) procedure

              My_Pub_Package.crMsg_My_Message_OAI_V1(
              messageObjectID => messageObjectID,
              aoid => aoID,
              EMP_NUM => p_emp_num);

              -- Call the pub_ (Publish Message) procedure
              My_Pub_Package.pub_My_Message_OAI_V1(
              messageObjectID => messageObjectID,
              srcAppName => p_srcAppName);

              COMMIT; -- Don't forget this!!

              END;

              Hope this helps

              Yan
              • 4. Re: Messages stuck in OAI schema
                509407
                Thanks for all the possible solutions!

                The problem actually related to the fact that we encountered a hardware issue in the middle of the job to publish the messages. The records which are stuck don't have any values for the type, applicationtype, correlationid, sendername of status fields. So the adapter didn't know they were waiting to be picked up.

                Thanks again for your help.

                Cheers
                Smudger