Creating Java Stored Procedure

Create Java Stored Procedure

 

Don’t save Java source in database

(1)    Create a java class and use javac to compile the .java file to .class file.

(2)    Use loadjava utility to load .class file to database

(3)    Create a PL/SQL wrapper procedure around the java method.

(4)    Call the PLSQL procedure as usual.

 

Example:

   

 

// Hello.java

public class Hello {

  public static String world(){

                return "Hello world";

  }

}

 

 

C:\PLSQL_JAVA\Test>javac Hello.java

 

C:\PLSQL_JAVA\Test>dir

 Volume in drive C has no label.

 Volume Serial Number is 483A-B798

 

 Directory of C:\PLSQL_JAVA\Test

 

08/09/2012  11:14 AM    <DIR>          .

08/09/2012  11:14 AM    <DIR>          ..

08/09/2012  11:14 AM               273 Hello.class

08/09/2012  11:13 AM                88 Hello.java

               2 File(s)            361 bytes

               2 Dir(s)  144,559,951,872 bytes free

 

C:\PLSQL_JAVA\Test>loadjava -user ro_gem_32_si_b13/a@1.1.1.1/orcl Hello.class

 

C:\PLSQL_JAVA\Test>sqlplus ro_gem_32_si_b13/a@1.1.1.1/orcl

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 11:15:48 2012

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create or replace function helloworld return varchar2

  2  as

  3  language java name 'Hello.world() return java.lang.String';

  4  /

 

Function created.

 

 

 

Save Java Source in database

(1)    Create a “JAVA SOURCE” object in database. (create or replace java source named xxx as….)

(2)    Compile the java source object. (alter java source xxx compile)

(3)    Create a PLSQL wrapper procedure around the java method

(4)    Call the PLSQL procedure as usual

 

 

Example:

create or replace and resolve java source named "HELLO_WORLD"

AS

public class Hello {

  public static String world(){

                return "Hello world";

  }

}

/

 

SQL> alter java source HELLO_WORLD compile;

 

 

SQL> create or replace function helloworld return varchar2

  2  as

  3  language java name 'Hello.world() return java.lang.String';

  4  /

 

Function created.

 

 

SQL> select helloworld from dual;

 

HELLOWORLD

-------------------------------------------------------------------------

Hello world

 

 

SQLJ

 

Remember to add “throws SQLException” clause when using SQLJ directive in java class.

 

SQL> create or replace and resolve java source named "HELLO_WORLD"

  2  AS

  3  public class Hello {

  4    public static String world() {

  5     String returnVal;

  6

  7     #sql {select 'Hello World' into :returnVal from dual};

  8

  9     return returnVal;

 10    }

 11  }

 12  /

 

Java created.

 

SQL> alter java source HELLO_WORLD compile;

 

Warning: Java altered with compilation errors.

 

SQL> show err

Errors for JAVA SOURCE "HELLO_WORLD":

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

0/0      HELLO_WORLD:5: unreported exception java.sql.SQLException; must

         be caught or declared to be thrown

 

0/0      1 error

0/0      ^

0/0      #sql {select 'Hello World' into :returnVal from dual};

SQL> create or replace java source named "HELLO_WORLD"

  2  AS

  3  public class Hello {

  4    public static String world() throws SQLException{

  5     String returnVal;

  6

  7     #sql {select 'Hello World' into :returnVal from dual};

  8

  9     return returnVal;

 10    }

 11  }

 12  /

 

Java created.

 

SQL> alter java source HELLO_WORLD compile;

 

Warning: Java altered with compilation errors.

 

SQL> show err

Errors for JAVA SOURCE "HELLO_WORLD":

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

0/0      HELLO_WORLD:2: cannot find symbol

0/0      symbol  : class SQLException

0/0      1 error

0/0      public static String world() throws SQLException{

0/0      ^

0/0      location: class Hello

 

SQL> create or replace java source named "HELLO_WORLD"

  2  AS

  3  import java.sql.*;

  4  public class Hello {

  5    public static String world() throws SQLException{

  6     String returnVal;

  7

  8     #sql {select 'Hello World' into :returnVal from dual};

  9

 10     return returnVal;

 11    }

 12  }

 13  /

 

Java created.

 

SQL> alter java source HELLO_WORLD compile;

 

Java altered.

 

 

 

 

Reference Doc

Oracle® Database Java Developer's Guide 10g Release 2 (10.2)

Calling Java Methods in Oracle Database

Server-Side Internal Driver