javaMeetsSqlldr.html
05-15-2001 Bikle
--------------------

A Demo of Mixing Java With SQL Loader

This page describes my efforts to load data from "Ukollectors" into an
Oracle table.  The data is supplied to me in the form of flat files
which I call a Ukollector log.  A typical Ukollector log contains many
lines (I call them logentries) each of which look like this:

bikle-46.bikletv.net 99.109.71.31 RXSBasic [25/Dec/2000:13:48:54 +0000] "GET /cgi-bin/2.0/getlocaldial2.pl?zone=650326&zipcode=94301&country=US HTTP/1.1" 200 741 1 "bikle/111300210" "JJ-HS2000D0RA52045"

Notice how a Ukollector log looks a little bit like an Apache web log.

While reading this demo, bear in mind Perl is a language better suited
for massaging data for SQL Loader than Java.  Once I figure out how to
base an SQL function on Perl, I'll write up a another demo which is
similar to this one.  

Also keep in mind that placing functions in a SQL Loader .ctl file
will slow loading performance.  If performance is your goal, it's best
to keep the .ctl file simple and place the bulk of text manipulation
in the script (a Perl script probably) which creates the .dat file.

With the above disclaimer out of the way, notice that the above
logentry may be divided into these sub-strings:

server
ipadd
RXScode
timestamp
action
vpath
httpVersion
result code 
bytes
mystery code (usually=1)
softwareVersion
Ukollector

A corresponding CREATE TABLE sql command is displayed below:

crTableUNITLOG.txt

After I run the above statement, I begin work on a sqlloader control
file.  It's an easy file to create so I just display it below without
much discussion.

unitlogCtl.txt

In the above sqlloader control file, I use a number of functions to
manipulate the data as it is being read from the flat file.

The functions depend upon a number of things each of which is
displayed in the order in which they must be created:

Unitlog.java

I load the above .java file into the database with the simple command line:

loadjava -u scott/tiger Unitlog.java

The above line will not overwrite an existing Java object.  Instead it will issue 
an error:

Error while creating Unitlog
    ORA-29533: attempt to overwrite class or resource Unitlog while
    defining or compiling SCOTT.Unitlog

If I see the above error I run two command lines:

dropjava -u scott/tiger Unitlog
loadjava -u scott/tiger Unitlog.java

The above commands work great on Linux or UNIX.  Notice the last argument
to dropjava does not have a .java suffix.

On Win2k I had to work around a silly connection problem:

$ loadjava -u scott/s Unitlog.java
SQL Error while connecting with oci8 driver to default database:
oracle.aurora.server.tools.loadjava.ToolsException: The JDBC OCI8
Driver is not installed properly, use -thin to try the JDBC Thin
driver: do_open
could not open connection
loadjava: 2 errors

This worked for me:
loadjava -u scott/s@localhost:1521:DANB -thin Unitlog.java

After I got loadjava to work, I ran the script below to create what are sometimes
referred to as "Java wrapper functions".  These functions act as a connection
between SQL and the newly created Java class.
 
crUNITLOGPKG.sql

After I ran the above script, I tested the resulting functions:

tstUNITLOGPKG.sql

Once I verify the above functions are working I write a simple function to 
convert the weblog date string into an Oracle date data type:

crWEBDATE2DATEfnc.sql

Notice that I use the above function in this file:

unitlogCtl.txt

Now that I have all the functions created within the database, I'm in
a position to test my sqlloader control file.  A screen dump is
displayed below which also shows all the steps required before
running sqlloader.

tst-unitlogCtl-screendump.txt

The above screen dump wraps up this discussion about loading Ukollector logs.

The main ideas presented here are listed below:

-Use of functions in sql loader control file
-Creation of Java methods inside a Java class
-Loading of a Java class into the database
-Creation of PL/SQL wrapper functions
-Use of PL/SQL wrapper functions to access Java methods from SQL
-Derivation of an Oracle date from a weblog date string