Hi Guys,

I have come up with a requirement i.e. We have Oracle Student Systems Applications, Whenever students adds the unit course to their Enrollments one of the base table gets inserted with the student details and the units the students has selected. As soon as the record is inserted into Oracle Students System Applications Base tables an database trigger fires and the same record is inserted into a Oracle custom table so that WM can picks the data and do the necessary data conversion and write it to a file.

I know how to write the data picked by WM to a file. I was wondering How can I pick the data that was inserted by database trigger into Oracle Custom table.

Can anybody let me know how to initiate the process? Can anybody let me know the steps to make this happen?

Thank you very much in advance.
Priyatham Porika

You could use either an insert notification which would publish notification documents when rows were inserted into your custom table. If you plan to use your custom table as a webMethods buffer table, then you could use a basic notification to poll the custom table deleting rows when notification docs have been successfully published.

Both notification types are described in detail in the JDBC Adapter User’s Guide found in the \IntegrationServer\packages\WmJDBCAdapter\doc folder.

HTH,

Mark

Thanks Mark for quick response.

Priyatham Porika

Priyatham,

When ever a row inserted in the DB tables,create a JDBCNotification Adapterservice and login to JDBCAdapter admin page and click on pollingnotifications then you should see the created service there and enable it and schedule an interval for polling the DB table.

So if any things updates on the table the polling notification will trigger and publishes the document what ever you have created in the JDBCAdapterNotification service you have to subscribe the published document using subscribe service it will return the results insert/updated data and loop the results and writeToFile.

As mark suggested above the userguide will help you for creating notification/making use of WM buffer tables etc…

HTH,
RMG

Hi Ram,

As you know I am learning WM. I am just trying take the data that’s inserted in a source table from SQL*PLUS and insert into the destination table not to a file right now. I just want to give a shot a see.

a.) I created an test package. I created the JDBCNotification
AdapterService and pollingnotifications for every 15 sceonds.
I have the insertAdapterMonitorPublishDocument and I have the
InsertAdapterMonitor.
b.) I have an JDBC Adapter Service (InsertUnitsAssignments) which
inserts into the destination table.
c.) I have created an trigger and I have given:
1.) The Document types and filters as “PorikaPractice.InsertNotification:insertMonitorPublishDocument” and I don’t have any filters right now.
2.) Service as:
PorikaPractice.InsertNotification:InsertUnitAssessments
3.) I don’t have any conditions.
d.) I have a sub service and I gave the “InsertNotification:insertMonitorPublishDocument” as input to my
JDBC Adapter Service (InsertUnitsAssignments) and I insert a record into the source table and after 15 seconds I can see an record inserted into the destination table but without any values, all null values.

I am not able to understand why only Null values are getting inserted?
I just ran the JDBC Adapter service seperately from WM,which inserts the records into the destination table without any problems.

Any idea?

Thanks Ram,
Priyatham Porika

IS your insertnotification service triger kicksoff?when you tried to insert some rows to db using SQL* plus or thru manully run jdbcinsert service?

Put some debug log in the subscribing service (note:the service input should be fully qualified published document name)

HTH,
RMG

Hi Ram,

Since I am getting the data in the form of document from Broker whenever the the trigger fires on insert, Do I need to convert the document byte/stream etc… and map it to the insert adapter service?

Can you please guide me.

Thank you in advance,
Priyatham Porika

Hi Ram,

I checked the logs: This is what I am getting.

2006-01-19 13:25:00 CST [ISS.0098.0006V2] Consumer:PorikaPractice.InsertNotification.Work:CustomNotifyTrigger received 1 Documents from Broker
2006-01-19 13:25:00 CST [ISC.0050.0019V2] Copy failed: No source data available: to=/InsertUnitAssMentsTabInput/PERSON_NAME, from=/CustomInsert/PERSON_NAME
2006-01-19 13:25:00 CST [ISC.0050.0019V2] Copy failed: No source data available: to=/InsertUnitAssMentsTabInput/DEPARTMENT_NAME, from=/CustomInsert/DEPARTMENT_NAME
2006-01-19 13:25:00 CST [ISC.0050.0019V2] Copy failed: No source data available: to=/InsertUnitAssMentsTabInput/MANAGER, from=/CustomInsert/MANAGER
2006-01-19 13:25:00 CST [ISC.0050.0019V2] Copy failed: No source data available: to=/InsertUnitAssMentsTabInput/DIRECTOR, from=/CustomInsert/DIRECTOR
2006-01-19 13:25:00 CST [ISC.0050.0019V2] Copy failed: No source data available: to=/InsertUnitAssMentsTabInput/PROJECTS, from=/CustomInsert/PROJECTS
2006-01-19 13:25:00 CST [ADA.0001.0103V1] Begin local transaction.
2006-01-19 13:25:00 CST [ADA.0001.0104V1] Commit local transaction.

I inserted a record in the table from Sql*plus and I checked the log. I got the following log above. Any idea?

Thanks,
Priyatham

Those messages are normal when your mapping from fields which don’t contain data. Add a pub.flow:tracePipeline statement to your triggered flow service to see what information is being passed. You should see the contents of the published document type in the console or server log.

You might need to map data retrieved from your database notification to get it in the right format for your subsequent insert, but that’s completely dependent on your data. No way to tell from here.

Mark

Hi Mark/Ram,

I was able to debug and solve the problem. Thank you so much for your help. Now I am ready for my next task. I learned it and now I am going to implement it.

Have a wonderful weekend!!!
Priyatham

Glad to know it is working.(:

Have a wonderful weekend!!!

hi rmg,
i have very much similar situation ,but i am unable to create the insert adapter notification, the following errors are displayed when i try to schedule the polling of this notification on IS
Error in Notification Callback:enableCallback notification
Cannot execute the SQL statement "CREATE TABLE WMBbcagefc ---------
Can’t start a cloned connection while in manual transaction mode

can u tell me why am i getting these errors,
its unable to create the buffer table .and other errors as u can see them above.
thanks
sri

Do your table have enough permissions/privileges like triggers,synonyms,updates etc…for the wm user that trying to connect to SQL DB?

Please make sure the user have certain permissions when you create table notifications.

HTH,
RMG

Hi priyatham
I too got the same problem.could u please tell me how to solve that problem

thanks