The PeopleCode language has a lot of features and functions. But sometimes, it seems there are tasks we need to accomplish that are just out of reach of the PeopleCode language. It is at these times that I reach for Java. I have written a lot about Java, so I'm sure many of you already know how to mix Java with PeopleCode. While certainly a rational solution, one of the pain points of a Java solution is managing custom Java on the app and process scheduler servers. Each time you update your compiled class (or jar) files, you have to restart the server. That might be OK in a stable production environment, where you don't intend to change code often, but in development, it is a real pain! Likewise, maintaining custom Java class and jar files through upgrades can be a little sketchy. Specifically, if you redeploy PeopleTools or rewrite psconfig, then it is possible you may miss some of your custom Java code. PeopleBooks tells us how to setup psconfig for custom Java classes, but again, that is just one more thing to manage through upgrades. Now, imagine being able to update your custom Java code with a Data Mover script. Further, imagine being able to run custom Java without making any changes to your application server. Imagine what it would be like to run Java without having to beg (or bribe) your admin for a "no customization" exception. It is possible today. The answer: Use JavaScript to interface between PeopleCode and the delivered Java Runtime Environment. Through the embedded Mozilla Rhino JavaScript script engine of Java, we have full, dynamic access to the JRE. When and how would you use this? Let's review some examples.
Custom HTTP Connections
For various reasons, some customers choose not to implement Integration Broker. These customers find themselves requiring integration, but without IB's networking features. An alternative to %IntBroker.ConnectorRequestURL is to use Java's HttpURLConnection.I strongly discourage this approach, but the question arises. The JRE is there, well integrated with PeopleCode, and ready for use. From PeopleCode, it is possible to create a Java URLConnection using CreateJavaObject("java.net.URL", "http...").openConnection(). A problem arises when we try to invoke methods of a HttpURLConnection, the real return value of URL.openConnection. Unfortunately, PeopleCode doesn't see it that way, which leads down the reflection path (we don't want to go there). This is where JavaScript can help us. JavaScript doesn't mind that URL.openConnection returned an HttpURLConnection even though it said it would just return a URLConnection. Here is an example:
var result = (function() {
var URL = Packages.java.net.URL;
var InputStreamReader = Packages.java.io.InputStreamReader;
var BufferedReader = Packages.java.io.BufferedReader;
var StringBuilder = Packages.java.lang.StringBuilder;
var serverAddress = new URL(
"http://hcm.oraclecloud.com/hcmCoreApi/atomservlet/employee/newhire"
);
var connection = serverAddress.openConnection();
connection.setRequestMethod("GET");
connection.setReadTimeout(10*1000);
connection.connect();
var reader = new BufferedReader(
new InputStreamReader(connection.getInputStream()));
var sb = new StringBuilder();
var line;
while ((line = reader.readLine()) !== null) {
sb.append(line + '\n');
}
return sb;
}());
Excel Spreadsheets
PeopleTools 8.55+ has a PeopleCode API for Excel, which means this solution is now irrelevant. I'm listing it because not everyone is up to PeopleTools 8.55 (yet). If you use this idea to build a solution for 8.54 and later upgrade, Oracle recommends that you switch to the PeopleCode Excel API. The solution will still work with 8.55+, but just isn't recommended post 8.54.
This solution uses the Apache POI library that is distributed with PeopleTools 8.54+ to read and write binary Microsoft Excel files. As with the networking solution above, it is possible to use POI directly from PeopleCode, but a little difficult because POI uses method overloading in a manner that PeopleCode can't resolve. Furthermore, POI uses methods that return superclasses and interfaces that PeopleCode can't cast to subclasses, leading to awful reflection code. Here is an example that reads a spreadsheet row by row, inserting each row into a staging table for later processing.
if (!String.prototype.endsWith) {
String.prototype.endsWith = function(searchString, position) {
var subjectString = this.toString();
if (typeof position !== 'number' || !isFinite(position) ||
Math.floor(position) !== position ||
position > subjectString.length) {
position = subjectString.length;
}
position -= searchString.length;
var lastIndex = subjectString.indexOf(searchString, position);
return lastIndex !== -1 && lastIndex === position;
};
}
var result = (function() {
var FileInputStream = Packages.java.io.FileInputStream;
var HSSFWorkbook = Packages.org.apache.poi.hssf.usermodel.HSSFWorkbook;
var Workbook = Packages.org.apache.poi.ss.usermodel.Workbook;
var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);
}
);
var fis = new FileInputStream(fileName);
var workbook;
if(fileName.toLowerCase().endsWith("xlsx")) {
workbook = new XSSFWorkbook(fis);
} else if(fileName.toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(fis);
}
var sheet = workbook.getSheetAt(0);
var rowIterator = sheet.iterator();
var roleName,
descr,
row;
while (rowIterator.hasNext()) {
row = rowIterator.next();
roleName = row.getCell(0).getStringCellValue();
descr = row.getCell(1).getStringCellValue();
SQLExec("INSERT INTO PS_JM_XLROLE_STAGE VALUES(:1, :2, :3, SYSTIMESTAMP)",
[guid, roleName, descr]
);
}
return guid;
}());
Here is an example of writing/creating a Microsoft Excel spreadsheet:
var result = (function() {
var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
var FileOutputStream = Packages.java.io.FileOutputStream;
var workbook = new XSSFWorkbook();
var sheet = workbook.createSheet("Countries");
var fileName = "c:/temp/countries.xlsx";
var row = sheet.createRow(0);
var cell = row.createCell(0);
cell.setCellValue("United States of America");
cell = row.createCell(1);
cell.setCellValue("USA");
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("India");
cell = row.createCell(1);
cell.setCellValue("IND");
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("Denmark");
cell = row.createCell(1);
cell.setCellValue("DNK");
var fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
return "Created workbook " + fileName;
}());
JSON Parsing
If your goal is to convert a JSON string into SQL insert statements, then this is a very painless alternative:
var result = (function() {
var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
var Name = Packages.PeopleSoft.PeopleCode.Name;
var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
var rec = CreateRecord(new Name('RECORD', 'NAA_SCRIPT_TBL'));
var count = 0;
var json_string;
var json;
var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);
});
rec.GetField(new Name('FIELD', 'PM_SCRIPT_NAME')).setValue('JSON_TEST_DATA');
rec.SelectByKey();
json_string = rec.GetField(new Name('FIELD', 'HTMLAREA')).getValue();
json = JSON.parse(json_string);
json.forEach(function(item, idx) {
SQLExec("INSERT INTO PS_NAA_TEST_TBL VALUES(:1, :2, :3, SYSTIMESTAMP)",
[guid, item.emplid, item.oprid]
);
count += 1;
});
return "Inserted " + count + " rows";
}());
I could go on and on with examples of creating zip files, encrypting information, base64 encoding binary data, manipulating graphics using Java 2D, etc, but I think you get the idea.
