Writing Java Stored Procedures by hcj


									Writing Java Stored Procedures
You can write a Java stored procedure to execute in the server, exactly as a PL/SQL stored procedure. Java stored procedures can be invoked directly with products like SQL*Plus, or indirectly with a trigger. Java stored procedures can also be accessed from any Oracle Net client--OCI, PRO*, JDBC or SQLJ.

Writing an Oracle Java Stored Procedure Step 1
Define a class in a file Hello.java, with one method hello that returns the string “hello”. Compile the java file using Sun’s java compiler to obtain the class file Hello.class.
public class Hello { public static String printHello() { return "Hello"; } } NOTE: Method printHello should be static. This is because you can publish only public static methods, with one exception; you can publish instance methods as member methods of a SQL object type.

Step 2
Use the loadjava command line utility to load the Java class into the Oracle database.
D:\>loadjava -verbose -user world/world Hello.class initialization complete loading : Hello creating : Hello

Step 3
Publish the java method with a function call specification. You publish value-returning Java methods as functions or procedures and void Java methods as procedures.
D:\>sqlplus world/world SQL> 2 3 4 create or replace function HELLO return varchar2 as language java name 'Hello.printHello() return java.lang.String'; /

Function created.

Step 4
Execute the Java Stored Procedure

SQL> SQL> Call SQL>

VARIABLE outString VARCHAR2(10); call hello() into :outString; completed. print outString;

OUTSTRING -------------------------------Hello

Writing an ArcSDE Java Stored Procedure
The java program below, SdeExample.java, attempts to connect to an ArcSDE server with the information passed through as command line arguments.
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; } }

Step 1
Load the ArcSDE java API jar file, jsde83_sdk.jar, into Oracle. Then load and resolve the example program.
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

Step 2
Create the Oracle Java Stored Procedure definition.
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.

Step 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 uses a dynamic Java ClassLoader, so the “createClassLoader” permission must be granted.
D:\>sqlplus sys/syspass SQL> grant JAVAUSERPRIV to world; Grant succeeded. SQL> call dbms_java.grant_permission('MAP','SYS:java.lang.RuntimePermission', 'createClassLoader', '' ); Call completed.

Step 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().
D:\>sqlpus world/world

SQL> SET SERVEROUTPUT ON SQL> CALL dbms_java.set_output(2000); 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.

To top