When modifying/deleting the SELECT fields of a notification event, be sure that the buffer table is completely empty.
If changes are attempted on a buffer table that contains data, an non-descriptive error message will be returned.
How can i check the contents of buffer table
before doing this.If am using oracle/sqlserver
or any RDBMS where exactly the BF table is created
If any suggstns i wld appreciate!
Thanks,
santosh
Hi, Santosh. When you are using the Oracle Intelligent Adapter to create a notification event, the adapter will build a buffer table for you. You do not need to build the buffer table. The tricky part, though, is to find the buffer table.
I will explain a little about what the Oracle Adapter does when you create a new configured operation for a NOTIFICATION event and it should be enough information to help you.
When a developer uses the operation “wizard” to create an INSERT notification event, it is required to select the fields of the output document. For example, in a table with 30 fields, you may want to select only the primary key. Let’s assume that the table name is “DEALS” and the table’s primary key is the field “DEALNUMBER”.
In the “TABLES” tab of the wizard, select the source table “DEAL”. In the “SELECT” tab, choose the field “DEALNUMBER”. You may put filters on “DEALNUMBER” using the “WHERE” tab. It is not necessary to use the other tabs, but you may find that there is reason to with your integration. I will not get into those topics here.
Okay. At this point, you have told the Intelligent Adapter which table you want it to listen to and what field(s) in which you are interested. The adapter takes this information and writes a trigger for the table “DEAL”. This happens immediately and can be verified using TOAD or any other database schema browser that you use.
The trigger may look like this: “AWT_cagoOracle_DEAL_Insert_N AFTER INSERT ON SCOTT.WMDEAL FOR EACH ROW BEGIN INSERT INTO AWB_cagoOracle_DEAL_Insert_N (DEALNUMBER)”.
Additionally, the adapters creates the buffer table as the user specified in the “Tables” tab of the wizard. The buffer table is only accessed by the Oracle adapter and should never be directly written to by a developer.
In the trigger, you will be able to see the exact name of the buffer table that the Oracle Intelligent adapter created. Usually, it is the name of your table with some prefixed characted and some suffixed indication of your notification type. For example, the INSERT notification that we built in this example may be named “AWB_CAGOORACLE_DEAL_INSERT_N”. I am not sure of teh naming conventions, but usually the buffer tables begin with “AWB” (ActiveWorks Buffer?).
The adapter will poll the buffer table for new records at regular intervals (15 seconds is the default) because the trigger is populating the new records into the buffer table.
Dan. Thanks for the great explanation!
Another way of determining the buffer table name is to run the adapter in debug mode from the adapter config tool. In the debug output you will see the name of the buffer table appearing every time the buffer table is polled by the adapter, which is set at 15 seconds by default as Dan had pointed out.
If the buffer table is empty you should see no output from the poll in the debug output.
In the example below, the buffer table name is AWB_OracleAdapter_INSERTOrders and it is empty.
9:39:25 PM [Debug] 0 INSERTOrders process source
9:39:25 PM [Debug] 0 INSERTOrders input
9:39:25 PM [Debug] 0 INSERTOrders println exec SQL SELECT t1.ORDER_DET_NO,t1.ORDER_NO,t1.LINE_NO,t1.PRODUCT_NO,t1.QUANTITY,t1.UNIT_MEASURE,t1.UNIT_PRICE,t1.DISCOUNT,t1.rowid FROM AWB_OracleAdapter_INSERTOrders t1
9:39:25 PM [Debug] 0 INSERTOrders output
9:39:25 PM [Debug] 0 INSERTOrders process time 140ms
9:39:25 PM [Debug] 0 println SQL: ROLLBACK
Dan,
Thanks for your kind information.its really
interesting.
cheers,
santosh