I am not a theatrical guy and probably never will be. My only foray into a production was as a big bully in a church production. But I must say that it was an good experience. The singing recordings, rehearsals, stage management was an eye-opener in all that is required to produce a decent play. [ And its tough work - it takes a passionate person to do this kind of stuff - so hats off to all who are involved in theater! ]

It is no wonder that I can sometimes confuse stage right from stage left - and when my scene ended, I sometimes ended up on the wrong side of the stage. Thankfully, only during rehearsals

So when one fine day, as I was living my day-life as an OTMer, I came across a problem where Action Checks on Order Base Templates failed to work.

As usual, my team and I went through all the necessary scenarios and did all the checks, double checks, triple checks and still more log checks - did we finally raise this to Oracle. And so it is that we found out that Action Checks was broken for Order Base Templates in CU3 RU(x) (where X does not seem to end yet!). I was told that this was fixed in CU4 RU1 - but Murphy's Law dictated that I was not to use CU4 due to other issues - sigh!

So I dug deep into the OTM Help and came across USER EXITS. What are they? In short, it is a way for users to programatically execute business logic on a business object before it is persisted to the database.

Unfortunately, OTM's notoriously vague help was as usual - notoriously vague - leaving me with nothing but PL/SQL code to figure out whats happening behind the scenes.

I decided to use USER_EXITS to do some user input validation on order bases created with Order Base Templates. So how do we do this? Lets start!

Step 1 – Identify the USER_EXIT Sql package
Whenever OTM tries to persist an business object, the PKG_USER_EXIT will be called prior to COMMIT. This means that depending on the return value of the procedures in this package, OTM will either commit or rollback and return an error message.

There is only 1 public procedure called to_exit. DO NOT CHANGE the procedure interface as this is called by OTM Java code and is the only interface used by OTM from this package. However, you may change the procedure body so as to implement the necessary business logic. A description of the to_exit interface can be found in the package header. RTFN (read the fine notes )

Step 2 – Create your own business validation logic in a separate PL/SQL package
This is a matter of preference but I always encapsulate custom business logic in a separate package for easy maintenance. You can create your own stored procedures with the following structure:

The section to note is the EXCEPTION block where I raise the RAISE_APPLICATION_ERROR exception. I am using OTHERS as a generic catch all clause to catch any exceptions that I raise in my code and then pass it on to the calling code the sql error code and sql error message.

• Error Code -20001 is used as per Oracle guidelines on raising your own custom exceptions.

• DBMS_UTILITY.FORMAT_ERROR_STACK allows us to send the error message up to 4K in size – and as the function describes, it provides a stack trace of all the errors up to this point.

Step 3 – Call your custom business logic from within TO_EXIT procedure
Once you have created your business logic procedure, you must then call your stored procedure from within the TO_EXIT procedure. The this is my custom code that is called from within TO_EXIT. Please note the use of pragma exception_init to catch the exception raised from my custom code.

Note: The following code is provided as-is

Step 4 – Set property to enable USER_EXIT functionality
To get the TO_EXIT procedure to be called by OTM, you must set the property glog.persistence.callout=on for both the web and application servers. Once done, you need to reboot the web and application servers.

And you should be good to go!

Hope this helps you to exit the stage correctly! Happy OTMming!