This document describes the Simple Bulk Loader
Application that demonstrates loading of a directory of XML files into Oracle9i XML DB using JDBC.
Today, XML has become a de facto standard
for communication on the Internet and between applications. It is also
used for storing data in an application independent way. Lot of users
have requirement for loading directory of XML files that are generated
or maintained in a single directory into the database. Also the size
of the XML files that needs to be loaded may vary from small one to
large ones (typically the ones with size of more than 4K).
Generally, when a large XML document (typically of size greater than 4K) is
inserted into an XMLType column using a String object in JDBC, the run-time
error, "java.sql.SQLException: Data size bigger than max size for this
type" is encountered. This application provides the solution for this
problem by using a CLOB object to hold the content of large XML documents.
The important thing here is that it shows how using a single Temporary CLOB
multiple XML files can be loaded into the Oracle9i XML DB. The destination in the database could be an XMLType
table or an XMLType column either schema or non-schema based. For demonstration
purposes we have considered a non-schema based XMLType table.
Technical Overview
This application is written using a set of Java classes. There is a
main class (SimpleBulkLoader class) and a set of support classes that provide for
the tracing of the log information into a file and on the system console.
The database connection parameters are provided through an XML file,
connection.xml. This application provides
support for connecting to the database through both OCI and thin JDBC
drivers. The location of the directory containing the XML files that
needs to be loaded into the Oracle9i XML DB is also specified in this
connection.xml file. The application
also provides provision for tracing the application's execution log into
the system console or the same into a log file.
The following figure depicts the flow of this
Simple Bulk Loader application:
Refer below for step-by-step instructions on extracting
files, installing and configuring the systems to successfully run this application.
Extracting the Source Code
The application files are stored in an archive named
SimpleBulkLoader.jar. Download the archive
and extract it to a convenient directory. Execute the following command
to extract the files:
jar xvf SimpleBulkLoader.jar
Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin
is present in your system path.
(JDK_HOME is the root directory of the JDKx.x installation).
The archive SimpleBulkLoader.jar contains the
files that implement the application. All the files are extracted into the
SimpleBulkLoader directory.
Details of the individual files extracted can be viewed in the Sample files
description section.
Configuring the Application
Following steps need to be completed before compiling
the Java source code
1. A non schema based XMLType table
needs to exist in the schema for successful running of this application. Execute
the following statement in SQL*Plus to create an XMLType table named
purchaseOrder
CREATE TABLE PurchaseOrder OF XMLType;
Note: We have considered a non-schema based XMLType table for demonstration purpose. This application will work for XMLType tables as well as XMLType columns either schema or non-schema based.
2. Edit the connection.xml
file extracted under SimpleBulkLoader\config
directory. The parameters in the connection.xml
should be modified so as to reflect the following :
a. Your database schema details. The following element
tag values need to be modified to reflect the connecting database schema details:
| <Driver>THIN</Driver> |
You can specify OCI or THIN
as the text value for the Driver.
For running the application using OCI driver you need some additional
configuration to be done. Please click here for details
on the OCI related configuration. |
| <Hostname>incq223b.idc.oracle.com</Hostname> |
Hostname where the database is running. |
| <Port>1521</Port> |
Port where the database is running. |
| <ServiceName>ora9201S</ServiceName> |
Database Service Name. |
| <SID>ora9201S</SID> |
Database SID. |
| <ServerMode>DEDICATED</ServerMode>
|
Database Server Mode. |
| <Schema>SCOTT</Schema> |
Database Schema Name. |
| <Password>TIGER</Password>
|
Database Schema Password. |
| <Table>PurchaseOrder</Table> |
XMLType database table where content of the XML files need to be inserted.
For example, here the XMLType table where the XML files content will
be inserted is PurchaseOrder.
|
b. The details of the source directory from which the XML
files are to be loaded into the database. The following element tag value
needs to be modified to specify the source directory location:
| <sourceDirectory>c:\temp\sampleXMLFiles</sourceDirectory> |
The source directory that contains the XML files to be loaded into
the XMLType table specified above.
In this application we have provided some sample XML files that can
be loaded into the XMLType table. These files are provided in the SimpleBulkLoader\sampleXMLFiles
directory and its sub-directories. The source directory path
can point to SimpleBulkLoader\sampleXMLFiles directory
location, if you wish to insert these sample XML files.
Please mention the directory path in accordance with the operating
system.
|
c. The logging mode for the application log that will be
generated during the execution of the program. This application provides for
logging the trace output onto the system console or write the same into a
log file. For example, the following element tag value needs to be modified
to specify the logging mode:
| <Logger>CONSOLE</Logger> |
Specify element's value as CONSOLE if you wish to trace the
log onto the System console.
Specify element's value as FILE if you wish the log to be written
into a trace file.
|
3. Edit the BaseApplication.java
file extracted under SimpleBulkLoader\src\oracle\otnsamples\xmldb\simplebulkloader\common\baseApp
directory. Modify the DEFAULT_CONNECTION_SETTINGS
static variable value so that it points to the location where connection.xml has been extracted. For doing this, replace
c:\\temp\\connection.xml path in the following
line with the directory path where connection.xml has been extracted. Please specify the
directory path in accordance with the OS.
public static final String DEFAULT_CONNECTION_SETTINGS
= "c:\\temp\\connection.xml";
4. JDBC OCI Configuration
For running the application with the OCI driver, the system should have the
Oracle database or Oracle database client installed. Also,
ensure that the following environment variables are set:
On Windows, ensure the following:
i) ORACLE_HOME\lib directory is in the PATH
ii) ORACLE_HOME\bin directory
On Solaris and Linux, (in Korne shell) :
Set LD_LIBRARY_PATH as follows:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
After completing the configuration steps,
the java sources can be compiled and run using Oracle9i JDeveloper or
JDK 1.3
Compiling and Running the
Application using Oracle9i JDeveloper
Note: For proper execution of the application
with the OCI JDBC Driver in Oracle9i JDeveloper on Solaris and Linux, ensure that
the Oracle9i JDeveloper is run from
the same console (Korn Shell console) where the LD_LIBRARY_PATH
has been set.
Compiling the Sources using Oracle9i JDeveloper
1. In Oracle9i
JDeveloper, open the SimpleBulkLoader.jws
workspace that is extracted into the SimpleBulkLoader
directory.
2. Expand SimpleBulkLoader.jws
node by clicking it. Expand the SimpleBulkLoader.jpr
node by clicking it. Now all the source Java files should be visible
.
3. Right click SimpleBulkLoader.jpr
and select Project Settings... from pop up menu
4. Expand Configurations => Development node
from left hand panel. Click on Libraries node.
5. Click on New button in this dialog box. New
library dialog pops up.
6. Enter library name as "OracleDriver"
and click on Edit button for CLASSPATH
entry.
7. In the Edit Class Path window click Add
Entry button. Select classes12.zip
from ORACLE_HOME\jdbc\lib.
(ORACLE_HOME - is the home directory
of your Oracle9i database installation)
8. Click OK on the create new library window.
On doing so, OracleDriver library will get included in the current
project.
9. Now from the the available libraries box in the left hand panel
click on Oracle XML parser v2 and click on single right handed
arrow in the middle. This will add the library in the current project.
After doing this, click OK on the Project Settings dialog and return
to the Navigator.
10. Compile all the Java files by right
clicking the SimpleBulkLoader.jpr and
selecting Rebuild SimpleBulkLoader.jpr option. This step should
get completed without any errors. The class files will be generated
inside the SimpleBulkLoader\src directory under
the relevant package structure.
Running the Application using Oracle9i JDeveloper
1. Select the SimpleBulkLoader.java
file by clicking on it in the SimpleBulkLoader.jpr
project.
2. Right click the SimpleBulkLoader.java
and select Run SimpleBulkLoader.java option to execute the application.
If the trace logging mode was specified
as "CONSOLE" then the Message Log
window of the Oracle9i JDeveloper can be viewed for the application execution details.
Details like the database connection and XML files that are loaded into
the database will be displayed in the Message Log window.
If the trace logging mode was specified as "FILE" then the trace output
file will be generated in the SimpleBulkLoader\src directory. This trace file can be
viewed for the application execution details. Please check for the successful
completion of the program from the trace log generated.
Compiling and Running
the Application using JDK 1.3
Compiling the Java Sources using JDK 1.3
Note: This application has been
certified for Korn Shell in Solaris and Linux. All the instructions
for Solaris and Linux pertains to the Korn shell in it. You can enter
into Korn Shell in Solaris and Linux by typing "ksh" on the command
prompt.
1. Set the JAVA_HOME
environment variable.
Example for Windows - set JAVA_HOME =
<JAVA_HOME>
Example for Solaris and Linux - export JAVA_HOME = /usr/java1.3
2. Set the PATH environment
variable
Example for Windows - set PATH=%JAVA_HOME%\bin;
Example for Solaris and Linux - export PATH=$JAVA_HOME/bin;
Set the CLASSPATH
environment variable. ( As per step No.3 below)
3. Make sure that CLASSPATH
visits following jars. These files are part of the Oracle9i database installation.
=> ORACLE_HOME\lib\xmlparserv2.jar
=> ORACLE_HOME\jdbc\lib\classes12.zip
=> And the current directory
where ORACLE_HOME
- is the home directory of your Oracle9i database installation
Example for Windows -
set CLASSPATH=%CLASSPATH%;.;%ORACLE_HOME%\lib\xmlparserv2.jar;%ORACLE_HOME%\jdbc\lib\classes12.zip
Example for Solaris and Linux -
export CLASSPATH=$CLASSPATH:.:$ORACLE_HOME/lib/xmlparserv2.jar:$ORACLE_HOME/jdbc/lib/classes12.zip
4. Go to the SimpleBulkLoader\src
directory and compile all the Java sources using the following commands:
For Windows:
javac -d . oracle\otnsamples\xmldb\simplebulkloader\common\trace\*.java
javac -d . oracle\otnsamples\xmldb\simplebulkloader\common\baseApp\BaseApplication.java
javac -d . oracle\otnsamples\xmldb\simplebulkloader\examples\SimpleBulkLoader.java
For Solaris and Linux:
javac -d . oracle/otnsamples/xmldb/simplebulkloader/common/trace/*.java
javac -d . oracle/otnsamples/xmldb/simplebulkloader/common/baseApp/BaseApplication.java
javac -d . oracle/otnsamples/xmldb/simplebulkloader/examples/SimpleBulkLoader.java
5. This should successfully compile all the Java
sources. The class files will be generated inside the SimpleBulkLoader\src directory under the
relevant package structure.
Running the Application using JDK 1.3
The application can be executed by running
the SimpleBulkLoader.class file using the following command from the SimpleBulkLoader\src directory
java oracle.otnsamples.xmldb.simplebulkloader.examples.SimpleBulkLoader
On executing this command, the database
connection information and the XML files upload information will
be displayed on the system console if the trace logging mode was specified as "CONSOLE".
If the trace logging mode was specified
as "FILE" then the trace output file will be generated in SimpleBulkLoader\src directory. This trace file can
be viewed for the application execution details. Please check for
the successful completion of the program from the trace log generated.
