Enable PLSQL Trace with bindings

Sql commands to enable/disable trace in Oracle plsql session.

ALTER SESSION SET sql_trace = true;

ALTER SESSION SET tracefile_identifier = 'pm_test1trace';

--enable trace with bindings and wait

ALTER SESSION set events '10046 trace name context forever, level 12';

--set of plsql blocks to be traced

select * from cz_devl_projects where name like 'PM 10';

ALTER SESSION SET sql_trace = false;

select * from v$parameter where name like '%user%dest%';

Validate Existing OAF Session in JSP/Servlet

One way to validate OAF session in custom jsp/servlet in Oracle applications. Method will return "INVALID" if the current session is invalid, "EXPIRED" if current session is expired and "VALID" if current session is valid.
You can also create new session if current session is invalid, wiill post detail in some other post. But, usually if Functionality is part of existing oracle application module, then you will just want to validate if current session is valid or not. If not, you may want to redirect to /OA_HTML/AppsLocalLogin.jsp page.

public static String validateOAFSession(HttpServletRequest request, HttpServletResponse response) {
    WebAppsContext appsContext = null;
    try {
        appsContext = WebRequestUtil.createWebAppsContext(request, response);
        if (appsContext != null) {
            String sessionCookie = WebRequestUtil.getSessionCookie(request, response, appsContext);
            if (sessionCookie == null) {
                return "INVALID";
            }
            if (!appsContext.validateSession(sessionCookie, true)) {
                return "EXPIRED";
            }
        }
    } catch (IOException ioe) {
        return "INVALID";
    } finally {
        if (appsContext != null) {
            appsContext.freeWebAppsContext();
        }
    }
    return "VALID";
}

Oracle: Find locking session and Kill

Usually you will see some query taking a lot of time and sometime never returns. Well, if it returns ever, you nee d to improve the explain plan of the query.
But if it never returns (usually update query) then it could be a deadlock in the system somewhere. To find out that particular session and query, following query will give more fruitful information for it.

I found if useful for debugging few issues in Oracle Configurator when some page does not return on Apply button click. Well, but in general following queries are not specific to oracle applications, you can use it anywhere to find locking sessions and eventually kill them.

SELECT sq.*, ses.*
FROM   v$sql sq, v$session ses
WHERE  sq.sql_id = ses.sql_id
 and ses.sid IN (SELECT sid
            FROM   v$lock
            WHERE  (id1,id2,TYPE) IN (SELECT id1,
                                             id2,
                                             TYPE
                                         FROM   v$lock
                                         WHERE  request > 0))
;

Find "BLOCKING_SESSION", analyze and kill if required.

select * from v$session where sid=310;


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


ALTER SYSTEM KILL SESSION '276,2323' IMMEDIATE;

Oracle Configurator

Oracle Configurator is

• an SCM (Supply Chain Management) family product in Oracle Applications
  
• integrates with Order Management, Quoting, iStore and other custom applications allowing them to configure its product.
• helps in building a Guide Selling flow
  
• allows the product to be configured according to custom defined rules and User Interfaces (UIs).
  
• manages the set of UIs (publications) for different application for different effectivity dates.
• has CZ as code name in Oracle Applications
• webbased product
  

From Oracle's website
Oracle Configurator application integrates with Oracle CRM and Supply Chain Management so you can offer guided selling to your customers. It actively gathers customer requirements and then maps them to a set of product or service options. Oracle Configurator supports Internet sales and telesales as well as other channels.

What to expect from this product:

To easily understand the end result CZ product, visit Dell website and configure any laptop.
While configuring the laptop, you will find that some items in the laptop product (like RAM and OS, Port and respective Accessories) may not be compatible with each other. Like, Windows Vista is not compatible with less than 1GB RAM. You may see some items selected/deselected automatically when you select certain items. You will also see some items appearing and disappearing in webpage UI based on certain selections.

Such compatibility and incompatibility of the items in product are created and managed by product developer. These are called Rules in CZ. CZ Rules can select/deselect certain items based on defined criteria.
The display layout of certain items, and their visibility/invisibility is defined in CZ UIs.
Once, you have the UIs, you can create a copy (although copy not an appropriate term, but gives good understanding about model publications) of UIs and send it to various applications registered in Oracle Applications. Process of creating this backup copy is called Publications.

Well, above example talked only about Laptop product model. You can create Rules and UIs for any product (having however complex its structure may be, usually a BOM-Bill of Materials model).
In addition to product's defined structure items, you can create additional nodes/items (in CZ, they are called non-BOM nodes) in the product model which help to create guided selling flow for the product. Like in above laptop example, first you may select the use of the laptop (like Home-use or Office-use) and certain selections will be made automatically (through CZ rules) for you to create laptop configuration according to selected requirement.