English

How To: Execute an Oracle Java Stored Procedure using the ArcSDE 9.x Java API

Summary

Instructions provided describe the steps to run an ArcSDE 9.x Java API program as an Oracle Java Stored Procedure.

Procedure

The following steps demonstrate how to execute an ArcSDE v9.0 Java API Program.

import com.esri.sde.sdk.client.*;

public class SdeExample {

public static void main(String[] args) {

try {
SdeExample sdeExample = new SdeExample();
SeConnection conn = sdeExample.connect( args[0], Integer.parseInt(args[1]), args[2] , args[3], args[4] );
sdeExample.printConnInfo(conn);
} catch( SeException e ) {
System.out.println("ERROR: " + e.getSeError().getErrDesc() );
}
}
public SeConnection connect(String server, int inst, String db, String user, String passwd) throws SeException {
System.out.println("Connecting to server " + server);
SeConnection conn = new SeConnection(server, inst, db, user, passwd);
System.out.println("Connection successful\n");
return conn;

}

public void printConnInfo(SeConnection conn) throws SeException {

System.out.println("Connection info");
System.out.println("Server Name: " + conn.getServer() );
System.out.println("Instance No: " + conn.getInstance() );
System.out.println("User Name: " + conn.getUser() );
System.out.println("Connection closed? " + conn.isClosed() );
return;
}

}



  1. Use the Oracle loadjava program to load the ArcSDE Java API jar files, concurrent.jar, jpe9x_sdk.jar, icu4j_3_2.jar (for 9.2), and jsde9x_sdk.jar into Oracle. After doing this, load and resolve the example program.

    Code:
    D:\>loadjava -resolve –u world/world concurrent.jar

    D:\>loadjava -resolve –u world/world jpe90_sdk.jar

    D:\>loadjava -resolve –u world/world jsde90_sdk.jar

    D:\>loadjava -resolve –u world/world icu4j_3_2.jar (for 9.2 only)

    D:\>loadjava –v –resolve –u world/world SdeExample.class
    arguments: '-v' '-resolve' '-u' 'world/world' 'SdeExample.class'
    creating : class SdeExample
    loading : class SdeExample
    resolving: class SdeExample

  2. Create the Oracle Java Stored Procedure definition.

    Code:
    sqlplus map/map
    SQL> create or replace procedure sdexample(SERVER VARCHAR2, INST VARCHAR2, DB VARCHAR2, USER VARCHAR2, PASSWD VARCHAR2)
    2 as language java
    3 name 'SdeExample.main(java.lang.String[])';
    4 /

    Procedure created.

  3. Since the ArcSDE Java API uses the java.net.Socket class to connect to an ArcSDE server, the user running the Java stored procedure must be granted the “JAVAUSERPRIV” privilege. Also, the Java API requires the “createClassLoader” permission to be granted.
    Code:
    D:\>sqlplus sys/syspass as sysdba

    SQL> grant JAVAUSERPRIV to world;

    Grant succeeded.

    SQL> call dbms_java.grant_permission('WORLD','SYS:java.lang.RuntimePermission', 'createClassLoader', '' );

    Call completed.

  4. The SdeExample Java program uses System.out class to print output. The default output device is a trace file, not the user screen. To redirect output to the SQL*PLUS text buffer, call the procedure set_output() in the package DBMS_JAVA().
    Code:
    SQL>connect world/world

    SQL> SET SERVEROUTPUT ON
    SQL> CALL dbms_java.set_output(500);

  5. Run the Oracle Java Stored Procedure.

    Code:
    SQL> call sdexample('gis','5151','none','sdetest','go');

    Connecting to server gis
    Connection successful
    Connection info
    Server Name: gis
    Instance No: 5151
    User Name: sdetest
    Connection closed? false

    Call completed.

    SQL>

Related Information