Home > B2B, Database, Oracle > Oracle B2B bulk resubmit with SQL

Oracle B2B bulk resubmit with SQL

In 11g it will be possible to resubmit messages from the user interface, but in 10g that feature was missing. In early 2008 some resubmit support was added,  see
http://www.b2bgurus.com/2008/02/resubmission-feature.html. It included automatic retries and manual resubmit using a command line tool and it made it possible to write more sophisticated tools supporting both inbound and outbound resubmission.

However, if thousands of messages are to be resubmitted based on some criteria nothing beats SQL. Luckily it is not that difficult to write a SQL script that resubmits messages that match custom search criteria.

For outbound messages it has always been fairly easy. All the information needed in order to create a new identical message and put it on the internal delivery channel for processing can be found in the b2b_instancemessage view. See Tech Note #004 for details.

For inbound messages it is harder, but there the resubmit support in the product helps. We can do what the command line tool does. Note that this is in no way supported by me or by Oracle; be sure to try it out on a test system before using it in production! It will probably not work as is with 11g either.

The script below finds matching messages, flags them for resubmit and alerts B2B to process them using the event queue. The condition can be quite complex, selecting exactly the inbound messages that are to be resubmitted.

set serveroutput on;
declare
  msg                 b2b_event_type;
  enqueue_options     dbms_aq.enqueue_options_t;
  message_properties  dbms_aq.message_properties_t;
  subscribers         dbms_aq.aq$_recipient_list_t;
  msg_handle          raw(16);
  n                   number(10);
begin
  dbms_output.put_line ('Resending messages...');
  begin
    subscribers(1) := sys.aq$_agent('b2buser', null, null);
    message_properties.recipient_list := subscribers;
    n := 0;
    for rec in (select b2bwiremessageid from b2b_instancemessage
      where direction = 'Inbound'
      -- ENTER CONDITION HERE ------------------------------------
      and b2btimestamp between sysdate - 20 and sysdate - 1
      and documenttype like 'INVOIC%'
      -- END OF CONDITION ----------------------------------------
    ) loop
      update tip_wiremessage_rt
        set protocoltransportbinding = 'B2BRetry=true
' || protocoltransportbinding
        where protocoltransportbinding not like '%B2BRetry=%'
        and b2bwiremessageid = rec.b2bwiremessageid;
      msg := b2b_event_type(event_type => 3, event_id => 24,
                            id => rec.b2bwiremessageid);
      dbms_aq.enqueue(queue_name  => 'B2B_EVENT_QUEUE',
                      enqueue_options => enqueue_options,
                      message_properties => message_properties,
                      payload => msg,
                      msgid => msg_handle);
      n := n + 1;
    end loop;
    dbms_output.put_line ('Processed ' || n || ' messages');
    dbms_output.put_line ('Commit or rollback now!');
  exception
    when others then
      dbms_output.put_line ('SQL Error: ' || SQLCODE ||
                            ' - ' || SQLERRM);
      rollback;
  end;
end;
/

Note that the newline after B2BRetry=true is intentional and required!

Again, this is not officially supported in any way, but perhaps it can save your skin if something has gone wrong somewhere and you need to get those messages in fast. Just remember not to run it in production before trying it out in a safe environment.

Advertisements
Categories: B2B, Database, Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: