ADF/BC JDBC to retrieve structured data from packaged function

Got a request for code to show how to retrieve a collection of structured data (Oracle Type Objects) using JDBC in an ADF/BC scenario. The first parameter is the name of the Oracle Type that is the collection of your structured data type.

public Object callStoredFunctionReturningArrayOfRecords(String pfunctionReturnType, String stmt,Object[] bindVars) throws SQLException {
oracle.sql.STRUCT [] returnArray = null;
String [] recordArray = null;
Connection conn = getDBTransaction().createStatement(1).getConnection();
//Connection conn = getConnection();
// Now, declare a descriptor to associate the host array type with the
// array type in the database.


ArrayDescriptor arrayDescriptor=ArrayDescriptor.createDescriptor(pfunctionReturnType, conn);
// example:  StructDescriptor structDescriptor = StructDescriptor.createDescriptor("TEST_ARR_OF_REC_TYPE", conn);


// Create the ARRAY objects to associate the host array
// with the database array.


oracle.sql.ARRAY returnARRAY = new oracle.sql.ARRAY(arrayDescriptor,conn,returnArray);

OracleCallableStatement st = null;
try {
// 1. Create a JDBC CallabledStatement
st = (OracleCallableStatement)getDBTransaction().createCallableStatement(
"begin ? := " + stmt + ";end;", 0);


// 2. Register the first bind variable for the return value
st.registerOutParameter(1, OracleTypes.ARRAY, pfunctionReturnType);
if (bindVars != null) {
// 3. Loop over values for the bind variables passed in, if any
for (int z = 0; z < bindVars.length; z++) {
// 4. Set the value of user-supplied bind vars in the stmt
st.setObject(z + 2, bindVars[z]);
}
}
// 5. Set the value of user-supplied bind vars in the stmt
st.executeUpdate();
// Associate the returned arrays with the ARRAY objects.


returnARRAY = (oracle.sql.ARRAY)st.getARRAY(1);
// OracleResultSet mainRS = (OracleResultSet)st.getResultSet();
// ARRAY anotherARRAY = mainRS.getARRAY(1);


// Get the data back into the data arrays.
// NOTE: I got an NPE on the following line at one point...not sure why...
// I saw this error in opmn log; not sure if returnARRAY was null or
// if there are special rules about casting null into an array.
Object[] oarray = (Object[])returnARRAY.getArray();
// Object[] oarray = (Object[])anotherARRAY.getArray();


for (int i = 0; i < oarray.length; i++) {
oracle.sql.STRUCT struct = (oracle.sql.STRUCT)oarray[i];//new STRUCT(structDescriptor, conn, recordArray);
StructDescriptor structDescriptor = struct.getDescriptor();
ResultSetMetaData rsmd = structDescriptor.getMetaData();
Object [] attrs = struct.getAttributes();
if (rsmd != null && attrs != null && attrs[0] != null && attrs[1] != null) {
getLogger().fine("nested row [" + i + "]: " + rsmd.getColumnLabel(1) +
" " + attrs[0].toString() + " " + rsmd.getColumnLabel(2) +
" " + attrs[1].toString());
}
}
// 6. Return the value of the first bind variable
return oarray;
} catch (SQLException e) {
throw new JboException(e);
} finally {
if (st != null) {
try {
// 7. Close the statement
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

dojo and JSF integration page unload solution when auto-saving

If you have integrated dojo and JSF in the way described here in the authors' first method of integration, the you probably ran into a problem of how to copy the values back to the hidden field -- if you are trying to implement an auto-save feature.  In other words, if you want to allow a user to not have to click a link/button in order to save their changes then you must find some other client event to tap into.  I believe the authors of that article I referred to used an example for their first method which simply used onclick of a button to save back a value.

For the purposes of my auto-save feature, I went with something much more piggy:  onkeyup for the dojo field itself.  So with every keystroke I copy back the value to the hidden field.  This fix was quick, and it really is not that slow.

For a more efficient implementation, you could program javascript to wait 5 seconds after every keystroke, then check to see if the value has changed, but I wonder if the simple copy would be more efficient than all that...probably!

ADF dialog framework simplified?

If you can overcome about any (needless) fear you might have about implementing a custom JSF NavigationHandler, you can actually open up your applications for many new possibilities.

The focus of this blog post is not how to create custom JSF framework extensions.  It is just this:  every time you use ADF's dialog framework (where you create an navigation outcome of the form "dialog:...", set useWindow in the af:commandButton/Link to true, partialSubmit to true) it is possible to create a bit of custom NavigationHandler code that simply launches a dialog window programatically instead of doing the normal navigation function. 

I tried this out because I was "forced to" due to a quirk of af:commandButton/Link which somehow has useWindow property ignored when inside of an af:iterator.  So I tried to think of the spot where it made the most sense to programmatically call up the dialog...duh!  The NavigationHandler of course.

JSF 2.0, Part I.b.

Initially the example I got working was just the downloaded code from the book.  Since then I also found out why my own assembed version (copied from the text of the book) did not work.

The short answer is:  I made some typos.

The longer answer is that:  I discovered that in web.xml, the JSF runtime is very sensitive to inaccuracies in the root node attributes.  I guess I thought they were not that important, but jsf runtime could not even run my application without correcting a typo in these attributes.  Before I corrected this typo I kept getting a "resource cannot be found" or something like that.

After that correction, I still had several more typos, but for each there was a sensible JSF error to help me find it, unlike the first.

Also used maven to compile the book's examples...first on-line...then on a different example...offline.  maven is such a boon the way it downloads what it needs the first time you run it, so that if you run it again on the same pom...or one with similar dependencies...it uses what it downloaded the last time to assemble things.  Very cool.

I should also mention in Glassfish that I deployed my exploded directory also, so now I can change xhtml files on the fly and just refresh the page and I see my page with the new changes in it when running the Glassfish JSF 2.0 app.

Just got my first JSF 2.0 program working on Glass Fish

I just got my first JSF 2.0 program working on Glass Fish.  Maybe I can be ahead of the power curve on this one!  I think I heard Frank Nimphius say something (unofficially) hinting that they might have plans to use JSF 2.0 2011...in ADF Faces. 

So I am studying JavaServer Faces 2.0: the complete reference by Burns and Schalk on airplane rides back and forth to/from VA from/to CO.  Also when I am on the treadmill in VA.

Also Frank Nimphius and Lynn Munsinger's new book as well.

I have submitted abstracts for both of these topics to both JavaOne and Oracle Open World...so I am under the gun now.  Nothing new there.

AD4J provides surgical improvements

I have talked about AD4J before.  I revisited it after doing some hands-on labs with it at Oracle Open World.  I still think I could use a huge manual of discussions on what to do with all the information they provide.  Maybe that is a class Oracle provides...I don't know.

I do know this...in my basic state of not knowing much about AD4J, I was able to do a default install (there is more to do on this install if you are using SSL, but if not the install takes hardly any time at all), deploy the agent app to my target OC4J container, and do a 60-second monitor of my system which resulted in lots of data.

It also has, right there in front of you, a list of the top 5 or 10 most "expensive" things that were going on while it was monitoring.  During the monitoring session, I was randomly darting about in my app which has been slow.

It gave my pl/sql calls a relatively high rating.  I checked it out...I was making 28 pl/sql calls unnecessarily with each request!!  I corrected this, and now my slow app is quite a bit faster!!  Thank you AD4J!!  (AKA Jade).

RowSetIterator example

Because of its utility I would like to give an example of a piece of RowSetIterator code which will help avoid row currency issues, even if your void object instance is used by a UI component:

I use it as a method in VOImpl class to help me navigate to a particular row without changing the row currency of the RowSetIterator of the VO instance that is also connected to a UI and could thereby cause row currency issues. (BTW this was a best practice I took out of the ADF Developers 10g Guide; my guess is that it is still best practice...but it might not be.):

  private AnotherViewRowImpl findRowMatch (Number pMenuId) {

        if (!this.isExecuted()) {

            this.executeQuery();

        }

        boolean lFoundMatch = false;

        TbReviewerCommentAnotherViewRowImpl lRow = null;

        RowSetIterator lRSI = this.createRowSetIterator(null);

        while(lRSI.hasNext()) {

            lRow = (TbReviewerCommentAnotherViewRowImpl)lRSI.next();

            if (pMenuId != null && pMenuId.equals(lRow.getMenuItemId())) {

                lFoundMatch = true;

                break;

            }

        }

        lRSI.closeRowSetIterator();

        if (!lFoundMatch) {

            lRow = null;

        }

        return lRow;

    }

I'm an Oracle ACE now!

Yay!!

Books I'm Reading

JavaServer Faces 2.0: the complete reference by Ed Burns, Chris Schalk

Also

Oracle Fusion Developer Guide by Frank Nimphius and Lynn Munsinger

MetaObjectManager scope (I'm listening now...)

I recommend that people working with ADF on JDev 10.1.3 read the MetaObjectManager entry at the following URL: http://blogs.oracle.com/Didier/bc4j/

...especially if you are deploying applications to OC4J and have overridden the DatabaseTransactionFactory... you might have done this if you wanted to do a post-commit or pre-commit type move, as was laid out in the Oracle ADF Developer's Guide for 4GL Developers version 10.1.3.0.

I first noticed when I added this override that, odd crap began to occur with my and others' deployed applications. Essentially they all began trying to use my SAME DatabaseTransactionFactory, which is not what I intended at all. The scope of this AM Module configuration property is something called MetaObjectManager. This means that every app deployed to that same OC4J instance will have the same setting...presuming I was the first one to deploy an app with this property defined in my bc4j.xcfg to that OC4J instance. This is not a smart way to identify a global setting...i.e., hidden in an app.

I am repeating this because, for me at any rate, the topic apparently bares repeating.