Connect Oracle 10g express edition with Weka

#oracle #weka

Requirements :

You need to download and install Oracle 10g express edition in your system. Download the Oracle 10g express edition from here
You need to download and install the Weka tool in your system. Download the weka tool from here

Procedure :

In order to connect your Database(Oracle 10gXE) with the weka tool you need to have the DatabaseUtils.props file. Generally, this file should be placed inside the props folder in your WEKA_HOME(which is mostly C:/Users/%YourUsername%/wekafiles/). DatabaseUtils.props file contains the configuration that is needed by weka tool to use JDBC driver to connect the database.

WEKA_HOME path in your system can be known by opening the weka tool and then clicking on the help menu and then selecting the Systeminfo option.
This will open a new window where you can find key and value table. Then note down your WEKA_HOME path value.

Now create a file named DatabaseUtils.props ,here .props is extension. And copy below text and paste it in the DatabaseUtils.props file. Then save the file.


# Database settings for Oracle 10g Express Edition
#
# General information on database access can be found here:
# http://weka.wikispaces.com/Databases
#
# url:     http://www.oracle.com/
# jdbc:    http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/
# author:  Fracpete (fracpete at waikato dot ac dot nz)
# version: $Revision$

# JDBC driver (comma-separated list)
jdbcDriver=oracle.jdbc.driver.OracleDriver

# database URL
jdbcURL=jdbc:oracle:thin:@server_name:1521:XE

# specific data types
# string, getString() = 0;    --> nominal
# boolean, getBoolean() = 1;  --> nominal
# double, getDouble() = 2;    --> numeric
# byte, getByte() = 3;        --> numeric
# short, getByte()= 4;        --> numeric
# int, getInteger() = 5;      --> numeric
# long, getLong() = 6;        --> numeric
# float, getFloat() = 7;      --> numeric
# date, getDate() = 8;        --> date
# text, getString() = 9;      --> string
# time, getTime() = 10;       --> date

VARCHAR2=0
NUMBER=2
DOUBLE_PRECISION=2
TIMESTAMP=8

# other options
CREATE_INT=INTEGER
CREATE_STRING=VARCHAR2(4000)
CREATE_DOUBLE=NUMBER
CREATE_DATE=TIMESTAMP
DateFormat=yyyy-MM-dd HH:mm:ss
checkUpperCaseNames=true
checkForTable=true

# All the reserved keywords for this database
# Based on the keywords listed at the following URL (2009-04-13):
# http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_keywd.htm
Keywords=\
  ACCESS,\
  ADD,\
  ALL,\
  ALTER,\
  AND,\
  ANY,\
  AS,\
  ASC,\
  AUDIT,\
  BETWEEN,\
  BY,\
  CHAR,\
  CHECK,\
  CLUSTER,\
  COLUMN,\
  COMMENT,\
  COMPRESS,\
  CONNECT,\
  CREATE,\
  CURRENT,\
  DATE,\
  DECIMAL,\
  DEFAULT,\
  DELETE,\
  DESC,\
  DISTINCT,\
  DROP,\
  ELSE,\
  EXCLUSIVE,\
  EXISTS,\
  FILE,\
  FLOAT,\
  FOR,\
  FROM,\
  GRANT,\
  GROUP,\
  HAVING,\
  IDENTIFIED,\
  IMMEDIATE,\
  IN,\
  INCREMENT,\
  INDEX,\
  INITIAL,\
  INSERT,\
  INTEGER,\
  INTERSECT,\
  INTO,\
  IS,\
  LEVEL,\
  LIKE,\
  LOCK,\
  LONG,\
  MAXEXTENTS,\
  MINUS,\
  MLSLABEL,\
  MODE,\
  MODIFY,\
  NOAUDIT,\
  NOCOMPRESS,\
  NOT,\
  NOWAIT,\
  NULL,\
  NUMBER,\
  OF,\
  OFFLINE,\
  ON,\
  ONLINE,\
  OPTION,\
  OR,\
  ORDER,\
  PCTFREE,\
  PRIOR,\
  PRIVILEGES,\
  PUBLIC,\
  RAW,\
  RENAME,\
  RESOURCE,\
  REVOKE,\
  ROW,\
  ROWID,\
  ROWNUM,\
  ROWS,\
  SELECT,\
  SESSION,\
  SET,\
  SHARE,\
  SIZE,\
  SMALLINT,\
  START,\
  SUCCESSFUL,\
  SYNONYM,\
  SYSDATE,\
  TABLE,\
  THEN,\
  TO,\
  TRIGGER,\
  UID,\
  UNION,\
  UNIQUE,\
  UPDATE,\
  USER,\
  VALIDATE,\
  VALUES,\
  VARCHAR,\
  VARCHAR2,\
  VIEW,\
  WHENEVER,\
  WHERE,\
  WITH

# The character to append to attribute names to avoid exceptions due to
# clashes between keywords and attribute names
KeywordsMaskChar=_

#flags for loading and saving instances using DatabaseLoader/Saver
nominalToStringLimit=50
idColumn=auto_generated_id

Now place this DatabaseUtils.props file in C:/Users/%profile%/wekafiles/props/.

Next, you need to find your host name[of your pc]. To find that...
Open tnsnames.ora file with notepad which is located in the directory where oracle is installed. Typically tnsnames.ora is located in %Oracle_10g%/app/oracle/product/10.2.0/server/NETWORK/ADMIN/tnsnames.ora

In the above image, my host value is DESKTOP-60BT53C. Note down the value of the host.
Then place the value of host in place of server_name in DatabaseUtils.props file as shown in the image below.

Next, you need to add the JDBC Driver to the classpath. To do this in a simple way we are going to download a package in the weka tool.
Open the weka tool and click on Tools menu and select Package manager.
A new window opens where you can search for the package JDBCDriversDummyPackage and install it.
After instaling, you will get a screen like one below.

Now open the directory where Oracle is installed. And there will be a .jar file in this path %Oracle_10g%/app/oracle/product/10.2.0/server/jdbc/lib/ojdbc14.jar.
Now copy the file ojdbc14.jar from this path. And paste the ojdbc14.jar in the JDBCDriversDummyPackage folder in %Wekahome/packages/ %C:/Users/%profile%/wekafiles/packages/JDBCDriversDummyPackage/ojdbc14.jar

Now open weka tool and you can use the jdbc_URL and your username and password which you use in RunSQLCommandLine of Oracle_10g.