OC4J or Java Stored Procedures
Some weeks ago I alluded that I would post a follow up to my Java Dilemma once I was back on site.
During this time I have been studiously working on a solution which allows a user to complete a computerized pdf form and submit it the server where a neat bit of code strips out all the user responses from a pdf form as XML. This in turn is then transformed to make it more useful to the system as a whole and stored in relational tables.
This process is a legacy requirement which compliments a new online, web-based, data input system written entirely in Oracle Apex. Ideally it would be nice to stipulate that all users complete the form via the web-based front-end. Unfortunately not all the users have access to the system but still need to get their information into the system easily, hence the need for an offline form.
The architecture employed is a typical Oracle Stack utilizing DB 10gR2, Apex 2.2, and AS/OC4J 10.1.2 with SSO. Architecturally we decided that it would be best to put all the Java Processing in a container on the web tier and write some custom JSPs to handle form upload, user response extraction and xml insertion into the database. As shown in Figure 1.
Figure 1:
The solution makes use of the Adobe XML/PDF Access API for Java or XPAAJ for short. Without the XPAAJ APIs this functionality would not be possible (easily). Unfortunately XPAAJ requires the Apache Xcerses and Xalan XML parser APIs. A suspected bug with Application Server 10.1.2 forces the use of the Oracle XML parsers as default and the Container class-path cannot be changed easily (in a supported manner).
In Application Server 10.1.3 there is a very nice and easy way to accomplish this in a supported manner. This process is outlined on otn.
AS/OC4J 10.1.3 was not an option for us and it meant we had to go back to the drawing board and use Java Stored Procedures. This approach is shown in Figure 2. However, this has given a very clear advantage. The Java procedures can be very easily used from within ApEx. This removes the need for writing tedious JSPs to manage the file upload process and instead allows facilitates the use of a simple ApEx file browser page item. With a pl/sql wrapper round the Java, the functionality is exposed seamlessly to the ApEx page just like any other pl/sql procedure. As the rest of the application we are writing is in Apex this keeps things very simple indeed.
Figure 2:
I'll be writing a simple how to which does a complete walk through once I have the time. Otherwise I hope some of the difficulties I've experienced with 10.1.2 will save someone some time.
Figure 1:
The solution makes use of the Adobe XML/PDF Access API for Java or XPAAJ for short. Without the XPAAJ APIs this functionality would not be possible (easily). Unfortunately XPAAJ requires the Apache Xcerses and Xalan XML parser APIs. A suspected bug with Application Server 10.1.2 forces the use of the Oracle XML parsers as default and the Container class-path cannot be changed easily (in a supported manner).
In Application Server 10.1.3 there is a very nice and easy way to accomplish this in a supported manner. This process is outlined on otn.
AS/OC4J 10.1.3 was not an option for us and it meant we had to go back to the drawing board and use Java Stored Procedures. This approach is shown in Figure 2. However, this has given a very clear advantage. The Java procedures can be very easily used from within ApEx. This removes the need for writing tedious JSPs to manage the file upload process and instead allows facilitates the use of a simple ApEx file browser page item. With a pl/sql wrapper round the Java, the functionality is exposed seamlessly to the ApEx page just like any other pl/sql procedure. As the rest of the application we are writing is in Apex this keeps things very simple indeed.
Figure 2:
I'll be writing a simple how to which does a complete walk through once I have the time. Otherwise I hope some of the difficulties I've experienced with 10.1.2 will save someone some time.
