English

How To: Execute an Oracle Java Stored Procedure using the ArcSDE 8.3 Java API

Summary

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

Procedure

The following steps demonstrate how to execute an ArcSDE 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 file, jsde83_sdk.jar, into Oracle. Then load and resolve the example program.

    Code:
    D:\>loadjava –u world/world jsde83_sdk.jar

    D:\>loadjava –v –resolve –u world/world SdeExample.class
    initialization complete
    loading : SdeExample
    creating : SdeExample
    resolver :
    resolving: SdeExample

  2. Create the Oracle Java Stored Procedure definition.

    Code:
    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

    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:
    D:\>sqlpus 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

Related Information