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

While replying several threads on OA Forums(http://forums.oracle.com/forums/forum.jspa?forumID=210), 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 eg.is 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 eg.is as follows:*******/
/******************************************************************************/
create or replace package body ioStructArray as
procedure testproc(iorec in rectab,orec out rectab) is
begin
/*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:
//imports
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;

try
{
//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!=null;
row = (XXVORowImpl)XXVO.next())
{
//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.
populateObjectArraylist(row,voRowStruct,arow);
}

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

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

//jdbc code to execute pl/sql procedure
try
{
cStmt
=(OracleCallableStatement)conn.prepareCall("{CALL ioStructArray.testproc(:1,:2)}");
cStmt.setArray(1,p_message_list);
cStmt.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");
cStmt.execute();

//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
attribMessage[0]=attr1;
attribMessage[1]=attr2;
attribMessage[2]=attr3;

try
{
STRUCT loadedStructTime = new STRUCT(voRowStruct, conn, attribMessage);
arow.add(loadedStructTime);
}
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!

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.)