Uploading Excel Spreadsheet into Oracle eBusiness Suite. Part 6.

Writing Java Concurrent Program
Any Java class that implements interface oracle.apps.fnd.cp.request.JavaConcurrentProgram can be registered as a concurrent program.

The only method which needs to be implemented is

public void runProgram(oracle.apps.fnd.cp.request.CpContext cpcontext)

CpContext provides developer with all attributes of a PL/SQL concurrent program such as output file, log file, and return status plus a database connection. A typical implementation of this method looks like

public void runProgram(CpContext cpcontext) {
try {
// read parameters
// execute business logic
cpcontext.getReqCompletion().setCompletion(ReqCompletion.NORMAL, “Request Completed Normal”);
} catch (Exception ex) {
// report exception
cpcontext.getReqCompletion().setCompletion(ReqCompletion.ERROR, “Error building output file”);
} finally {
cpcontext.releaseJDBCConnection();
}
}

Though this template is small, it does illustrate the key differences between Java and PL/SQL concurrent program.

By default, Java concurrent program completes with an error, unlike PL/SQL program which completes successfully. Hence, it is imperative to set up not only completion in error, but also when program completes successfully.

At the end of the execution of the Java concurrent program, you must release JDBC connection back to the pool.

Reading Parameters

Concurrent program parameters are accessible via method cpcontext. getParameterList. This method returns an instance of oracle.apps.fnd.util.ParameterList class. This class provides an Enumeration interface to access all the parameters passed to the program in the order defined during the concurrent program registration. This class has one interesting drawback. You can read the parameters only once.

Each concurrent program parameter is represented by an instance of class oracle.apps.fnd.util.NameValueType. It is clear from the name of the class that it provides name, type and string value of the parameter. It means that if you have a date value as the parameter to the concurrent program, it will be available to the Java concurrent program as a canonical string.

I found it useful to have a utility that converts the ParameterList into a Map.

static public Map convertParameters(ParameterList parameterList)
{ Map result = new HashMap();
while( parameterList.hasMoreElements() ) {
NameValueType nameValueType = parameterList.nextParameter();

if (nameValueType.getValue() != null)
{
result.put(nameValueType.getName(), nameValueType.getValue());
}
}
return result;
}

Map provides a better controlled access to the list of parameters. The method can be enhanced to recognize the type of the value and convert them from String into BigDecimal or Date.

Reporting Exception

Exception.printStackTrace() does not work here!!!

If you really want the user to see the exception stack, you need to print it into the oracle.apps.fnd.cp.request.LogFile. An instance of this class is available via cpcontext.getLogFile(). This class provides an OutputStream like interface, though it is not an implementation of java.io.OutputStream.

LogFile provides a number of methods to write strings into the output stream like

write(String message, int level)

Here, level specifies the debugging level which can be set from LogFile.STATEMENT to LogFile.EXCEPTION.
In order to report exception to the log file, you can use the following code

StringWriter writer = new StringWriter();
ex.printStackTrace(new PrintWriter(writer));
logFile.writeln(writer.toString(), logFile.EXCEPTION);

Writing Output

The key task of the concurrent program which we are writing is to read the CLOB that contains the file contents and write it into its output stream. The output of a concurrent program can later be accessed by SQL*Loader, other concurrent program or, even, OAF web pages and services outside of Oracle eBusiness Suite.

To access the database, we need to get hold of the database connection. An instance of the connection can received from cpcontext.getJDBCConnection().

The following method retrieves the CLOB from the database as and InputStream which is then converted into a String.

protected String getOutput(Connection connection, BigDecimal primaryKey) throws SQLException, IOException
{
String statement = “select file_contents from xxpt_file_upload_tmp where id = :1″;
String result;
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
stmt = connection.prepareStatement(statement);
stmt.setBigDecimal(1, primaryKey);
resultSet = stmt.executeQuery();
resultSet.next();
result =streamToString( resultSet.getAsciiStream(1) );
resultSet.close();
resultSet = null;
stmt.close();
stmt = null;
} finally
{
if (resultSet != null)
{
try { resultSet.close(); } catch (SQLException ex) {}
}
if (stmt != null)
{
try { stmt.close(); } catch (SQLException ex) {}
}
try { connection.commit(); } catch (SQLException ex) {}
}
return result;
}

In this code streamToString method read the InputStream into a String.

An instance of oracle.apps.fnd.cp.request.OutFile get be obtained using methof cpcontext.getOutFile(). This class provides OutputStream like interface, though it is not an implementation of java.io.OutputStream. Writing a string into the output is as simple as

cpcontext.getOutFile().write(fileContents);

Submit Concurrent Program from Java

Oracle provides Java classes to submit a concurrent program. Definitely, one can call PL/SQL procedure directly using JDBC connection, though the Java wrapper is much nicer and easier to use.

Class oracle.apps.fnd.cp.request.ConcurrentRequest represents submission of a concurrent request. You can setup optional layouts, notifications, schedule and other attributes available through FND_REQUEST package.

Constructor of ConcurrentRequest class accepts database connection instance as a parameter.
Signature of the method that submits the concurrent request for execution

submitRequest(String application, String program, String description, String startTime, String subrequest, Vector parameters)

mimics parameters of the corresponding PL/SQL procedure FND_REQUEST.SUBMIT_REQUEST. Here, parameters is the vector of String in the order the parameters are specified during the concurrent program registration. If one of your parameters is a Date, it is your responsibility to convert it into the canonical string.

So, the code that submits the concurrent program may look like

protected void submitConcurrentProgram(Number primaryKey, String purposeCode, int orgId, Connection connection) throws IOException, SQLException, RequestSubmissionException
{
ConcurrentRequest request = new ConcurrentRequest(connection);
Vector param = new Vector();
param.add(primaryKey.stringValue());
param.add(purposeCode);
param.add(String.valueOf(orgId));

int reqId = request.submitRequest(”XXPT”, “XXPTCLOBOUTPUT”, “Print CLOB”,null, false, param);
connection.commit();

MessageToken[] tokens = { new MessageToken(”REQUEST”, String.valueOf(reqId)) };
OAException confirmMessage = new OAException(”XXPT”,
“XXPT_REQUEST_SUBMIT_CONF”,
tokens,
OAException.CONFIRMATION,
null);
throw confirmMessage;
}

As you can see, upon successful submission of the concurrent program, we raise a confirmation exception with the request ID.

This post concludes the series. In the course of six installments we learned the overall technical design, how to build OAF based UI to upload a file, how to parse an Excel spreadsheet into a comma delimited file, how to write a Java concurrent program and submit it from an OAF page controller. Everything I was talking about here has been built and is actively being used by our customers.