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.