Friday, December 28, 2007

Passing pl/sql table to Java using Oracle JDBC and vice - versa

While replying several threads on OA Forums(, I have seen couple of threads where developers have a requirement to pass a pl/sql table to OA Framework Page and vice-versa.Even if people copy paste this code from google. oracle or other available sources, they are not sure what actually they are doing!
Actually the best practice for such a requirement is to use Rosetta to generate class file for the particular pl/sql pkg.But the point is Rosetta is Oracle's internal tool and Oracle does not ship it to customers.Then also,I have seen it to be used in consulting industry, very often, i don't know how :)!But if u don't have rosetta, u don't have an option that to go with jdbc, which is anyways base of Oracle and Java bridge.When talking of JDBC, there is two set of JDBC classes popular in market, one from SUN(Founder of Java)and Oracle.Oracle jdbc wrapper classes are better in the sense, that they are flexible enough to cater almost all pl/sql objects.
Ok, so if you are not using Rosetta, then how would u pass pl/sql table to Java using Oracle JDBC and vice - versa?Recently was helping a friend in this code, had to some struggle for this code , so thought to put it on my blog, so that would help other developers with similar requirement.

So, lets start:
Read comments carefully to understand the code

1)Define Object with the same structure as your table of records u need to use in ur pl/sql procedureof some pkg.Remember define it as global object type and not inside package specification, because in that case our jdbc code would not be to find this object type.
/*The Script used to create Object type in this example is as follows:*******/
create or replace type rectype as object(col1 varchar2(10),col2 Date,col3 Number);

2)Define table of object u have defined above which u need to use in ur pl/sql procedure of some pkg.Remember define it as global table type and not inside package specification, because in that case our jdbc code would not be to find this object type.
/*The Script used to create table of Object type is as follows:*******/
create or replace type rectab as table of rectype;

3)Defining package specification and procedure definition
/*The Script used to create package specification in this as follows:*******/

create or replace package ioStructArray as
procedure testproc(iorec in rectab,orec out rectab);
end ioStructArray;

4)Defining package body and procedure
/*The Script used to create package specification in this as follows:*******/
create or replace package body ioStructArray as
procedure testproc(iorec in rectab,orec out rectab) is
/*see how to loop and assign values*/
for i in 1..iorec.count loop
iorec(i).col1 := orec(i).col2;
iorec(i).col2 := orec(i).col1;
end loop;
end testproc;
end ioStructArray;

5)Getting connection object in JDBC Code :
//Getting db connection in a jdbc
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
Connection conn=DriverManager.getConnection ("jdbc:oracle:oci8:@S692815.WORLD",
"scott", "tiger");

6)If writing in AM in OAF page, code to get connection:
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getOADBTransaction();
OracleConnection conn = (OracleConnection)oadbtransactionimpl.getJdbcConnection();

7)Lets, consider a simple scenario where we have a XXVO in AM and I have to pass all VO rows data to the pl/sql procedure we just created and then receieve a table of data back and then based on it values do something in AM.Here is the Code:
import oracle.sql.*;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;

Public void arryToPLSQL()
//Getting Db connection
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getOADBTransaction();
OracleConnection conn = (OracleConnection)oadbtransactionimpl.getJdbcConnection();

//Defining variables

//oracle.sql.ARRAY we will use as out parameter from the package
//and will store pl/sql table
ARRAY message_display = null;

//ArrayList to store object of type struct
ArrayList arow= new ArrayList();

//StructDescriptor >> use to describe pl/sql object
//type in java.
StructDescriptor voRowStruct = null;

//ArrayDescriptor >> Use to describe pl/sql table
//as Array of objects in java
ArrayDescriptor arrydesc = null;

//Input array to pl/sql procedure
ARRAY p_message_list = null;

//Oracle callable statement used to execute procedure
OracleCallableStatement cStmt=null;

//initializing object types in java.
voRowStruct = StructDescriptor.createDescriptor("RECTYPE",conn);
arrydesc = ArrayDescriptor.createDescriptor("RECTAB",conn);

catch (Exception e)
throw OAException.wrapperException(e);

for(XXVORowImpl row = (XXVORowImpl)XXVO.first();
row = (XXVORowImpl)
//We have made this method to create struct arraylist
// from which we will make ARRAY
//the reason being in java ARRAY length cannot be dynamic
//see the method defination below.

//make array from arraylist
STRUCT [] obRows= new STRUCT[arow.size()];
for(int i=0;i < arow.size();i++)

p_message_list = new ARRAY(arrydesc,conn,obRows);
catch (Exception e)
throw OAException.wrapperException(e);

//jdbc code to execute pl/sql procedure
=(OracleCallableStatement)conn.prepareCall("{CALL ioStructArray.testproc(:1,:2)}");

//getting Array back
message_display = cStmt.getARRAY(2);
//Getting sql data types in oracle.sql.datum array
//which will typecast the object types
Datum[] arrMessage = message_display.getOracleArray();

//getting data and printing it
for (int i = 0; i < arrMessage.length; i++)
oracle.sql.STRUCT os = (oracle.sql.STRUCT)arrMessage[i];
Object[] a = os.getAttributes();
System.out.println("a [0 ] >>attribute1=" + a[0]);
System.out.println("a [1 ] >>attribute2=" + a[1]);
System.out.println("a [2 ] >>attribute3=" + a[2]);
//You can typecast back these objects to java object type


catch (Exception e1)
throw OAException.wrapperException(e1);

/*Our custom method which will populate
arraylist with struct object type
public void populateObjectArraylist( XXVORowImpl row,StructDescriptor voRowStruct , ArrayList arow)
Object[] attribMessage = new Object[3];
String attr1 = null;
Date attr2 = null;
Number attr3 = null;

//Get value from Vo row and put in attr1,att2 and attr 3

//Putting values in object array

STRUCT loadedStructTime = new STRUCT(voRowStruct, conn, attribMessage);
catch (Exception e)


So, i hope every step is clear in the above code. I hope it helps all OAF developer community.Special Thanks to Pooja Arora for her contribution and constantly bugging me to fix this!

Sunday, December 16, 2007

PPR-- An insight !

PPR or Partial Page Rendering is one of the most attractive feature put up by Oracle in 11.5.10 release of Oracle Apps, or OA Famework.
Although most of OAF developer community use this feature, i think very few would have idea, how excatly it works in UIX.PPR uses Ajax kind of design and hence very attractive to use, because it can put dynamic features in various UIX beans, without the use of any client side script like javascript, which is otherwise a integral part of any web application. PPR events are very fast as they refresh only a particular region or protion of page and no the entire page.

Developers that want to add such behaviors to their web pages are often faced with a difficult decision. All of these actions can be implemented using a very simple solution: by refreshing the entire page in response to the user interaction. However easy, this solution is not always desirable. The full page refresh can be slow, giving the user the impression that the application is unresponsive. Another option is to implement such actions using JavaScript (or other client-side scripting technologies). This results in faster response times, at the expense of more complex, less portable code. JavaScript may be a good choice for simple actions, such as updating an image. However, for more complicated actions, such as scrolling through data in a table, writing custom JavaScript code can be a very challenging undertaking.

Oracle UIX provides another solution which avoids some of the drawbacks of the full page refresh and custom JavaScript solutions: partial page rendering (or PPR for short). The UIX partial page rendering functionality provides the ability to re-render a limited portion of a page. As in the full page render solution, PPR sends a request back to the application on the middle-tier to fetch the new contents. However, when PPR is used to update the page, only the modified contents are sent back to the browser. UIX automatically merges the new contents back into the web page. The end result is that the page is updated without custom JavaScript code, and without the loss of context that typically occurs with a full page refresh.

How Partial Page Rendering Works
The partial page rendering process breaks down into three main areas: the partial page event, the partial page rendering pass, and the partial page replacement.

The partial page event is the request that the browser sends to the application to request new contents. Partial page events are very similar to their full page event equivalents. For example, when the user navigates to a new record set in a table bean, a goto event with a value event parameter is sent to the application regardless of whether the event is a partial or full page event. There are two important differences between partial and full page events. First, partial page events specify partial page rendering-specific event parameters which are not present on the full page event equivalents. For example, partial page events may include an event parameter which identifies the set of nodes that should be re-rendered (referred to as "partial targets"). The second difference between partial page events an full page events is how the events are sent.

Unlike full page events, partial page events must be sent in a way which does not force the browser to reload the current page. To implement this capability, UIX partial page rendering uses a hidden inline frame (iframe) as a communication channel between the browser and the web application running on the middle-tier. Partial page events are sent by forcing a navigation in the hidden iframe, which in turn causes a request to be sent to the application on the middle-tier. Since the iframe is hidden, the process of sending a partial page event and rendering partial page contents can take place behind the scenes, without discarding the contents of the current page.

When the partial page event is received by the application, the application responds by determining the set of partial targets to render and performing the partial page rendering pass. The partial page rendering pass is similar to a full page rendering pass. In both cases, the UINode tree is traversed by calling render() on each node in the tree. However, in the partial page rendering case, only the contents generated by the partial targets are actually sent back to the browser. All other contents are dropped. So, although the scope of a partial page rendering pass and full page rendering pass are similar in the number of UINodes that are rendered, the partial page response is generally much smaller, since only the modified contents are sent back to the browser.

The final part of the PPR process is the partial page replacement. When the browser receives the partial page response, the new contents for each partial target node are copied from the hidden iframe into the main browser window, replacing the existing contents for each target node. So, for example, in the table navigation case, rather than replacing the entire page, just the contents of the table itself are replaced. The browser reflows in response to the modifications, displaying the new contents to the user without fully re-rendering the entire page.

To recap, the sequence of steps which occur during a partial page render are:

The initial page is rendered.
The user performs some action which triggers a partial page render, for example clicking on a link or button.
JavaScript event handlers provided by UIX force a navigation in a hidden iframe.
The partial page event is sent to the application.
The application determines whether the request is a partial page event and which partial target nodes to re-render.
The partial page rendering pass is performed.
The contents of the partial target nodes are sent back to the iframe in the browser.
The partial page replacement is performed, at which point the new contents are copied from the iframe into the main browser window.
The browser re-flows and displays the new content to the end user.
Note that the partial page rendering process requires no custom JavaScript code.
(The source of this article is Oracle UIX documentation.)

Wednesday, November 21, 2007

What the hell is JVM , Session , Cache ?

From quite some days, I find threads on OAF Forums, where people could not understand the basic terminology of J2EE like session,JVM , Oracle user session, cache etc. I think these terms are not new for people from Java background, but for people who come from pl/sql background or not from Java background, these terms might look like hammers.I remember one thread where I and Tapash were replying somebody asked whats' the difference between ICX session and http session, I wanted to reply, but I thought, replying there would make thread too long, i guess that thread already crossed 4 pages and replying there may not reach to all target audience.
So, I decided to write a small article for the same..... and in fact I must say my article is nothing but compilation of information from OAF Developer's guide , googling , metalink, blogs and lastly my knowledge....[You must be doubting on the last point.... even i am :)]

So, lets start with basic terminalogy:

About JVM
At the heart of the Java platform lies the Java Virtual Machine, or JVM. Most programming languages compile source code directly into machine code, suitable for execution on a particular microprocessor architecture. The difference with Java is that it uses bytecode - a special type of machine code.
Java bytecode executes on a special type of microprocessor. Strangely enough, there wasn't a hardware implementation of this microprocessor available when Java was first released. Instead, the processor architecture is emulated by what is known as a "virtual machine". This virtual machine is an emulation of a real Java processor - a machine within a machine (Figure One). The only difference is that the virtual machine isn't running on a CPU - it is being emulated on the CPU of the host machine.

The Java Virtual Machine is responsible for interpreting Java bytecode, and translating this into actions or operating system calls. For example, a request to establish a socket connection to a remote machine will involve an operating system call. Different operating systems handle sockets in different ways - but the programmer doesn't need to worry about such details. It is the responsibility of the JVM to handle these translations, so that the operating system and CPU architecture on which Java software is running is completely irrelevant to the developer.

The Java Virtual Machine forms part of a large system, the Java Runtime Environment (JRE). Each operating system and CPU architecture requires a different JRE. The JRE comprises a set of base classes, which are an implementation of the base Java API, as well as a JVM. The portability of Java comes from implementations on a variety of CPUs and architectures. Without an available JRE for a given environment, it is impossible to run Java software.
Differences between JVM implementations
Though implementations of Java Virtual Machines are designed to be compatible, no two JVMs are exactly alike. For example, garbage collection algorithms vary between one JVM and another, so it becomes impossible to know exactly when memory will be reclaimed. The thread scheduling algorithms are different between one JVM and another (based in part on the underlying operating system), so that it is impossible to accurately predict when one thread will be executed over another.
Initially, this is a cause for concern from programmers new to the Java language. However, it actually has very little practical bearing on Java development. Such predictions are often dangerous to make, as thread scheduling and memory usage will vary between different hardware environments anyway. The power of Java comes from not being specific about the operating system and CPU architecture - to do so reduces the portability of software.


The Java Virtual Machine provides a platform-independent way of executing code, by abstracting the differences between operating systems and CPU architectures. Java Runtime Environments are available for a wide variety of hardware and software combinations, making Java a very portable language. Programmers can concentrate on writing software, without having to be concerned with how or where it will run. The idea of virtual machines is nothing new, but Java is the most widely used virtual machine used today. Thanks to the JVM, the dream of Write Once-Run Anywhere (WORA) software has become a reality.

JVM Cache and Its impelemetation in Oracle Apps:

Qestions like what is cache? How is it related to JVM? How is caching implementated in Oracle Apps?Instead of me explaining this, it would be best if you go through the brilliant explanation by Mike Shaw on Steven Chan’s blog.Here is the link:
Mike Shaw's article about JVM Cache


HttpSession is nothing but a java interface.The servlet container uses this interface to create a session between an HTTP client and an HTTP server.
The session persists for a specified time period, across more than one connection or page request from the user. A session usually corresponds to one user,
who may visit a site many times. The server can maintain a session in many ways such as using cookies or rewriting URLs.
This interface allows servlets to View and manipulate information about a session, such as the session identifier, creation time, and last accessed time
Bind objects to sessions, allowing user information to persist across multiple user connections.

ICX Session or Oracle Applications User Session
(Is it same as HTTP session/servelet session ?)

A http session or a servelet session usually corresponds to an application login/logout cycle, but that is not strictly true in the case of OA Framework applications.I tell u y ? When the user logs in to an OA Framework application, the OA Framework creates an AOL/J oracle.apps.fnd.common.WebAppsContext object and a browser session-based cookie that together keep track of key Oracle Applications context information like the current responsibility, organization id and various user attributes such as user name, user id,
employee id and so on.
The cookie contains an encrypted key identifier for a session row stored in the Applications database.Specifically, this is the servlet session ID which, in its decrypted form, serves as the primary key in the
ICX_SESSIONS table.(to get an idea do run Select * from ICX_SESSIONS , to get an exact idea of what information is stored in the table)
The WebAppsContext retrieves this key value after each request and uses it to query the current session state.
The Oracle Applications user session or ICX SESSION is associated and not dependent with a servlet session, because , it has its own life cycle and time-out characteristics. Generally, the Oracle Applications user session has a longer life span than the servlet session. The servlet session should time-out sooner.

User session is dependent on following profiles:

a)ICX: Limit Time :Determines the maximum Oracle Applications user session length( in

b)ICX:Session Timeout: Maximum idle time for the Oracle Applications user session
(specified in minutes).

While servelet session timeout is purely dependent on setting in Apache Jserv session timeout setting.

An Oracle Applications user session might be associated with multiple http servlet sessions. For example, the servlet session times out
while the user takes a phone call in the middle of creating an OA Framework expense report, then resumes work before the Oracle Applications user session
times out.If the Oracle Applications user session times out, aslong as the user does not close the browser window (so the browser session-based cookie isn't
lost) and no one deletes the corresponding session row in the ICX_SESSIONS table, the user can resume her transaction at the point where he stopped working
after being prompted to log back in.
Although the best practice as per Oracle standards is to sync ICX_SESSION and servelet session,till passivation feature is implenented in Oracle Apps. Passivation is still not supported in 11i and 12i.

This is straight from metalink “The ICX: Session Timeout option sets the the maximum number of minutes to wait before invalidating an idle ICX Session. The default value is null. The web server session timeout value, or more appropriately the Apache Jserv Session value is used to specify the number of milliseconds to wait before invalidating an unused session. The default value is 1800000 or 30 minutes.
We recommend that you set the ICX: Session Timeout and the Apache Jserv Session to be the same. It's better to be consistent and let the ICX session and the Apache Jserv (middle tier) session expire at the same time. If the ICX session expires before the Jserv session, the user will be presented with a login page even though the Jserv session is still active. If the user logs back in before the Jserv session expires, they will see the old state of their middle-tier transaction. This can be confusing, since from the point of view of the user there is no distinction between the ICX session and the Jserv session.
We also recommend that you do not set the Apache Jserv Session timeout to be any higher than 30 minutes. Longer idle sessions will drain the JVM resources and can also cause out of memory errors.
The session timeout for the webserver is specified via the following directive in the /Jserv/etc/ file.
session. timeout=1800000”

I think by now you must be crystal in ur understanding of basic key terms in J2EE.I would like to stress on one thing... JVM cache is shared across servelet/http sessions , and is purely dependent on first login, it can be removed progrmatically by invalidating the cache or by bouncing the apache server.

Any doubts or queries are welcome!

Friday, November 2, 2007

Setting the default selected date for an OAMessageDateFieldBean

I have seen couple of threads in recent past on OA Forums about setting default value in OAMessageDateFieldBean, so, did a little R n D :) on OAMessageDateFieldBean.

OAMessageDateFieldBean in framework, u will find its nothing but a onClick js event is called on the imageicon attached with messagetextinput bean(in case the type is Date) and OAInlineDatePickerBean is opened in a modal js window.

Setting deafult date in the OAMessageDateFieldBean, also sets the OAInlineDatePickerBean , as it takes the default value from OAMessageDateFieldBean.
So, here is code for setting/getting value both in both in OAMessageDateFieldBean and OAInlineDatePickerBean :
import java.sql.Date;
import java.text.SimpleDateFormat;

//defining format of date
SimpleDateFormat f = new SimpleDateFormat("mm/dd/yyyy");
//define date as string
String dateString = "06/30/1984";
//defining new
Date sqlDate=new Date(f.parse(dateString).getTime());

OAMessageDateFieldBean dateField = (OAMessageDateFieldBean)webBean.findIndexedChildRecursive();

//getting oracle.jbo.domain.Date object from OAMessageDateFieldBean
//in process form request
Timestamp ts = (Timestamp)dateField.getValue(pageContext);
java.sql.Date select = new java.sql.Date(ts.getTime());
oracle.jbo.domain.Date sd=new oracle.jbo.domain.Date(select);

//You can also set min Value and Max value in OAMessageDateFieldBean which
//will also be set in OAInlineDatePickerBean
//Here is code

I hope this helps all.

Tuesday, September 25, 2007

Implementing a simple watch in a OA Framework Page

I hope this is interesting ... implementing a watch in OA Framework page.There can be several other functionalities, one can do after implementing this watch.Here i am just writing a simple function for javascript watch,you can take an idea.... and change this function accordingly.

Just read this function.....

/* Here is the javascript function, which is self explainatory
Read this function carefully
to understand it.*/

function update()
//Define a new date object
var today=new Date();

var hours=today.getHours();
var minutes=today.getMinutes();
var seconds=today.getSeconds();

//for formatting output
if (hours<10)
if (minutes<10)
if (seconds<10)

//writing output to message text input

//refreshing ever sec

I hope this clear. Now here are the steps how you can put it in your OA Framework page.Add a message text input to your page, say its id is "OAclockDisplay".

Now add this code in process request of controller of page:

//storing javascript function is a string
// "\" is used as escape character
String s="function update(){var today=new Date();var hours=today.getHours();var minutes=today.getMinutes();var seconds=today.getSeconds();if(hours<10){hours=\"0\"+hours;} if(minutes<10){minutes=\"0\"+minutes;} if(seconds<10){seconds=\"0\"+seconds;} document.getElementById('OAclockDisplay').value=hours+\":\"+minutes+\":\"+seconds;setTimeout(\"update()\",1000);}";

//getting body bean
OABodyBean bodyBean = (OABodyBean)pageContext.getRootWebBean();

//attaching javascript function to page

// calling this function on load of body bean
String javaS = "javascript:update();";

We are done, run this page you will be able to see a watch in oa framework page in that particular message text input.

Friday, September 14, 2007

Few Useful threads

Here is the list of few useful of the many threads replied by me on OA forums:

1)Dynamic tool tip in classic table

2)Putting a different color message in OA Framework page for instrtuction text etc:

3)How to make the cursor as "Busy" (i.e. HOURGLASS) and not like the usual "ARROW"..., when ur doing lot of tasks on an event

4)Delete Personilization:

5)Putting error message stack on OA page:

6)Make some rows' some columns read only in a table:

7)Not able to find server.xml

8)Runtime VO

9)Using alerts in OA Framework Page

10)Showing pl/sql exceptions in java(Also, see this thread if ur facing previous message stack retaining probling while using OAExceptionUtils class)

11)Dynamic CSS class generation in a OAF Page


Thursday, September 6, 2007

Dependent Dynamic message choicelists

This is one of the most common scenario in any web application page.Basically here i am just giving an idea how to code for dependent message choicelists in OA Framework page both at page level or in a collective ui feature like table or hgrid.

3 Message Choicelists in a Page :

Lets consider the first scenario, where we have 3 dependent message choicelists(mc1,mc2,mc3), these can be n in number, just follow the same approach.Here mc1,mc2 and mc3 are the item ids of the message choice lists in the page UIX file, which are attached to vo instances mc1VO1 and mc2VO1 and mc3vo1 respectively.Assuming "Intial value" property value is blank and "Add blank value" is "true" in property inspector for all three poplists.
Lets start from controller code, please read comments, as they explain each and every line..

Controller Code

/*Code in Controller*/
import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;

//Code in process request
OAMessageChoiceBean mc1 = (OAMessageChoiceBean)webBean.findChildRecursive("mc1");
/*The poplist data object that OA Framework creates the first
* time is cached in the JVM and reused
*until you explicitly disable caching
*Hence, use setPickListCacheEnabled API to stop JVM
* from caching Poplist values, this API needs to be called on any Poplist
* where the VO where clause keeps on changing
//Similarly for second Poplist
OAMessageChoiceBean mc2 = (OAMessageChoiceBean)webBean.findChildRecursive("mc1");

//Similarly for third Poplist
OAMessageChoiceBean mc3 = (OAMessageChoiceBean)webBean.findChildRecursive("mc3");

//Code in process form request
/*If a value is selected in mc1
*"update" is the name of PPR event
* attached to poplist mc1
if("update".equals(pageContext.getParamete(OAWebBeanConstants.EVENT_PARAM))) {
String value_selected=pageContext.getParameter("mc1");
//Priniting the value selected
System.out.println("value_selected in mc1>>"+value_selected);
//if the selected value is not null
if(!(("".equals(value_selected)) (value_selected==null)))
//then calling the method in AM which will reinitialise VO query
// in second message choicelist and not third because
//the value selected in seocond will null
//as we have turned Add blank value to true
//and initial value is null in property inspector
Serializable[] param = {value_selected};
am.invokeMethod("initmc2VOQuery", param);
// if user selects some value in first choicelist
// pass some dummy_value in mc3vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
Serializable[] param = {dummy_value};
am.invokeMethod("initmc3VOQuery", param);
// if user selects null in first choicelist
// pass some dummy_value in mc2vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
//doing this for mc2vo1 and mc3vo1
Serializable[] param = {dummy_value};
am.invokeMethod("initmc2VOQuery", param);
am.invokeMethod("initmc3VOQuery", param);

/*If a value is selected in mc2
*"update1" is the name of PPR event
* attached to poplist mc2
String value_selected=pageContext.getParameter("mc2");
//Priniting the value selected
System.out.println("value_selected in mc2>>"+value_selected);
//if the selected value is not null
if(!(("".equals(value_selected)) (value_selected==null)))
//then calling the method in AM which will reinitialise VO query
// in third message choicelist
Serializable[] param = {value_selected};
am.invokeMethod("initmc3VOQuery", param);
// if user selects null in second choicelist
// pass some dummy_value in mc3vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
//doing this only for mc3vo1
Serializable[] param = {dummy_value};
am.invokeMethod("initmc3VOQuery", param);

Dependent message choicelists in Table

Controller Code :

/*Cosider two message choicelist items HeaderPoplistItem and LinePoplistItem in table with item id "XXX".We will use setListVOBoundContainerColumn api,which is typically used for containers that repeat it's content multiple times and want to attach different set of records for each iteration, eg table or hgrid.*/

OATableBean XXX = (OATableBean)webBean.findChildRecursive("XXX");

/*LinePoplistItem is a poplist item, in which view def is filled and not view instance this is based on initial value in HeaderPoplistItem which is a poplistwith view instance name*/
OAMessageChoiceBean LinePoplistItem = (OAMessageChoiceBean)XXX.findChildRecursive("LinePoplistItem");
LinePoplistItem.setListVOBoundContainerColumn(0, XXX,"HeaderPoplistItem");

AM code
//Also note for runtime dependency of table of hgrid poplists, you have attach PPR in the LinePoplistItem and in your event method in AM write:

In this article , i have covered the Dependent Dynamic message choicelists in tables and independent poplists on page. Hope this scenario is now clear.

Friday, August 31, 2007

More updates on Javascript usage

Common errors in Pop up pages and their resolution:
One thing i missed out in previous post is the profile options related to pop up page .Sometimes while trying to run a pop up page you get errors like:
"You are trying to access a page which is no longer active....."

The root cause of this error are the profile options:
3)Framework Validation Level- None
They should be none in order to avoid the error.The root cause of the error is MAC key url validation, which is governed by the above 3 profiles.

Also, if you are getting error in doing a page submit on pop up page confirm:
FND: Framework Compatibility Mode profile option should have value greater than or equal to "11.5.10".

Will be back with a artcle on databound values in the next post.

Monday, July 2, 2007

JavaScript In OA Framework

“To be or not to be…… is the question…..!” The famous soliloquy by William Shakespeare in Hamlet…. a similar scenario we have in OA Framework for JavaScript!
The exact lines of Developers’ guide for use JavaScript are as follows:
“UIX and the OA Framework are rapidly adding new features to provide a more interactive user experience
(partial page rendering, automatic table totaling, and so on). You are certainly encouraged to leverage these
features as they are released, however, you should not attempt to implement them yourself before they're
In short, JavaScript is prohibited without explicit permission from the OA Framework team. Furthermore, you
must have confirmation from the corporate UI design team that a Javascript implementation is essential for
your product.”

But this is really funny, if you are developing some web pages for your application, so expect all web features in the framework, and if they are missing definitely you have to use JavaScript to implement them, you cannot wait , for the framework team to implement them.

So, if you are developing some custom pages for your application, you can go ahead with JavaScript. I will advice to refrain from JavaScript if your extending standard OA pages, because, Oracle may not support it, in that case.
In case of custom pages, anyways oracle will not support customization, so you dam care for support.

Another interesting thing in OA Framework is that you will methods for JavaScript usage method in almost every UIX beans; in fact, framework team must be using them in development of various features in OA Framework.

What is JavaScript?

JavaScript is the Netscape-developed object scripting language used in millions of web pages and server applications worldwide. Netscape's JavaScript is a superset of the ECMA-262 Edition 3 (ECMAScript) standard scripting language, with only mild differences from the published standard.

Contrary to popular misconception, JavaScript is not "Interpretive Java". In a nutshell, JavaScript is a dynamic scripting language supporting prototype based object construction.

For those who are interested and new to concept of javascript, should explore this link

I will like to stress on one point before starting the various scenarios where JavaScript can be used, is that JavaScript is a browser specific script and may behave different in different browsers, so you must test your application code on various browsers like internet explorer, mozilla firefox, Netscape etc.

Scenarios in OA Framework, where you are bound to use javascript.
Various Pop Up Window scenarios
Closing a Window
Timely refresh of a OA Page
Client side events on different beans

Various Pop Up Window scenarios:
This is the most common scenario in OA Framework, as framework does not provide anything for pop windows, except standard LOVs, which may or may not suit your requirement.I think I have replied this thread several times on OA Framework forum, in fact, thought of writing this article, after I replied the thread so many times J.
Lets, take the most common scenario:
You need to fire a custom page as a pop up window , do some processing(say add some new values in one of the VO) and on returning back to base page, reflect the changes on base page(say the base page VO should reflect the changes done on pop up page).
Follow these steps for the solution:
1. To open a custom page..... as a modal window.... u can have a link or button(button of type button and not submit button) on base page with destination url property as following javascript:

javascript:var a ='OA.jsp?page=/XXX/oracle/apps/xxx/......&retainAM=Y', 'a','height=500,width=900,status=yes,toolbar=no,menubar=no,location=no'); a.focus();

Meaning of each parameter in this javascript:
· RetainAM:If this is equal to “Y”, AM will be retained when child window is opened.
· Height: Height of the pop up or child window.
· Width: Width of pop up or child window
· Status: String, Specifies a priority or transient message in the window's status bar (This property is not readable.).
· Toolbar: Object, Represents the browser window's tool bar.
· Menubar: Object, Represents the browser window's
menu bar.
· Location:Location of window on the sceen
· Resizable: if resizable=yes, is included in above api, then child window size can be maximized.By default the child window size is fixed user cannot, increase or decrease the size.
· Scrollbar: if scrollbar=yes, is included in above api, scrollbars will appear in the child window.

Please Note : While trying to run a pop up page you get errors like:
"You are trying to access a page which is no longer active....."

The root cause of this error are the profile options:
3)Framework Validation Level- None
They should be none in order to avoid the error.The root cause of the error is MAC key url validation, which is governed by the above 3 profiles.There can be scenarios when your DBA/Admin would not allow you to set these profiles at site level in order to maintain security of Apps from cross site scripting attacks.

In such scenarios, or I will say in all scenarios, its better to use a utility class in OAF called OABoundValueEmbedURL which secures your js function by bypassing the MAC key validation.You can use following code to attach js url programatically to a button/image bean :
OAImageBean img = (OAImageBean) webBean.findChildRecursive("");
//url of the page
String url ="/XXX/oracle/apps/xxx/......"
//retain am parameter
string amMode ="true";
//additional parameters i want to pass to to pop up page
String params="order_id={@workorder_id}";

//Computing page_url
String page =
url + "&retainAM=" + amMode + "&fndOAJSPinEmbeddedMode=y" + "&" +
//Computing final url
String destURL =
"?" + OAWebBeanConstants.JRAD_PAGE_URL_CONSTANT + "=" + page;

//Securing js function from MAC key validation
OABoundValueEmbedURL jsBound =
new OABoundValueEmbedURL(img, "openWindow(self, '", destURL,
"' , 'longTipWin', {width:" + width +
", height:" + height +
"}, true); return false;");

//Attaching bound value embed url to on click of image.
img.setAttributeValue(OAWebBeanConstants.ON_CLICK_ATTR, jsBound);

There are 3 important points to understand here :
1) This code has to be written in process request and not process form request, because as per OA Developer standards bean properties can only be set in process request and not process form request.

2)The additional parameters you want to pass can be passed at runtime, this is especially in case of tables/hgrids where lets say a particular column has link and each row has different parameter values to pass, you can send VO attributes as parameters like @Vo_attr_name.This will make your pop window link to be dynamic as each row js url will have different parameter values depending upron row of VO.

3)If you see the final url computed here I have used an additional parameter like
fndOAJSPinEmbeddedMode=y, this is especially in case of pages which use jtfchrome.jsp where in the JTF/JTT and OAF flows are integrated.This parameter will take care that the JS URL OF LOV page, should not be changed to jtfcrmchrome.jsp from OA.jsp which results in error of lov page.

In one of the 2 approaches you would be able to open the pop up page.

2. Now, you can do your pop up page processing by the pop page controller.
3. Now your child window should have two buttons, one for select(submit button) and another for close(button).On press of select button you will have all vaues in your trasient VO, and you can also show user a message like, " values created successfully....".The second button will be of type "button", whose destination url property will be

javascript:opener.submitForm('DefaultFormName',0,{XXX:' abc'});window.close();
(In this api you can pass n number of attributes, the second parameter will be correspondingly n-1, e.g you have to pass three attr, then second parameter of api will be 2
javascript:opener.submitForm('DefaultFormName',2,{'XXX':' abc','XXX1':'efg','XXX2':'ijk'});window.close(); )
Now in the process request of the base page CO, u can get parametre 'XXX' from the
{///LOGIC to initialize or not to intialize some components of base page}

Please Note :
1)From Apps R12.1, the framework is also validating parameter security, so getting js parameter values by pageContext.getParameter("XXX"), can throw security exception error
FND_FORM_POST_SECURITY_FAILED. To bypass this parameter security validation , you need to take js parameter values directly from http request using following code:

2)Also, if you are getting error in doing a page submit on pop up page confirm:
FND: Framework Compatibility Mode profile option should have value greater than or equal to "11.5.10".

Closing a Window
To open a custom page..... as a modal window.... u can have a link or button(button of type button and not submit button) on base page with destination url property as following javascript:window.close();

Timely refresh of a OA Page
Lets, consider a scenario when you want a page refresh after every 10 seconds.Here is javascript for it:javascript:setTimeout(window.location.reload(),10000);where 10,000-->10 sec(10,000 milli sec)

What you need to do is attach with onLoad method of OABody bean, so that when the page is rendered, this javascript is triggered.You can code like this:

OABodyBean bodyBean = (OABodyBean) oapagecontext.getRootWebBean();

String javaS = “javascript:setTimeout('window.location.reload()',10000)”;

Client side events on different beans:
Above I have given a few of million possible situations of javascript in OA Framework.In almost all ui beans u will find methods like setOnLoad *, setOnClick* etc., where you can attach a javascript function to the bean.
Will be back with more examples of javascript in OA Framework, soon!

Friday, June 1, 2007

Extensions in OA framework

1. Introduction
2. What is Oracle Applications Framework?
3. Setting up your Environment
4. How to confirm whether actually go for extension or not?
5. BC4J Extensions
6. Deploying your extensions on the application server

1. Introduction

This purpose of this document is to help its readers understand the extensions in Oracle Applications Framework (OAF) and provide a general guideline as to how to create and deploy extensions in OAF.

This document is created based on experiences while implementing iProcurement module in R11.5.10 for one of US Banking Client.

2. What is Oracle Applications Framework?

Oracle Applications Framework is a development and deployment platform for Oracle Self-Service Web Applications. It is a
100% Java & XML, middle-tier application framework and services for the rapid development & deployment of HTML based applications. This framework is based on MVC archietecture (Model-View-Controller).

Correspondingly, MVC in OA Framework can be categorized as:
View – Common UIX-based HTML components used throughout Applications.
Controller – OA Controller responds to user actions, directs application flow.
Model – Business logic encapsulated in Business Components for Java view objects and entity objects.
OA Framework provides with two main features:


1. Personalization

Personalization means declaratively tailoring the user interface (UI) look-and-feel, layout or visibility of page content to suite a business need or a user preference.
Note that with personalization we are not adding a custom functional flow to the system. It plainly refers to modifying the User Interface with the capabilities provided by the framework, for a better user experience.

Examples of personalization include:

Changing the order of the column in a UI table
Making certain attributes as read-only
Hiding/Showing certain attributes to the user

2. Extensibility

Extensibility refers to the ability to programmatically extend an application's functionality. In other words, extending the n functionality of an application gives developer the ability of:

· Adding new content or business logic
· Extending/overriding existing business logic

The tool which we use for extending various applications in Oracle Applications 11i is JDeveloper.Oracle Self-Service Web Applications can be customized using extensibility feature as per business requirement, in following two ways:

· Customization by extensions: The Oracle Self-Service Web Applications can be customized as per business requirement by adding the custom code to the extended class of the base class. Oracle support this approach because in case of upgrading or applying any patch, if the base class file is replaced by a new class file, then the custom code will still be present in the extended class.

· Customization by modification: The Oracle Self-Service Web Applications can also be customized as per business requirement by adding the custom code to base class itself. However, Oracle does not support this , because in case of upgrading or applying any patch, if the base class file is replaced, then the custom code is removed and so, is the related functionality. That is why Oracle advises for customization by extensions.
With extension, custom functional flows can be created or existing business logic can be overridden to suit customer needs. Examples of extensions include:

· Extending View Objects in order to include an extra transient attribute.
· Extending Application Modules in order to add a new logic as per business requirement.

3. Setting up your Environment

In order to start with extensions we first have to setup appropriate environment for it, i.e. setup Jdeveloper environment. For setting JDeveloper environment, we follow following steps:

Download the latest version of JDeveloper ( which is patched for OA Framework from metalink.(We used 9.0.3 version of JDeveloper) and install it as per the instructions on metalink.

To start Jdeveloper go to JDev\jdevbin\jdev\bin and click
jdevw.exe (For convenience, we can create a shortcut of this icon on desktop.)

Put the dbc file extracted from server from <$FND_TOP>\secure\ and put it in appropriate folder i.e.
Here, refers to path till “jdev” directory in “jdevhome” directory of JDeveloper

Expand the Connections node in the JDeveloper System Navigator, and then expand the Database node. Right-click on the Database node, and select New Connection... to open the Connection Wizard. Follow the JDeveloper instructions to define a new database connection for the Oracle Applications database identified by the DBC file you selected above.

Please confirm that the dbc file does not contain any slashes in the line:
Originally the dbc file contains back slashes in this line it is extracted from Unix database server.

The various information needed to complete the connection wizard can be filled by opening the dbc file and then filling Host name, Port, Sid etc. If you get any errors while making the database connection, again cross check the database entries from dbc file and see that there are no back slashes in line
of the dbc file.

In order to set our module related environment in Jdeveloper,make a folder named “myclasses” under path \myclasses
Zip the application top from your UNIX application server specific to your module. Eg., since, we were working on IProcurement, so it was

Extract this under \myclasses. Please ensure that we have exactly same directory structure under myclasses folders as the zip file of application top has.

Under the directory structure C:\JDev\jdevhome\jdev there are two folders:
Ideally myclasses folder contain all the files of the application top related to that module, while in myprojects folder we should put only those packages and files are needed to be run, extended or customized. Also note, once a file in myprojects folder is compiled through jdev, a copy of its class file is automatically placed in myclasses.
But, there are a lot of dependencies between VOs and Eos, so, what we did in our implementation was that we unzipped the application top both in myclasses as well as myprojects folder.

In order to find which page to run, to start Oracle Applications from a specific responsibility, through local Jdev Apache server, login on Oracle Applications home page and click the responsibility specific to your module.
Like in our case it was I- procurement. Click on about the page link on this page in order to find relevant information.

NOTE:The about the page link is by default not available on every page. To make it available, two profile options need to set, to have the link ‘about the page’ and ‘Personalize this Page’ on each OA Page. In order to enable it, set these two profile options:

Personalize Self-service Defn (FND_CUSTOM_OA_DEFINTION) - set to “YES”
FND: Personalization Region Link Enabled - set to “YES”

In the above screenshot you can see the directory structure and exact page (red oval space) that opens up while clicking the I-procurement responsibility. In our case it was
Now, make the same directory structure under myprojects folder and copy, paste ShoppingHomePG.xml i.e.

Select File > New... to open the New... dialog (shown in the following screenshot). This dialog is also called the New Object Gallery.
Workspace Configured for Oracle Applications highlighted">
Choose General > Workspace Configured for Oracle Applications from the New... dialog, or highlight Workspaces in the Navigator and choose New OA Workspace... from the context menu. We'll be prompted to create an OA workspace. Verify that the default workspace directory name points to our own \myprojects directory, as shown in the following diagram. Modify the workspace file name as well (any name is okay for a workspace, such as Iproc.jws). Check the Add a New OA Project check box.

After you click OK, you will see the Oracle Applications Project Wizard.
In Step 1 of the wizard, verify that the default project directory name points to your own JDEV_USER_HOME\myprojects directory, as shown in the following diagram. Modify the project file name as well (any name is okay for a project, such as OAPjoject2.jpr). Set the default package name to
oracle.apps. Like in our case it was oracle.apps.icx
In Step 2 of the wizard we have to set the database connection and runtime connection. In Step3, Browse the correct dbc file here. Fill the runtime username and password .Fill the application short name specific to your module, like in our case it was “ICX” for iprocurement. (If you are unaware of application short name you can find it out from fnd_application_vl).To fill Responsibility key of the related responsibility (for iprocurement it is SELF_SERVICE_PURCHASING_5), you can login in oracle applications and then
System AdministratoràSecurity: ResponsibilityàDefine
Here query for the Responsibility name to get Responsibility key.

12. In order to add homepage of the responsibility(module), on which we are working to the current project, click the red circled button as shown in screenshot below:

We will browse the page which we found in step 4 in myprojects folder i.e.

13. Right click ShoppingHomePG.xml and select Run ShoppingHomePG.xml
14. We will be able to see the homepage of the specific responsibility.
This completes all steps needed to develop suitable environment for extensions in particular page under specific responsibility in Oracle Applications 11i.

4. How to confirm whether actually go for extension or not?
In order to decide whether to actually go about extending a webpage or not, we take an example, which we implemented in I-Procurement. The requirement was that in a standard requisition details page, it looks like the screenshot below

Item number was not available. We had a requirement where we have to introduce an extra column in the table: LinesTableRN named as Item number, which will reveal item number of the item. We added an extra column through personalization, to reveal to item number , but to check whether item number is available in standard view objects of the page ,we have to check its availability in the about this page of the page.
Click “about this page” link, to find out information about various View Objects, application modules and controller objects. We also get information about directory structure and the page from this link. So, we get following information:

Page : /oracle/apps/icx/por/reqmgmt/webui/ReqDetailsPG
Application Module : ReqMgmtAM
Available View Objects : (All view objects that are available in ReqMgmtAM can be used)
Controller object : ReqDetailsCO

In order to analyze whether we need to actually extension or we can solve just by going through personalization, we analyzed all the view objects definitions by clicking each view object through about this page link and search whether item number is available in any view object or not .We found that non of the available view objects had item number, so, we are now confirmed of the approach, extension
We introduced an extra column in the table: LinesTableRN named as Item number, which will reveal item number of the item. We added an extra column through personalization and now we will have to extend any View Object available on the page(we choose ReqDetailsVO) to add an extra an extra transient column called item number and get it populated by some business logic.

5. BC4J Extensions
The best way to start the extensions is to mount the whole package in Jdeveloper. To do it we should browse through the server folder of the package. Like in our case we browsed


This folder contains a file called server.xml

Sometimes, it happens that some server.xml files are missing in the package which stops us from mounting the package in jdeveloper. In such case raise an SR with Oracle to provide patch for all server.xml files of the module or package your working. We raised it for Iprocurement module, as some server.xml files were missing. As soon as you apply this patch all the server.xml are available.

Now, in order to complete our extensions follow these steps:
1. Create Your New BC4J Package.
In your OAProject2.jpr project, create a new, empty BC4J package named .oracle.apps.icx.por.reqmgmt.server. i.e.
2. Create Your New View Object (VO) and transient attribute
Create a new VO, named ReqDetailsVO, that extends ReqDetailsVO i.e.
Create the VO in the
Package. Add transient attribute Item Number to your extended VO.
Substitute new VO FtuReqDetailsVO for the existing VO ReqDetailsVO.For details of how to substitute one VO for another ,look at the steps listed in the
OAF Implementation Guide.
Similarly extend the controller class ReqDetailsCO to FtuReqDetailsCO and a new method in process request () like "ftuItemNumberDisplay". Invoke this method through application module.
Extend the application module ReqMgmtAM TO FtuReqMgmtAM, so that we extend the application module and write our custom logic inside the method "ftuItemNumberDisplay" in FtuReqMgmtAM, in order to retrieve value of Item Number
Run ShoppingHomePG and navigate to the present page in order to see the result of our personalization and extension. We see the following result

6. Deploying your extensions on the application server
In order to deploy your extensions follow these steps:
1. Compile your Java files in JDeveloper and zip up your Java classes to the
<$JAVA_TOP>// oracle/apps/
Hence in our case we, put the all the class files from C:\JDev\jdevhome\jdev\myprojects\ftu\oracle\apps\icx\por\reqmgmt\server
To <$JAVA_TOP> /ftu/oracle/apps/icx/por/reqmgmt/server in the application unix server.
Run the jpx import utility to import substitutions specified in the .jpx definition file to the MDS repository of application server. Hence, in our case we have OAProject2.jpx file which is used for extracting substitutions on application server. For details of how to jpx import utility, look at the steps listed in the OAF Implementation Guide.
Note: Each JDeveloper project has one .jpx file. If you have more than one JDeveloper project where you have created substitutions, you can import the substitutions specified in the respective .jpx file from each project, one after another. If you deploy substitutions for the same component more than once, the latest substitution will take precedence, replacing the existing substitution for that component.
3. Bounce the web server
4. Review your deployed extensions
At this point the deployment of your extension is complete, and you should be able to login to your application to verify that your changes have successfully taken effect.