1 2 Previous Next 20 Replies Latest reply: Feb 5, 2013 8:11 AM by 989231 RSS

    Custom Event Subscription

    190407
      I am in the process of the implementing our own custom events, and for my initial testing I have created the custom event and a subscription on that. When I test the event from workflow administratror with the test the subscription is raising and feeding the message into custom table. If I call the same custom event from a pl/sql program then the subscription is not raising, and I think I am missing something here. Any one have any idea?

      Thanks,
      Srini C
        • 1. Re: Custom Event Subscription
          48771
          Try making the PL/SQL an autonomous transaction. Also, if it is compiled and you have made changes, you may have to bounce the WF Background Managers.
          • 2. Re: Custom Event Subscription
            190407
            When I made with the pl/sql with autonomous then I am getting the following error.

            ORA-06519: active autonomous transaction detected and rolled back

            Thanks,
            Srini C
            • 3. Re: Custom Event Subscription
              48771
              Can you post your code here?
              • 4. Re: Custom Event Subscription
                190407
                CREATE OR REPLACE PROCEDURE schl_test_event
                (p_event_name IN VARCHAR2,
                p_event_key IN VARCHAR2,
                p_data IN CLOB DEFAULT NULL,
                p_parameters IN wf_parameter_list_t DEFAULT NULL)
                IS
                l_x_header_id NUMBER := 4567;
                match_date VARCHAR2 (20);
                l_item_key VARCHAR2(240);
                l_event VARCHAR2(240);
                PRAGMA AUTONOMOUS_TRANSACTION;

                BEGIN
                --l_event := event(p_event_name);

                Wf_Event.Raise
                ( p_event_name => l_event,
                p_event_key => p_event_key,
                p_parameters => p_parameters,
                p_event_data => p_data);

                INSERT INTO schl.schl_event_test
                VALUES (1234);
                commit;
                END;


                While executing this I am giving the following statement.

                exec schl_test_event('schl.apps.ont.book.order','4567',null,null);

                Thanks,
                Srini C
                • 5. Re: Custom Event Subscription
                  48771
                  Add an exception block to make sure you have explicitly committed or rolled back the transaction:

                  exception
                  when others then
                  -- Raise or log an error
                  rollback;
                  • 6. Re: Custom Event Subscription
                    190407
                    The procedure is executing successfully but the event subscription is not raising from the schl.apps.ont.book.order event, if I perform the test from the event from the wf administrator the subscription is raising. Why it's not raising when I call it as shown in the previous post?
                    I would highly appreciately your help on this, Thanks again.

                    Thanks,
                    Srini C
                    • 7. Re: Custom Event Subscription
                      48771
                      Did you bounce the WF Manager? Try that and try your event again.
                      • 8. Re: Custom Event Subscription
                        190407
                        yes, I bounced it. No help with it.

                        Thanks,
                        Srini C
                        • 9. Re: Custom Event Subscription
                          48771
                          When you run your stored procedure manually, it inserts the record into the table correct? Is this an EBS env or standalone WF? If EBS, make sure apps and applsys have exec permissions on your sp.

                          If you put your subscription on a standard event, does it fire?

                          Steve
                          • 10. Re: Custom Event Subscription
                            190407
                            It inserts the records when I run my stored procedure manually and it inserts the records when I do the event test from workflow admin responsibility it inserts the records. This is EBS 11.5.10 version. Issue with when I call it in the external pl/sql the subscription is not raising.

                            I did the test on a seeded event 'oracle.apps.ont.oi.po_inbound_create' and it's behaving same as custom event, from wf administrator event testing the subscription is raising and when I call it in pl/sql the subscritption is not raising. I have used the following code, and the test procedure code is attached earlier post. Still couldn't find the reason for this.

                            exec schl_test_event ('oracle.apps.ont.oi.po_inbound.create',4567,null,null)

                            Thanks,
                            Srini C
                            • 11. Re: Custom Event Subscription
                              48771
                              How are you raising the custom event? Is it from a form, trigger, workflow, or other procedure? Are you sure your calling method is raising the event and not throwing an error?

                              Have you tried raising the event manually from pl/sql (wf_event.raise)?

                              Steve
                              • 12. Re: Custom Event Subscription
                                190407
                                I am raising it with the wf_event.raise and the procedure execution is successfully completing and not throwing any exception, but the subscription is not raising.

                                Thanks,
                                Srini C
                                • 13. Re: Custom Event Subscription
                                  48771
                                  Do you have a commit in your calling procedure? You need to have one if you don't.

                                  Steve
                                  • 14. Re: Custom Event Subscription
                                    190407
                                    Yes, I do have commit in my calling procedure.
                                    1 2 Previous Next