Sunday, October 26, 2014

RDBMS, Java -- Working with the Java inside the Oracle Database

It all started with a Java Source compilation error. The problematic code was a Java Source, which was tried to be compiled inside the Oracle Database..
The code was written to make a web service call remotely using the Oracle Database using Java. The creation script was starting as follows;

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "BLABLA"
AS import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.nio.charset.Charset;
import java.sql.Clob;
import java.sql.SQLException;
import javax.xml.soap.MessageFactory;
import javax.xml.soap.MimeHeaders;
import javax.xml.soap.SOAPConnection;
import javax.xml.soap.SOAPConnectionFactory;

import javax.xml.soap.SOAPException;
import javax.xml.soap.SOAPMessage;

public class WebServiceCall {
private static SOAPMessage getSoapMessageFromString(String xml) throws SOAPException, IOException {
MessageFactory factory = MessageFactory.newInstance();
SOAPMessage message = factory.createMessage(new MimeHeaders(), new ByteArrayInputStream(xml.getBytes(Charset.forName("UTF-8"))));
return message;
}

......

And it was continuing, without any syntax errors. It was actually compiled in an 11.2.0.4 Oracle database without any problems, but when it have come compiling it in an 11.2.0.3 Oracle Database , the customer had throubles, which made me writing this post ..

As you may expect, the Java Souce could not be compiled on 11.2.0.3..
The key errors were as follows;

09:54:01 AS import java.io.BufferedReader;
09:54:01 ...
09:54:02 ORA-24344: compilation error
09:56:01 Start Compiling 1 object(s) ...
09:56:01 Executing ALTER JAVA SOURCE blabla COMPILE ...
09:56:02 [0:0] blabla:10: cannot find symbol
09:56:02 [0:0] symbol : class MessageFactory
09:56:02 [0:0] location: package javax.xml.soap
09:56:02 [0:0] import javax.xml.soap.MessageFactory;
09:56:02 [0:0] blabla:11: cannot find symbol
09:56:02 [0:0] symbol : class MimeHeaders
09:56:02 [0:0] location: package javax.xml.soap
09:56:02 [0:0] import javax.xml.soap.MimeHeaders;
09:56:02 [0:0] blabla:12: cannot find symbol
09:56:02 [0:0] symbol : class SOAPConnection
09:56:02 [0:0] location: package javax.xml.soap
..
...
.....

And a lot more ....

Okay, the cause of these errors were the JDK embedded / inside the Oracle Database..  It was clear that some packages could not be found , especially javax.xml.soap..

The developers were thinking that at least Oracle 11.2.0.4 was needed to compile such a Java Source, as it was clear that 11.2.0.3 didnt have the necessary java packages.. That's why an 11.2.0.4 upgrade was requested immediately..
On the other hand; the customer's Dba didnt approve this request because it was only about a single Java object.
At this point , I step in, made the following analysis and  solved the problem without a need to upgrade the entire database.
It was a productive day, which made me do practices in Java.

First of all, there is no document or a whitepaper saying that 11.2.0.4 is needed for compiling these kind of Java Souce objects..
The only statement I could find in Oracle, was  "Release 11.2.0.4 provides an enterprise class platform, Oracle JVM, for developing and deploying server-based Java applications."

So, this could mean that the Entprise Java packages(like soap) in 11.2.0.4 were coming by default.
That seemed to be true, because the Java source was compiling without any errors in 11.2.0.4.
However; we had 11.2.0.3 , so we needed to find a solution in place.

First, checked the component status in the reqisty using the following query;
I was interested to see the Java Virtual Machine Status and Jasva Database Java Packages status.

Select comp_name, status, version
from dba_registry ;

COMP_NAMESTATUSVERSION
OWBVALID11.2.0.3.0
Oracle Application ExpressVALID3.2.1.00.12
Oracle Enterprise ManagerVALID11.2.0.3.0
OLAP CatalogVALID11.2.0.3.0
SpatialVALID11.2.0.3.0
Oracle MultimediaVALID11.2.0.3.0
Oracle XML DatabaseVALID11.2.0.3.0
Oracle TextVALID11.2.0.3.0
Oracle Expression FilterVALID11.2.0.3.0
Oracle Rules ManagerVALID11.2.0.3.0
Oracle Workspace ManagerVALID11.2.0.3.0
Oracle Database Catalog ViewsVALID11.2.0.3.0
Oracle Database Packages and TypesVALID11.2.0.3.0
JServer JAVA Virtual MachineVALID11.2.0.3.0
Oracle XDKVALID11.2.0.3.0
Oracle Database Java PackagesVALID11.2.0.3.0
OLAP Analytic WorkspaceVALID11.2.0.3.0
Oracle OLAP APIVALID11.2.0.3.0
Oracle Real Application ClustersVALID11.2.0.3.0

Everything seemed okay..
Then I checked to see the soap package .. I was interested to see its presence..

select *
from dba_objects
where object_type like '%JAVA%'
and owner = 'SYS'
and object_name like '%soap%'

Okay, the problem was that as expected.. The soap package was missing.. That is, it was not coming in 11.2.0.3 by default.. So it seemed; we had to install/load these kind of missing packages manually using jars...
Okay good, but there were no references for this kind of operations , especially for soap..
Maybe that was the reason that made the Developers think 11.2.0.4 as one an only solution.
Only the following documents were making sense, but they weren't point shots, and unfortuneatly they were for older releases.
How to Load Soap.jar into Oracle Database (Doc ID 344799.1)
They were not answering the question likes which soap package to use; where to download what are the dependencies and etc..

On the other hand; there was a document in Oracle Support for making these kind of web services operations using PL/SQL rahter than Java.
Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service (Doc ID 841183.1)

Altough, this document seemed unrelated , it had an excellent reference to a jar file..
In te 4th Step of this  documents; it was saying that : Load the necessary core web services callout jar files into the database. This step is to load the core Java components and is a completely separate action from loading the PL/SQL package as described in the previous steps. This means that the considerations for completing this step are entirely different from the loading of the PL/SQL components.

The load that was mentioned here was by using the dbwsclientws.jar and dbwsclientdb11.jar files.
These jar files were located in te UTL_DBWS utiliy , which can be downloaded from Oracle Support. Download the LATEST copy of the UTL_DBWS utility zip file from the Oracle Technology Network (OTN).  This file, for an 11G database, is named dbws-callout-utility-10131.zip and can be obtained from here. 

By using the following sequence of commands loading of soap package could be done;
cd $ORACLE_HOME/sqlj/lib (replacing $ORACLE_HOME with the proper directory structure)
loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

The customer loaded those jars; and as expected it seemed loading these jars would bring the soap java package in to the database , and these actions actually did bring the soap java package :)

So far so good. No effort for manual load was needed, but this time another compilation errors were encountered..

14:31:13 Executing ALTER JAVA SOURCE blabla COMPILE ...
14:31:13 [0:0] blabla:21: cannot find symbol
14:31:13 [0:0] symbol : method getBytes(java.nio.charset.Charset)
14:31:13 [0:0] location: class java.lang.String
14:31:13 [0:0] SOAPMessage message = factory.createMessage(new MimeHeaders(), new ByteArrayInputStream(xml.getBytes(Charset.forName("UTF-8"))));
14:31:13 [0:0] ^
14:31:13 [0:0] blabla:31: cannot find symbol
14:31:13 [0:0] symbol : method isEmpty()
14:31:13 [0:0] location: class java.lang.String
14:31:13 [0:0] if(outputParameterText.isEmpty() == false){
14:31:13 [0:0] ^
14:31:13 [0:0] 2 errors
14:31:13 Compilation complete - 11 error(s) found
14:31:13 End Compiling 1 object(s)


This time, the compiler was complaing about the the java.nio.charset.Charset.. It seemed it had no method named forName..

Checked the java package using the following;

select *
from dba_objects
where object_type like '%JAVA%'
and owner = 'SYS'
and object_name like '%java/nio%'

It was there.. Oracle Database 11.2.0.3 had java.nio.charset, so what was the problem?

The problem should be the method "forName" .. I guessed that it was missing because I knew that this method comes with JDK 1.6.. So the jdk in this release may be below 1.6..


According to Oracle Support JDKs or lets say JVMs in Oracle Database were as follows;

Executing the same java stored procedure on Oracle 10.1, 10.2, 11.1, 11.2 and 12.1 databases will show following results for JVM version:

Oracle 10.1 runs java.vm.version=1.4.1
Oracle 10.2 runs java.vm.version=1.4.2
Oracle 11.1 runs java.vm.version=1.5.0_01
Oracle 11.2 runs java.vm.version=1.6.0_43
Oracle 12.1 runs java.vm.version=1.6.0_43 or higher

But , I had doubts about this info, as it might be referring to Oracle Database 11.2.0.4 when saying Oracle 11.2 runs java.vm.version=1.6.0_43 

Then I checked the JDK version of Oracle Database using the following support doc..

How To Determine The JDK Version Used by the Oracle JVM in the Database (Doc ID 131872.1)

The result was as I expected;

The JDK in 11.2.0.4 Oracle Database was 1.6, but the JDK in 11.2.0.3 Oracle Database was 1.5...

So this error was normal ..

The idea to upgrade JDK, which resided in Oracle Database 1.5 to 1.6 seemed utopic, and it was also not supported.

Thus, I had to find another solution in place. 

In this manner; I suggested to change a line in the problematic java source script as follows;
I recommended to use UTF8 directly..  Sourcing a script would do the job..


java.lang
Class String
getBytespublic byte[] getBytes( String charsetName) throws UnsupportedEncodingException
Encodes this String into a sequence of bytes using the named charset, storing the result into a new byte array.

The behavior of this method when this string cannot be encoded in the given charset is unspecified. The CharsetEncoder class should be used when more control over the encoding process is required.

Parameters:charsetName - the name of a supported charsetReturns:The resultant byte arrayThrows:UnsupportedEncodingException - If the named charset is not supportedSince:JDK1.1

Modify this line;
....................ByteArrayInputStream(xml.getBytesCharset.forName("UTF-8")));

To be like the following;

.....................................ByteArrayInputStream(xml.getBytes("UTF8")));


This was the needed action to compile this Java Source with a 1.5 JDK, and this action solved the remaining compilation problems.. It saved the day :)


In this incident; I realized that again..I realized that being a Senior/Principle Oracle Dba/Apps Dba consultant, requires we to have a good developer perspective, too..
No need to say that; using Oracle Support efficiently is a must for being successful.
One last thing about the dependency in Oracle Database; upgrading the Jdk seems unsupported.
Alternatively, you can use OS tier to develop your Java code if it satisfies your needs.. You can have several Jdks in Os tier and you can upgrade them if needed..
One other alternative is to use PLSQL for web service operations, like mentioned in the following doc:
Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service (Doc ID 841183.1) .. UTL_DBWS will make you use java indirectly :)

Okay that 's all for now.. Hope you 'll find this useful.

No comments :

Post a Comment

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.