Uploading Excel Spreadsheet into Oracle eBusiness Suite. Part 5.

Storing CLOB in database table
Once we have the input stream converted into an ASCII string, we need to store it in the custom table. To perform this operation, we need to generate primary key, and extract the purpose for the upload.

We already discussed the query which generated globally unique primary key, and creation of a view object PrimaryKeyGeneratorVO based on this query. The method which generates the primary key becomes

protected Number generatePrimaryKey()
{
OAViewObject viewObject = getPrimaryKeyGeneratorVO();
viewObject.setMaxFetchSize(1);
viewObject.executeQuery();
return (Number) (viewObject.first().getAttribute(”Id”));
}

In order to determine the select purpose, we need to walk through each row of purposes in the current range, and locate the one with checked attribute ‘Selected’, since this was the transient attribute designated to work with table single selection. The method is

public String getSelectedPurpose()
{
OAViewObject viewObject = getFileUploadPurposeVO();
Row[] rows = viewObject.getAllRowsInRange();
if (rows.length > 0)
{
for(int i=0; i {
Row row = rows[i];
if ("Y".equals(row.getAttribute("Selected"))) {
return (String)row.getAttribute("LookupCode");
}
}
}
return null;
}

Now, we are ready to store data in the database. We will need the purpose of the upload later when we submit the concurrent program to print the file contents in its output. So, we extract the purpose separately and pass as a parameter to the storage procedure, which now looks like

public Number storeStream(String inputStream, String purposeCode)
{
OAViewObject viewObject = am.getXXPTFileUploadTmpVO();
viewObject.setMaxFetchSize(0);

ClobDomain myClob = new ClobDomain();
myClob.setChars(inputStream.toCharArray());
OARow row = (OARow)viewObject.createRow();
row.setAttribute(”FileContents”,myClob);
Number primaryKey = generatePrimaryKey();
row.setAttribute(”PurposeCode”, purposeCode);
row.setAttribute(”Id”, primaryKey);
viewObject.insertRow(row);
am.getTransaction().commit();
return primaryKey;
}

The method returns the primary key of the newly created record. We will later pass this key to the concurrent program that prints the output of the CLOB into its output.

Stay with us to learn how to write Java concurrent programs.