Welcome

Troves being gleaned while surfing on the Internet mostly about computer/IT/system skills and tricks, Welcome here ...
Powered By Blogger

Disclaimer

This blog is written by the owner with real practices and tests and intended to hold all original posts except there is a clear declaration for referencing from others. Thanks for tagging with the source link or other tips for reference from here if you would like to quote partial or full text from posts in this blog.

Saturday, December 11, 2010

Q&A for Oracle Distributed Database Application Development Environment Setting


1. Driver installation – oracle JDBC


Like the installation of JDBC driver for MySql, installing the JDBC driver for oracle also needs to follow the same RULE OF THUMB:

  • Extract the compressed archive downloaded (ojdbc6dms_g.jar or ojdbc6dms.jar, for example) and fetch out only the .jar file, and put it into the $JAVA_HOME/jre/lib/ext directory!! NOT $JAVA_HOME or the so-called CLASSPATH which is ordinary $JAVA_HOME/jre/lib !!

  • And, for safety you should make the permission 644 for the library (.jar) under this directory.

2. SQL Developer Installation and Connection


After Installing SQLDeveloper (linux X86_64), you may want to use it immediately starting from connecting a DB.
While trying with this following problems might be waiting for you:

a. Firstly you will most possibly come across this DMS gnat:

oracle.classloader.util.AnnotatedNoClassDefFoundError:

Missing class: oracle.dms.console.DMSConsole

Dependent class: oracle.jdbc.driver.DMSFactory
Loader: jre.extension:0.0.0
Code-Source: /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre/lib/ext/ojdbc6dms_g.jar
Configuration: system property java.ext.dirs

this is caused by lack of an essential library, It is DMS!
To fix it, you need to install this library. I just copy dms.jar under the installation directory for
JDeveloper into $CLASSPATH/ext to resolve this gnawing problem!

b. after fixing up problem a, another one is there greeting you:

oracle.classloader.util.AnnotatedNoClassDefFoundError:

Missing class: oracle.core.ojdl.MessageType

Dependent class: oracle.dms.instrument.Level
Loader: jre.extension:0.0.0
Code-Source: /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64/jre/lib/ext/dms.jar
Configuration: system property java.ext.dirs

This is another "library shortage" problem - lacking ojdl.jar. It is not really the empytiness you might found with the "core package" in the dms.jar that is the culprit, but the ojdl.jar, completely yet another libraries!

To make up this library, I simply drag it from still the JDeveloper installation directory into $CLASSPATH/ext to make the SQLDeveloper docile!

Now, the connection should be ok!

3. Generate tables and feed random data into the database


Here we make use of BenchMark-SQL-2.3.2 to do this.
However, you ought to find that this tools is not so friendly as you might expect under linux environments. So here goes problems and solutions to those problems as follows:

a. when you firstly try running the script runSQL.sh, you probably get stunned immediately by this error : err.sh: line 1: /bin/java: No such file or directory.
This is occuring just because $JAVA_HOME does not effect at all. To fix this, follow these two simple steps:
1> head lines as follows in runSQL.sh
#!/bin/bash
source ~/.bash_profile
2> convert the script, which is most likely in a DOS format while downloaded, to unix format by dos2unix

b. You might hit the wall on this error : No suitable driver found!
This is most likely due to incorrect setting for connection in the properties file that the main runSQL.sh script uses. Here is the hopeful solution:

edit the .properties file. Now since we use oracle driver and database, we had better to use oracle.properties under the "run" subdirectory.
Then change things like this:

driver=oracle.jdbc.driver.OracleDriver
#conn=jdbc:oracle:thin:@localhost:1521:XE
#user=scott
#password=tiger
conn=jdbc:oracle:thin:@dia2.cs.usm.edu:1521:dia2
user=xxxxxx
password=*************

note that the value for "conn" is not simply the host name, you should comply with the format above, including driver type, host, port and SID.


c. Following the same solution above to beat the same problems you might be confronted with loadData.sh.

Now, you should get all data randomly inserted into the database.

4. Get hands on SQLJ

Not exaggerating, doing with SQLJ is far more arduous than with JDBC, at least in the context of Oracle Database Application Development according to my real practice.
Let me show as follows selected problems you might encounter and my solutions to them at the cost of large amount of time with the Google search and reading through oracle discussion forums..

4.1 SQLJ driver installation

Actually in terms of database connection, SQLJ is not really a driver in the sense of what we refers to JDBC drivers, it is a java compiler accessory, to my knowledge and understanding towards this stuff, that is used to interpret SQLJ based code in the java program by SQLJ translator.
So in order the java application to employ SQLJ, both JDBC driver and SQLJ translator must be prepared in the development environment.
a. oracle JDBC driver for SQLJ translator

For what is mentioned above, we should make sure that JDBC driver works well with SQLJ. A simplest test could be done with the attachment test programs in the SQLJ download, found in the demo directory.

Just “javac TestInstallJDBC.java” and then “java TestInstallJDBC”, things happen out of our expectation however. JVM complains with following gnats:

Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1734)
at java.lang.Runtime.loadLibrary0(Runtime.java:823)
at java.lang.System.loadLibrary(System.java:1028)


Then, we should think of making up the missed library ocijdbc11.so, which is also there found at the “Oracle software Download” page. Place it under the path contained in the LD_LIBRARY_PATH (for example, $CLASSPATH/oci_drivers) and reexport this environment variable, and take a try again, trouble renewed like as follows if you are using AMD64 architecture with your hardware platform:
Exception in thread "main" java.lang.UnsatisfiedLinkError: /usr/java/jdk1.6.0_22/jre/lib/oci_solaris/libocijdbc11.so: /usr/java/jdk1.6.0_22/jre/lib/oci_solaris/libocijdbc11.so: wrong ELF class: ELFCLASS32 (Possible cause: architecture word width mismatch)
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1803)
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1728)
at java.lang.Runtime.loadLibrary0(Runtime.java:823)
at java.lang.System.loadLibrary(System.java:1028)

However, the download page only provides the 32bit library!
After fiddling on Google with queries containing “Oracle xxxx” and lot of time burned, I find a very devious way out at last – installing Oracle InstantClient and then fetching libraries bounded inside, which include what we need as above.

Download and install the Oracle InstantClient basics for 64bit architecture (oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm, for instance), and find the libraries missed in the directory “/usr/lib/oracle/11.2/client64/lib/” , copy following libraries into the $CLASSPATH/oci_drivers to replace the those 32bit versions :
libclntsh.so.11.1 libheteroxa11.so libnnz11.so libocci.so.11.1 libociei.so libocijdbc11.so

reexport LD_LIBRARY_PATH so that to make it effect, then just change things related to actual/existent table name in the TestInstallJDBC.java, recompile and run, you can get it work!

b. Install SQLJ translator

Download SQLJ translator from the Oracle official site, unzip it you should get at least following .jar under the lib directory:
runtime11.jar runtime12ee.jar runtime12.jar runtime.jar runtime-nonoracle.jar translator.jar
among which translator.jar and one of the runtime*.jar are definitively necessary, just copy them to the $CLASSPATH/ext directory. ALSO, these two jars should be included in the CLASSPATH itself!
However, when you directly launch the sqlj executable in the bin directory for testing or verification of the well preparedness of SQLJ, it complains after dumping help message like:
Error: This JDBC library requires JDK 1.4. Your are using JDBC library ojdbc14.jar under JDK 1.3 or earlier. Either run in a JDK 1.4 environment, or use the JDBC library classes111.jar or classes12.jar.
Warning: Code generator "oracle" cannot be instantiated from class oracle.sqlj.codegen.CodeGenFactory: oracle/xdb/XMLType.
Total 1 warning.


Probing for a while with SQLJ documentation online, it might stuck us to change to other way to run it:
./sqlj -codegen=oracle
Unfortunately, the stack dumps the loathsome as follows:
unexpected error occurred...
java.lang.NoClassDefFoundError: oracle/xdb/XMLType
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)
at java.lang.Class.getDeclaredMethods(Class.java:1791)
at sqlj.framework.JSClass$ClassWrapper.getDeclaredMethods(JSClass.java:1809)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1639)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1649)


Following the same cracking idea, we continue to find the missed things.
Firstly, the xdb library, we can find it through “www.findjar.com” and wget it from “http://mirrors.ibiblio.org/pub/mirrors/maven/mule/dependencies/maven1/oracle-xdb/jars/xdb.jar".
Also place it under $CLASSPATH/ext. Then error seems endless:



So it is not the real treatment by making up each single library one by one!
Since it appears that we need the XML holistic library or something as a whole actually, we simple go Oracle site and find it. Yes it is "xdk_linux_10_1_0_2_0_production". Download it and unpack the jar, and for safety just copy all the .jar under the lib dictory to $CLASSPATH/ext, including:
xdb.jar xmlcomp2.jar xmlcomp.jar xmldemo.jar xml.jar xmlmesg.jar xmlparserv2.jar xschema.jar xsqlserializers.jar xsu12_817.jar xsu12.jar classgen.jar


Take another try, we are still not that lucky:
unexpected error occurred...
java.lang.NoClassDefFoundError: javax/servlet/http/HttpServletRequest
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)
at java.lang.Class.getDeclaredMethods(Class.java:1791)
at sqlj.framework.JSClass$ClassWrapper.getDeclaredMethods(JSClass.java:1809)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1639)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1675)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1681)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1638)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1665)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1670)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1639)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1649)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1664)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1670)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1639)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1675)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1681)
at sqlj.framework.JSClass.resolveClassReferences(JSClass.java:1638)

Keep on! Find servlet at Oracle site and manually pack it into a single jar file, named as servlet.jar for instance, and place it under $CLASSPATH/ext.

Now JVM finally stops complaining with runtime errors like the foregoing gnats!


Until this point, your SQLJ should work fine, which could be verified by running test code bundled with the SQLJ translator downloads that lies in the demo directory.
Now enter the demo
  1. javac TestInstallCreateTable.java
  2. java TestInstallCreateTable
// now Example table SALES should be created correctly
  1. sqlj TestInstallSQLJ.sqlj
  2. java TestInstallSQLJ
And "Hello, SQLJ!" should be seen in the output then.

Supplementary Notes ( for the pitiful )

However, SQLJ does not get all right even now, I just still faced this prompt at the end of the output with "sqlj -codegen=oracle":
Error: This JDBC library requires JDK 1.4. Your are using JDBC library ojdbc14.jar under JDK 1.3 or earlier. Either run in a JDK 1.4 environment, or use the JDBC library classes111.jar or classes12.jar.

I have tried many times of reinstalling higher version of JDK (1.6.0_22), but it does not cure this problem in the end!

FINALLY, we found the problem consists in the JDBC driver – the version we use is not consistent with the JDK version and OCI driver libraries!
WAY OUT: With JDK 1.3 or lower, and ocijdbc11 (i.e. libclntsh.so.11.1 libheteroxa11.so libnnz11.so libocci.so.11.1 libociei.so libocijdbc11.so), we should use JDBC at version 5! which can be found in InstantClient package, under the lib directory. Place the ojdbc5.jar in the $CLASSPATH/ext and remove the higher version, ojdbc6dms.jar, say.

Now, SQLJ should be definitely working there well!

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)