Hello – I need to trigger execution of stored procedure using event based scheduling. Here the scenario...
1. Siebel application submits the Order
2. The Order Id and XML Payload is inserted in Oracle JMS queue (In COORID and USER_DATA respectively)
Now I need to trigger Oracle stored procedure and pass Order Id and XML message as parameters to the procedure
How can I do that?
Appreciate any help.
What do you do with JMS queue ?
You could cretate job that run in reluglar interval (every minute) and it dequeue message from JMS queue and then call you stored procedure.
Or create after insert trigger on JMS queue nad submit event. THis event run you event based job. But I don't recomed this because I'm not sure how it will work.
It's similar solution to regular job that dequeue messages from queue. There is only small time delay.
JMS queue will have primary key (order id) of the order message. once I dequeue message from JMS queue, I'll fire stored procedure and extract Order XML message by passing order id using SQLX function from Siebel database tables
Once I extract order XML message I'll again enqueue the XML message in weblogic server queue.
I want to use event based scheduling to do this if possible. Oracle documentation explains it with queues with object type payload.
Siebel only supports JMS queues.
please take a look
I ask you again.
Why it is so critital for you to submit event immediately after new record in inserted in JMS queue and then in wake up job process it.
When you create job that run every minute then delay is 1 minite maximaly.
How many records per minute is inserted to JMS ?
I want to post message to weblogic server as soon as possible. The stored procedure would be executed as soon as Order is submitted. There could be more ~20-25 order submitted within a minute.
Actually Sales rep who is submitting the order is waiting for successful completion of the order. Our middleware (AIA and weblogic) would route it to fulfillment and billing systems immediately and Siebel would gets the response immediately (with in few sec). So after order submission, rep will see the Order status within few seconds
If we wait for a min it's not good user experience.
I want to use parallel_instances = True so that we can spawn multiple actions at the same time for each JMS event.
Appreciate your help
Yes, it is possible but it will require customization of packaged application which is costly to develop, maintain and upgrade.
That's why we are looking into other ways of implementation and I feel Oracle XMLDB, JMS queue, Oracle scheduler etc. can avoid lot of Siebel customization.
I can't find any place in the documentation that says JMS queues are not supported for event based job. I've tried event based scheduling using object type queues and its working fine.
If somehow we implement JMS event based scheduling it will save us lot of out time, cost and maintenance.
Oracle XMLDB is a way to go instead of relying on packaged application EAI capability :)