JDBC Connections leakage and optimization in Oracle Apps R12.

Identifying issues with JDBC connections in Apps can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article will give you a better understanding of JDBC Pooling and where to start looking for issues. Hopefully this article should help system and technical architects in Apps to resolve connection leak problems.

Recently while working with Oracle MEA in world's first major implementation of CMRO module in Apps, we faced some critical performance issues, initially. One of the major performance issue was "JDBC connections leakage". Over a period the "INACTIVE" jdbc connections rise and finally they cross the maximum jdbc connections figure set in database and server goes down. The immediate solution was bouncing OC4J Core and HTTP containers of Oracle Apps application server 10g, but off-course this is something you can not do daily in a production environment.

Since our implementation layer has a strong layer of custom code and standard code , done by various teams and you don't have control on coding of individual new developers. I thought it is next to impossible to identify root cause and fix it ,but thanks to Oracle... in Apps there are some standard ways to fix this problem.

Ok to start with lets start with some basic definitions and facts to understand the problem , I am talking about here :


What is JDBC ?
The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases – SQL databases(Oracle,mysql etc) and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.

What is JDBC Connection ?
Java JDBC APIs uses connection (session) instance in which SQL statements are executed and results are returned within the context of a connection.

What is JDBC Connection pool?
If you have used a SQL or other similar tool to connect to a database and act on the data, you probably know that getting the connection and logging in is the part that takes the most time. An application can easily spend several seconds every time it needs to establish a connection.

In releases prior to JDBC 2.0 every database session requires a new connection and login even if the previous connection and login used the same table and user account. If you are using a JDBC release prior to 2.0 and want to improve performance, you can cache JDBC connections instead.

Cached connections are kept in a runtime object pool and can be used and reused as needed by the application. One way to implement the object pool is to make a simple hashtable of connection objects.

What is Connection leakage ?
An application(basically application server) is said to be leaking connection, if it acquires a connection and does not close it within specified time period. If this feature is enabled, the application server detects these potential connection leaks and dumps the leak tracing logs to server logs. Looking at the logs the user can figure out which application is leaking connection and fix the issues with application, if exists.

What is Connection locks ?
If your are doing multiple DML jdbc transactions in your code with autocommit flag as false on the connection object,the jdbc connection remains locked till the time commit is issued specifically.(By default in OAF/JTF the autocommit flag is false.)


How to identify that Apps instance has connection leakage problem ?

You can run the following sql statement occasionaly to monitor jdbc connections
behaviour :

select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id


This will list down number of jdbc connections in each module. For OAF, the module is shown as page AM and for JTF/JTT/JSP, the JDBC connections are listed in "JDBC Thin Client". You can keep of track of connections in each module mainly ("JDBC Thin Client"), if this is growing each day. You can also check the status of these connections in v$session if its "INACTIVE" and these connections are persisting for more than 24-48 hours, means these sum up to connection leakage.

For OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool). This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.So, generally this layer would not have problem, because developer does not have to release/close connection in his code, that is automatically taken care by framework depending upon AM instance pooling and Database Connection Pooling enabled on the instance.

AOL/J JDBC code is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.This layer covers all yours JSP/JTT/JTF layer and 99% of the cases is responsible for connections leaks.

How to track leaked and locked connections?
Once you see that your instance has a problem of growing jdbc connections over a period of time all with inactive status for more than 24-48 hours, you will now start to debug and find the exact leaked and locked connections.To gather JDBC Connection statics , do following steps :
1) We need to first identify number of OACORE processes running on apps application server, then on each OACORE process we will check number of leaked locked jdbc connections in Apps instance.To note the number of OACORE processes , we need to do following in Unix box of each application server node :

(i) Login to Apps Application server unix box using ftp tool like putty.
(ii) cd $ADMIN_SCRIPTS_HOME
(iii) adoacorectl.sh status

The output will look like :

Processes in Instance:
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:oafm | 21894 | Alive
OC4JGroup:default_group | OC4J:forms | 21861 | Alive
OC4JGroup:default_group | OC4J:oacore | 21775 | Alive
OC4JGroup:default_group | OC4J:oacore | 21776 | Alive
HTTP_Server | HTTP_Server | 21699 | Alive

From this we can analyse that there are two OCCore processes running with process id
21775 and 21776 .

Please note in case of multi-node enviorment i.e. Application server with multiple nodes,you will have to run this command individually on each node to get the OACORE process id(s) for that node.

2) Enable FND Diagnostics profile at your user level.

3) Login into Apps instance, on home page click "About This Page" link.

4)Go to subtab "Java System Properties" and note "CLIENT_PROCESSID" from the
table, this will be one of what you got in step 1.

5) Now in the url type : http://< host >:< port >/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp

6) This will display you the list of leaked and locked connections for the process id you got in step 3. For leaked connections if you will click the hyperlink, you will also get detailed stack for each leaked connection , which can furthur help you
in identifying the code layer where connection leak is happening.


You need to repeat step (3) and (4) again and again each time logging out and closing the browser to track all the CLIENT_PROCESSID locked and leaked connections.


How to get the root cause/code that is causing this connection leak ?
Actually AoljDbcPoolStatus.jsp is the key to resolve this problem and leaked connection stack will help us to figure out our problem.We can make following conclusions from the stack :

1) The first obvious thing you will want to look at is that whether or not your custom code is causing this problem. Thats easy just search the stack with ur custom top name eg- all your extensions lie in lets say xxabc folder under $JAVA_TOP, then you can search xxabc in leak connection stack, that will give you pointers of class and code where this connection is instantiated and later not released/closed.

2) For custom and standard jsp(s), search the stack with "_" as all jsp classes in apps are compiled as "_" pre-fixed to the jsp name. So this can give you idea where and which jsp are leaking connections. In my experience, this is the layer where most developers make mistakes.

3) Lastly if not custom class then standard classes are doing this (This is a rare case), and for this you anyways just have to raise an SR with Oracle to fix this. To identify search stacks and see its coming from which module i.e., the stack will we like oracle.apps.per.xxx.yyy or oracle.apps.icx.yyy.zzzz , this simple points you that per=> HRMS or ICX=> I-procurement module standard class is causing this problem. Its always good to decompile and look at the code of these classes because often developers do invaisive customization to Oracle code, directly changing the seeded Oracle file to take easy way to their customizations , without realising that this can later cause bigger problems.

Hopefully these analysis will help you to fix root cause of connection leakage problem.

Adding programmatic fire Action to UIX beans which dont support it

Recently, a friend had a requirement of a customization in a seeded page where he needs to put some validation on a table column which was actually OAMessageDateFieldBean.

This is quite simple and generic requirement , when he called me ...I told him that since , this has to be done in seeded Oracle page and fire action elements can't be added by personalization,he just needs to extend the CO and attach fire action programatically to the OAMessageDateFieldBean in process request and then can handle his logic of validation in process form request.But the tricky part came when he told me his code is not compiling since setFireActionForSubmit API is absent in OAMessageDateFieldBean class.

I just wondered how is it possible since this bean allows me to configure fireaction decalaratively, then there should be an API of doing it programatically too in the bean. Then I remebered with each UIX bean Oracle also gives a helper class, which can usually be instantiated by using getHelper(), on the bean instance.

Whenever you are in such situations , its worth while to look into bean helper classes. Ok.. so here is the code how you can configure firection on OAMessageDateFieldBean programatically :

//In process request.

//getting table bean instance
OATableBean t=(OATableBean)webBean.findChildRecursive("< table bean id >");

//getting OAMessageDateFieldBean inside
//table bean
OAMessageDateFieldBean expDate = (OAMessageDateFieldBean)t.findChildRecursive("< OAMessageDateFieldBean id >");

//hard-parameters for fire action
//it will help us to identify if action
//has occured or not
Hashtable params = new Hashtable (1);
params.put ("XX_ACTION","XXX");

//bound value parameters for fire action
// basically the primary key attribute of
//VO to get the row from which action has occured
//eg, lets say the VO attribute is Visit id AND
//that is primary key
Hashtable paramsWithBinds = new Hashtable(1);
paramsWithBinds.put ("XX_PRIMARY",new OADataBoundValueFireActionURL((OAWebBeanData) expDate, "{$VisitId}"));

//Most important---
//taking helper class instance
// there we get the API to attach fire action.
expDate.getHelper().setFireActionForSubmit(expDate,"delete",params, paramsWithBinds,false,false);

//In Process form request

if(pageContext.getParameter("XX_ACTION")!=null)
{
//by this primary key we can retrieve the VO row at which
// action occured and utilise the other column data
// for validations.
String primary_key=pageContext.getParameter("XX_PRIMARY");

.......

//finally releasing the parameters from
//current pagecontext requesr
pagecontext.removeParameter("XX_ACTION");
pagecontext.removeParameter("XX_PRIMARY");
}



Alternative : There is a alternate way to attach PPR/Fireaction to any bean.Use static methods in class OAWebBeanUtils, like
OAWebBeanUtils.getFirePartialActionForSubmit()
OAWebBeanUtils.getFireActionForSubmit()
and then use

// finally set it on your bean
bean.setAttributeValue
(PRIMARY_CLIENT_ACTION_ATTR, fireAction);

Happy coding....!

Concept of Nested AM.

Hi All,
Writing a theoretical article after a long time. What inspired me to write.... hmm... lot of questioning developers :) . Ok, my target with this article is to make an OA Framework developer understand what is nested AM? and how can u find a nested AM instance from master/root AM instance? What we should know about it while developing extensions or new pages.

What is a Nested AM?
--------------------------------------
An application module may be a root application module or a nested application module. A root application module is not contained in another applicatin module. It provides transaction context for all objects contained in it. It may optionally contain nested application modules. A root application module is created through JNDI calls.

A nested application module is contained in another application module. The containing application module is referred to as the parent application module. If one traverses this containership ancestry, one will eventually find the root application module (which does not have a parent application module). A nested application module uses the transaction context provided by the root application module. Thus, data modifications performed in application modules parented by one root application module will commit or rollback together.

Transaction
-----------------------------
Associated with the root application module is the Transaction object, which provides this transaction context. From any (root or nested) application module, the user can retrieve the transaction object through a call to getOADBTransaction(). In reality, getOADBTransaction() first locates the root application module and then returns the transaction object from it.

The transaction object manages connection to database and entity caches. Thus, changes made through one view object are visible to other view objects as long as these view objects all parented by the one root application module. In contrast, if two view objects are parented by two separate root application modules, then changes made through the view object will not be seen by the second view object until the changes are committed to database through the first root application module and the second VO executes query (to retrieve the most up-to-date data from database).

Creating Application Module
--------------------------------------------------------------------
A root application module is created by:

Finding the application module home through JNDI.
Calling create() on the application module home.
Here is a sample code to create a root application module:

java.util.Hashtable env = new java.util.Hashtable();

// Add environment entries into env...

javax.naming.Context ic = new InitialContext(env);

// 'defName' is the JNDI name for the application module
// definition from which the root application module is to
// be created
String defName = ...;

oracle.jbo.ApplicationModuleHome home = ic.lookup(defName);
oracle.jbo.ApplicationModule am = home.create();


One creates a nested application module by calling createApplicationModule on the parent Application module.

How nested AM concept works in OAF :
---------------------------------------------------------------------------
Now to associate a nested application module with a region,
specify either of the following properties:
1) AM Instance -- The application module instance name as specified in the application module's data
model. For example, PoSummaryAM1.
2) AM Definition -- The fully qualified name of the application module instance. For example,
oracle.apps.fnd.framework.toolbox.tutorial.server.PoSummaryAM.

If you specify the AM Instance property, OA Framework attempts to find and return the AM instance with the given name. It searches the application module associated with the parent web bean.
If you specify the AM Definition property, OA Framework assigns a generated AM instance name to the nested region. This name is comprised of the following values:
a) Region code of the associated nested region (if the region was originally created in AK)
b) Nested region application ID
c) Nested region ID
d) AM definition name.

OA Framework checks to see if the nested AM with the system-generated name has been already created under the application module of the parent web bean. If the instance is not found, OA Framework creates and uses a nested AM with the system-generated name. Otherwise, it reuses the pre-existing AM with the system generated
name.

When specifying both the AM Instance and AM Definition properties
if a matching AM instance is found, OA Framework compares its definition with the AM Definition property value. If there is a mismatch, OA Framework throws an exception.
If a matching AM instance is not found, OA Framework creates and returns a nested application module instance with the given AM instance name and the definition.

Code for finding nested AM instance in root AM :
-----------------------------------------------------------------------------------
Hence, whenever you are trying to find an nested AM instance linked to a OA region under pagelayout region, please check the am defination and am instance property of that region, because only then you would be able to have a idea of name of nested AM instance. It can be system generated AM instance or name given by developer for AM instance.So, you need to confirm this from the three cases mentioned in the previous article.For debugging purposes, you can get the list if Application modules in root AM and print there names ,too :

String[] nestedAMNames = parentAM.getApplicationModuleNames();

// If you want to retrieve all currently loaded nested Application Modules
ApplicationModule[] nestedAMs = new ApplicationModule[nestedAMNames.length];

for (int i = 0; i < nestedAMNames.length; i++)
{
nestedAM[i] = parentAM.findApplicationModule(nestedAMNames[i]);
}



Otherwise for finding nested AM instance by :
ApplicationModule nestedAM = parentAM.findApplicationModule("MyNestedAM");

This concept is really helpful while doing extensions in OAF, as you may have to find a nested AM in a CO of particular OAF region.You can always find root AM of the page in any controller using pagecontext.getRootApplicationModule().

I hope after reading this article nested AM conecept becomes crystal clear in your mind :), if not raise queries, and I will try to answer you.

Attaching AutoSubmit Property to a OA page.

Hi All,
I recently received couple of mails where people more or less have the requirement like :
1) Data of the page gets refresh every 10 sec automatically.
2) Data of the page should be autosaved every 10 seconds etc.

This is very similar to autosave feature of microsoft word or popular email websites like yahoo and google. A small javascript function can do this for you.Here are the steps how, you can achieve this functionality in an OAF page :

/**
* @param pageContext -current page context in CO
* @param evt_name - javascript event will be registered with this name.
* @param time_in_milli_sec - time in milli sec after which page will refresh
* e.g if you wanna submit page every 10 sec , enter 10000
* This api should be used in process request and not process form request.
*/
public void attachAutoSubmitPropertyToPage(OAPageContext pageContext,
String evt_name,
String time_in_milli_sec)
{
OABodyBean bodyBean = (OABodyBean) pageContext.getRootWebBean();
String javaS =
"javascript:setTimeout(\"submitForm('DefaultFormName',0,{'" +
evt_name + "':'Y'});\"," + time_in_milli_sec + ");";
bodyBean.setOnLoad(javaS);
}

/**
* @param pageContext -current page context in CO
* @param event_name - pass javascript event name that you registered
* in process request.
* @return return true/false accordinging whether the event has occured or not.
* This api call should be there in process form request.
*/
public boolean isAutoSubmitEvent(OAPageContext pageContext,
String event_name)
{
boolean b = false;
String s =
pageContext.getRenderingContext().getServletRequest().getParameter(event_name);
if (!((s == null) || ("".equals(s.trim()))))
{
if("Y".equals(s))
{
b = true;
pageContext.removeParameter(event_name);
}
}
return b;
}

Then , in process request add the api call

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
// see javadocs for parameter description
//10000 for 10 sec as we are paaing in millsec
attachAutoSubmitPropertyToPage(pageContext,"XX_EVT","10000");

.
.
.
}



In process form request... you can catch this event in every 10 sec:
public void processFormRequest(OAPageContext pageContext,
OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
.
.

if(isAutoSubmitEvent(pageContext,"XX_EVT"))
{
// YOUR LOGIC TO SAVE THE PAGE CONTENTS.
//or to refresh the data... etc
}

I hope this is interesting and will help developers of such kind of requirement.
Happy Coding..!

Upload file to Application server using OAFileUploadBean instead of Database

Sometimes, there can be requirement , where you need to upload a file on unix server, instead of database.You can use OAMessageFileUploadBean for this. Add the upload bean in your OAF UIX page. Add an additional submit button bean which will be responsible for file Upload.
On click on this button in process form request call the following method with parameters as described in CO. So, copy this method in CO and then call it in process form request on this submit button click like

uploadFileToServer(pageContext, "item3",
"/xx/app/sss/x");


Here is the method for upload:
/**
* @param pageContext is current pagecontext in CO
* @param fileuploadBeanId is item id of file upload bean
* @param server_dir_path is abslute path on unix server
* where file needs to be written.eg "/xx/xxx/xxx"
*/
public void uploadFileToServer(OAPageContext pageContext,
String fileuploadBeanId,
String server_dir_path)
{
DataObject fileUploadData =
(DataObject) pageContext.getNamedDataObject(fileuploadBeanId);
if(fileUploadData!=null)
{
String uFileName =
(String) fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
String contentType =
(String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

File file = new File(server_dir_path, uFileName);
FileOutputStream output = null;
InputStream input = null;
try
{
output = new FileOutputStream(file);
BlobDomain uploadedByteStream =
(BlobDomain) fileUploadData.selectValue(null, uFileName);
input = uploadedByteStream.getInputStream();
for (int bytes = 0; bytes < uploadedByteStream.getLength(); bytes++)
{
output.write(input.read());
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (input != null)
{
input.close();
}
if (output != null)
{
output.close();
output.flush();
}
}
catch (Exception ez)
{
ez.printStackTrace();
}
}

}
}

Please understand when you run a page from jdeveloper, you are running page on local oc4j server of jdeveloper and not the actual application server of Oracle Apps, hence if this code is run from jdeveloper then you need to give path of your local desktop folder and file will be uploaded in it and not the actual application server.
To test this code actually you should deploy your page onto Oracle apps application server and access the page from a responsibility by registering it as a function , because then it will use Oracle Apps Application Unix server and upload file there.
Happy coding ...!

Giving download option to user for a file at a particular location on Application Server

Hi All,
This is a very generic scenario where a user wants to download a particular file from application server. eg- We are storing some report output or some pdf documents in particular location in unix application server. Now in a OAF page on press of a button/link I wanna give user download option for this file.

For this requirement you can use following method, from process form request, u need to pass pagecontext and other parameters as described in method.

//on button click in process form request
//call this method
downloadFileFromServer(
pageContext,--pagecontext
"/xx/xxx/ssss/120devg.pdf",--full file path with file name and ext
"120devg.pdf" --file name with extension
);

Make sure that the folder/files you are giving user option to download through this method, have 777 rights in UNIX server.

Copy these two methods in your CO for the above API to work:
/**
* @param pageContext the current OA page context
* @param file_name_with_path - this is fully qualified file name with its path on unix application
* server. eg "/xxcrp/xxapplcrp/mukul/abc.pdf"
* @param file_name_with_ext - this is file name with extension, you wanna display user
* for download. eg- i wanna display the abc.pdf file download with name five_point_someone.pdf
* then I can pass this as "five_point_someone.pdf"
*/
public void downloadFileFromServer(OAPageContext pageContext,
String file_name_with_path,
String file_name_with_ext)
{
HttpServletResponse response =
(HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
if (((file_name_with_path == null) ||
("".equals(file_name_with_path))))
{
throw new OAException("File path is invalid.");
}

File fileToDownload = null;
try
{
fileToDownload = new File(file_name_with_path);
}
catch (Exception e)
{
throw new OAException("Invalid File Path or file does not exist.");
}

if (!fileToDownload.exists())
{
throw new OAException("File does not exist.");
}

if (!fileToDownload.canRead())
{
throw new OAException("Not Able to read the file.");
}

String fileType = getMimeType(file_name_with_ext);
response.setContentType(fileType);
response.setContentLength((int)fileToDownload.length());
response.setHeader("Content-Disposition",
"attachment; filename=\"" + file_name_with_ext +
"\"");

InputStream in = null;
ServletOutputStream outs = null;

try
{
outs = response.getOutputStream();
in = new BufferedInputStream(new FileInputStream(fileToDownload));
int ch;
while ((ch = in.read()) != -1)
{
outs.write(ch);
}

}
catch (IOException e)
{
// TODO
e.printStackTrace();
}
finally
{
try
{
outs.flush();
outs.close();
if (in != null)
{
in.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

/**
* @param s
* @return file mime type from its name
*/
public String getMimeType(String s)
{
int i = s.lastIndexOf(".");
if (i > 0 && i < s.length() - 1)
{
String s1 = s.substring(i + 1);
if (s1.equalsIgnoreCase("amr"))
{
return "audio/amr";
}
if (s1.equalsIgnoreCase("mid"))
{
return "audio/midi";
}
if (s1.equalsIgnoreCase("mmf"))
{
return "application/vnd.smaf";
}
if (s1.equalsIgnoreCase("qcp"))
{
return "audio/vnd.qcelp";
}
if (s1.equalsIgnoreCase("hqx"))
{
return "application/mac-binhex40";
}
if (s1.equalsIgnoreCase("cpt"))
{
return "application/mac-compactpro";
}
if (s1.equalsIgnoreCase("doc"))
{
return "application/msword";
}
if (s1.equalsIgnoreCase("jsp"))
{
return "application/jsp";
}
if (s1.equalsIgnoreCase("oda"))
{
return "application/oda";
}
if (s1.equalsIgnoreCase("pdf"))
{
return "application/pdf";
}
if (s1.equalsIgnoreCase("ai"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("eps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ps"))
{
return "application/postscript";
}
if (s1.equalsIgnoreCase("ppt"))
{
return "application/powerpoint";
}
if (s1.equalsIgnoreCase("rtf"))
{
return "application/rtf";
}
if (s1.equalsIgnoreCase("bcpio"))
{
return "application/x-bcpio";
}
if (s1.equalsIgnoreCase("vcd"))
{
return "application/x-cdlink";
}
if (s1.equalsIgnoreCase("Z"))
{
return "application/x-compress";
}
if (s1.equalsIgnoreCase("cpio"))
{
return "application/x-cpio";
}
if (s1.equalsIgnoreCase("csh"))
{
return "application/x-csh";
}
if (s1.equalsIgnoreCase("dcr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dir"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dxr"))
{
return "application/x-director";
}
if (s1.equalsIgnoreCase("dvi"))
{
return "application/x-dvi";
}
if (s1.equalsIgnoreCase("gtar"))
{
return "application/x-gtar";
}
if (s1.equalsIgnoreCase("gz"))
{
return "application/x-gzip";
}
if (s1.equalsIgnoreCase("hdf"))
{
return "application/x-hdf";
}
if (s1.equalsIgnoreCase("cgi"))
{
return "application/x-httpd-cgi";
}
if (s1.equalsIgnoreCase("jnlp"))
{
return "application/x-java-jnlp-file";
}
if (s1.equalsIgnoreCase("skp"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skd"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skt"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("skm"))
{
return "application/x-koan";
}
if (s1.equalsIgnoreCase("latex"))
{
return "application/x-latex";
}
if (s1.equalsIgnoreCase("mif"))
{
return "application/x-mif";
}
if (s1.equalsIgnoreCase("nc"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("cdf"))
{
return "application/x-netcdf";
}
if (s1.equalsIgnoreCase("sh"))
{
return "application/x-sh";
}
if (s1.equalsIgnoreCase("shar"))
{
return "application/x-shar";
}
if (s1.equalsIgnoreCase("sit"))
{
return "application/x-stuffit";
}
if (s1.equalsIgnoreCase("sv4cpio"))
{
return "application/x-sv4cpio";
}
if (s1.equalsIgnoreCase("sv4crc"))
{
return "application/x-sv4crc";
}
if (s1.equalsIgnoreCase("tar"))
{
return "application/x-tar";
}
if (s1.equalsIgnoreCase("tcl"))
{
return "application/x-tcl";
}
if (s1.equalsIgnoreCase("tex"))
{
return "application/x-tex";
}
if (s1.equalsIgnoreCase("textinfo"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("texi"))
{
return "application/x-texinfo";
}
if (s1.equalsIgnoreCase("t"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("tr"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("roff"))
{
return "application/x-troff";
}
if (s1.equalsIgnoreCase("man"))
{
return "application/x-troff-man";
}
if (s1.equalsIgnoreCase("me"))
{
return "application/x-troff-me";
}
if (s1.equalsIgnoreCase("ms"))
{
return "application/x-troff-ms";
}
if (s1.equalsIgnoreCase("ustar"))
{
return "application/x-ustar";
}
if (s1.equalsIgnoreCase("src"))
{
return "application/x-wais-source";
}
if (s1.equalsIgnoreCase("xml"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("ent"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("cat"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("sty"))
{
return "text/xml";
}
if (s1.equalsIgnoreCase("dtd"))
{
return "text/dtd";
}
if (s1.equalsIgnoreCase("xsl"))
{
return "text/xsl";
}
if (s1.equalsIgnoreCase("zip"))
{
return "application/zip";
}
if (s1.equalsIgnoreCase("au"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("snd"))
{
return "audio/basic";
}
if (s1.equalsIgnoreCase("mpga"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp2"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("mp3"))
{
return "audio/mpeg";
}
if (s1.equalsIgnoreCase("aif"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aiff"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("aifc"))
{
return "audio/x-aiff";
}
if (s1.equalsIgnoreCase("ram"))
{
return "audio/x-pn-realaudio";
}
if (s1.equalsIgnoreCase("rpm"))
{
return "audio/x-pn-realaudio-plugin";
}
if (s1.equalsIgnoreCase("ra"))
{
return "audio/x-realaudio";
}
if (s1.equalsIgnoreCase("wav"))
{
return "audio/x-wav";
}
if (s1.equalsIgnoreCase("pdb"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("xyz"))
{
return "chemical/x-pdb";
}
if (s1.equalsIgnoreCase("gif"))
{
return "image/gif";
}
if (s1.equalsIgnoreCase("ief"))
{
return "image/ief";
}
if (s1.equalsIgnoreCase("jpeg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpg"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("jpe"))
{
return "image/jpeg";
}
if (s1.equalsIgnoreCase("png"))
{
return "image/png";
}
if (s1.equalsIgnoreCase("tiff"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("tif"))
{
return "image/tiff";
}
if (s1.equalsIgnoreCase("ras"))
{
return "image/x-cmu-raster";
}
if (s1.equalsIgnoreCase("pnm"))
{
return "image/x-portable-anymap";
}
if (s1.equalsIgnoreCase("pbm"))
{
return "image/x-portable-bitmap";
}
if (s1.equalsIgnoreCase("pgm"))
{
return "image/x-portable-graymap";
}
if (s1.equalsIgnoreCase("ppm"))
{
return "image/x-portable-pixmap";
}
if (s1.equalsIgnoreCase("rgb"))
{
return "image/x-rgb";
}
if (s1.equalsIgnoreCase("xbm"))
{
return "image/x-xbitmap";
}
if (s1.equalsIgnoreCase("xpm"))
{
return "image/x-xpixmap";
}
if (s1.equalsIgnoreCase("xwd"))
{
return "image/x-xwindowdump";
}
if (s1.equalsIgnoreCase("html"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("htm"))
{
return "text/html";
}
if (s1.equalsIgnoreCase("txt"))
{
return "text/plain";
}
if (s1.equalsIgnoreCase("rtx"))
{
return "text/richtext";
}
if (s1.equalsIgnoreCase("tsv"))
{
return "text/tab-separated-values";
}
if (s1.equalsIgnoreCase("etx"))
{
return "text/x-setext";
}
if (s1.equalsIgnoreCase("sgml"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("sgm"))
{
return "text/x-sgml";
}
if (s1.equalsIgnoreCase("mpeg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpg"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("mpe"))
{
return "video/mpeg";
}
if (s1.equalsIgnoreCase("qt"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("mov"))
{
return "video/quicktime";
}
if (s1.equalsIgnoreCase("avi"))
{
return "video/x-msvideo";
}
if (s1.equalsIgnoreCase("movie"))
{
return "video/x-sgi-movie";
}
if (s1.equalsIgnoreCase("ice"))
{
return "x-conference/x-cooltalk";
}
if (s1.equalsIgnoreCase("wrl"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("vrml"))
{
return "x-world/x-vrml";
}
if (s1.equalsIgnoreCase("wml"))
{
return "text/vnd.wap.wml";
}
if (s1.equalsIgnoreCase("wmlc"))
{
return "application/vnd.wap.wmlc";
}
if (s1.equalsIgnoreCase("wmls"))
{
return "text/vnd.wap.wmlscript";
}
if (s1.equalsIgnoreCase("wmlsc"))
{
return "application/vnd.wap.wmlscriptc";
}
if (s1.equalsIgnoreCase("wbmp"))
{
return "image/vnd.wap.wbmp";
}
if (s1.equalsIgnoreCase("css"))
{
return "text/css";
}
if (s1.equalsIgnoreCase("jad"))
{
return "text/vnd.sun.j2me.app-descriptor";
}
if (s1.equalsIgnoreCase("jar"))
{
return "application/java-archive";
}
if (s1.equalsIgnoreCase("3gp"))
{
return "video/3gp";
}
if (s1.equalsIgnoreCase("3g2"))
{
return "video/3gpp2";
}
if (s1.equalsIgnoreCase("mp4"))
{
return "video/3gpp";
}
}
return "application/octet-stream";
}

Happy coding...! I hope this helps! :)

Implementing Export Button Functionality Programatically

Hi All,
There is often a requirement to export a VO data or a UI table data into a CSV file. OA Framework provides export button bean for the this functionality, which works fine in almost 99% of the cases. But sometimes, you might face a scenario when ur table u have complex UIs like switcher/hide show columns etc, where the standard export functionality doesn't work or you want some columns not to come in export, or you wanna change some data on export.

In such cases, you can implement the export functionality programatically by yourself.Copy paste the following method in the page CO and pass appropriate parameters to get exported data.You can change this method, as per your requirement, to get data/format data or change data.

By default this method will bring all columns of fetch rows of VO instance,although you can use param hidden_attrib_list to pass attributes which you don't want to be included in csv file.You can call this method in the submit button event that you have made in process form request like :

//see api parameter details in the method below.
//array of Vo attr names which need not be written in csv file
String ss[]={xID,xName};
downloadCsvFile(pageContext, "XxAdatVisSearchVO",null, "MAX",ss);

/**
* @param pageContext
* @param view_inst_name is the view object instance name like VO1 etc in root AM.
* Make sure the VO instance name you have passed should be same as in Root AM.
* @param file_name_without_ext - pass for eg for abc.csv , u should pass "abc".If no
* name is passed then by default it will pick "Export.csv".
* @param max_size -pass "MAX", if u want all rows, pass null to get fetch row count
* else pass integer number like 10,20 etc , the number of rows you want to fetch.
* @param hidden_attrib_list -Array of VO attribute names which doesn't need to be shown/written in
* csv file.
*/
public void downloadCsvFile(OAPageContext pageContext,
String view_inst_name,
String file_name_without_ext,
String max_size, String[] hidden_attrib_list)
{
OAViewObject v =
(OAViewObject) pageContext.getRootApplicationModule().findViewObject(view_inst_name);

if (v == null)
{
throw new OAException("Could not find View object instance " +
view_inst_name + " in root AM.");
}
if (v.getFetchedRowCount() == 0)
{
throw new OAException("There is no data to export.");
}
String file_name = "Export";
if (!((file_name_without_ext == null) ||
("".equals(file_name_without_ext))))
{
file_name = file_name_without_ext;
}
HttpServletResponse response =
(HttpServletResponse) pageContext.getRenderingContext().getServletResponse();
response.setContentType("application/text");
response.setHeader("Content-Disposition",
"attachment; filename=" + file_name + ".csv");
PrintWriter pw = null;

try
{
pw = response.getWriter();
int j = 0;
int k = 0;
boolean bb = true;
if ((max_size == null) || ("".equals(max_size)))
{
k = Integer.parseInt(pageContext.getProfile("VO_MAX_FETCH_SIZE"));
bb = false;
}
else if ("MAX".equals(max_size))
{
bb = true;
}
else
{
k = Integer.parseInt(max_size);
bb = false;
}

//Making header
AttributeDef[] a = v.getAttributeDefs();
StringBuffer cc = new StringBuffer();
ArrayList exist_list = new ArrayList();
for (int l = 0; l < a.length; l++)
{
boolean zx = true;
if (hidden_attrib_list != null)
{
for (int z = 0; z < hidden_attrib_list.length; z++)
{
if (a[l].getName().equals(hidden_attrib_list[z]))
{
zx = false;
exist_list.add(String.valueOf(a[l].getIndex()));
}
}
}
if (zx)
{
cc.append("\"" + a[l].getName() + "\"");
cc.append(",");
}
}
String header_row = cc.toString() + "\n";
pw.write(header_row);

for (OAViewRowImpl row = (OAViewRowImpl) v.first(); row != null;
row = (OAViewRowImpl) v.next())
{
j++;
StringBuffer b = new StringBuffer();
for (int i = 0; i < v.getAttributeCount(); i++)
{
boolean cv = true;
for (int u = 0; u < exist_list.size(); u++)
{
if (String.valueOf(i).equals(exist_list.get(u).toString()))
{
cv = false;
}
}

if (cv)
{
Object o = row.getAttribute(i);

if (!(o == null))
{
if (o.getClass().equals(Class.forName("oracle.jbo.domain.Date")))
{
//formatting of date
oracle.jbo.domain.Date dt = (oracle.jbo.domain.Date) o;
java.sql.Date ts = (java.sql.Date) dt.dateValue();
java.text.SimpleDateFormat displayDateFormat =
new java.text.SimpleDateFormat("dd-MMM-yyyy");
String convertedDateString = displayDateFormat.format(ts);
b.append("\"" + convertedDateString + "\"");
}
else
{
b.append("\"" + o.toString() + "\"");
}
}
else
{
b.append("\"\"");
}
b.append(",");
}
}
String final_row = b.toString() + "\n";
pw.write(final_row);
if (!bb)
{
if (j == k)
{
break;
}
}
}
}
catch (Exception e)
{
// TODO
e.printStackTrace();
throw new OAException("Unexpected Exception occured.Exception Details :" +
e.toString());
}
finally
{
pw.flush();
pw.close();
}
}

Happy coding!