# Westhawk SNMP Stack in Oracle™ 9i.

This note explains and gives examples how to use the Westhawk SNMP stack to send SNMPv2 traps in an Oracle environment. Due to the unusual threading model (actually threads don't work at all :-) a dedicated set of classes has to be used to avoid starting threads that are difficult to kill in JVM.

The stack has been amended to send (i.e. not receive!) traps in an Oracle environment for SNMP v1 and v2c. It will probably not be possible to do more in Oracle.

Running the stack in Oracle requires sufficient knowledge of Oracle, Java, SNMP and our stack.

## Environment

Oracle runs JDK 1.2.X, so source files have to be compiled with a compatible version. The Ant targets can be found in `oracle.xml` in the root directory of the stack.

The example is tested in the following environment:

- Oracle9i Enterprise Edition Release 9.2.0.4.0
- Linux RedHat 8.0
- Ant 1.5.4

We run the trap example using XSQL using

- Tomcat Version 4.1.27
- XSQL 9.2.0.6 (part of Oracle 9i)

However, the later are not necessary for the traps to work!

## The Oscar Example

The directory `uk/co/westhawk/nothread/oscar` contains the Oscar example. This example is a proof-of-principle example that has nothing to do with the stack. The Oscar class has one static String method

    public static String quote()
    {
        return "I can resist everything except temptation.";
    }

The example creates a stored function in PL/SQL with the call spec for the method and then calls the example using SQL. The example uses the `scott` schema. This schema is used in most of Oracle's examples and is usually created during Oracle's installation time.

- `Oscar.java` - The java source file
- `ant loadOscar` - The Ant target that loads the class file, using the Oracle tool `loadjava`, and creates the stored procedure;
  - `checkLoadResult.sql` - The SQL file that checks the status after loading the class in Oracle
  - `createStoredProc.sql` - The SQL file that creates the stored procedure (function)
  - `checkStoredProc.sql` - The SQL file that checks the stored procedure
- `ant runOscar` - The Ant target that calls the stored procedure;
  - `callStoredProc.sql` - The SQL file that calls the stored procedure

## The Trap Example

The directory `uk/co/westhawk/nothread/trap` contains the Trap example. The idea is that every time a row is inserted in the (newly defined) `scott.trap` table, a SNMPv2 trap is sent, using the `SendTrap.doSendTrap()` java method. The fields in the table represent the trap parameters.

To use the stack for sending traps in SNMPv2 without creating threads, use the classes

- `PassiveTrapPduv2` instead of `OneTrapPduv2`. The send operation will send the trap in the callers thread
- `PassiveSnmpContextv2c` instead of `SnmpContextv2c`

Similar classes are available for SNMPv1.

All the necessary files are jarred in a cut-down version of the stack; `snmpOracle<no>.jar`, where `no` is the stack version number, like `4_14`.

The example uses the `scott` schema. This schema is used in most of Oracle's examples and is usually created during Oracle's installation time.

- `SendTrap.java` - The java source file
- `ant loadTrap` - The Ant target that loads the jar file, using the Oracle tool `loadjava`, creates the `scott.trap` table and creates the stored procedure;
  - `grantPermission.sql` - Grants the necessary socket permissions to Oracle user scott
  - `checkLoadResult.sql` - Checks if all the classes in the jar file have loaded and are resolved successfully
  - `createTrapTable.sql` - Creates the Oracle package with the call spec, the `scott.trap` table and the database insert trigger that will call the function to send the trap
  - `checkStoredFunc.sql` - Checks the stored procedure

Resolving the jar file (see `loadTrap`) resulted in a lot of complains about running out of shared pool memory, so the shared pool size was enlarged.

Running the example can be done in two ways; via the Ant `runTrap` target or via `XSQL.` The later is much more fun, but requires Oracle's XSQL to work.

- `ant runTrap` - The Ant target that runs the example
  - `insertRow.sql` - Inserts a row in the trap table. The trigger will cause the sending of the trap
- `xsql/` - The directory with `HTML,` `XSQL` and `XSL` files;
  - `insert.html` - Offers a `HTML` form to enter the values of each field in the `scott.trap` table
  - `table-post.xsql` - The action in the form above. It will insert the data into the table and returns the result by querying the last insert
  - `table-transform-post.xsl` - The stylesheet used to transform the data before being inserted into the table
  - `table-post.xsl` - The stylesheet used to transform the result

$Revision: 1.3 $, $Date: 2005/03/02 14:12:24 $

Post to the mailing list or contact the snmp group at Westhawk Ltd