ORACLE database supports stored java procedure. This article shows you how to load and use java stored procedure briefly.

* Using stored java procedures

1. Make and compile your own java class.
   If you want make a package/procedure/function, you have to make all methods as static. Instance method is supported only if you use OOP(i.e, CREATE TYPE command in Oracle) features.

2. Load your java classes.

   
   C:\j2sdk1.4.2_04\bin\jar -cf compression.jar 
   .\kr\ac\dke\protein\compression\SequenceCompressor.java 
   .\kr\ac\dke\protein\compression\CodingException.java 
   .\kr\ac\dke\protein\compression\ByteUtility.java
   C:\oracle\ora81\bin\loadjava -user protein/****@yourhost 
   -force -resolve compression.jar
   pause
   

   If ORA-29547 errors occur, you have to install JVM related packages. To do this,

   
   $cd $ORACLE_HOME/javavm/install
   sqlplus ID/PASSWORD as sysdba;
   SQL>@initjvm
   SQL>@initdbj
   

3. Modify parameters.
   shared_pool_size = 50000000
   java_pool_size = 20000000

   Make sure you update the init[SID].ora

4. Make your packages/procedures/functions.

   
   CREATE OR REPLACE FUNCTION ENCODE_SEQUENCE(SEQUENCE VARCHAR2) RETURN VARCHAR2
   AS LANGUAGE JAVA
   NAME 'kr.ac.dke.protein.compression.SequenceCompressor.encode(java.lang.String) 
   return java.lang.String';
   /
   
   CREATE OR REPLACE FUNCTION DECODE_SEQUENCE(SEQUENCE VARCHAR2) RETURN VARCHAR2
   AS LANGUAGE JAVA
   NAME 'kr.ac.dke.protein.compression.SequenceCompressor.decode(java.lang.String) 
   return java.lang.String';
   /
   

   Note that I have used ‘java.lang.String’ instead of ‘String.’ In oracle stored procedure, ‘java.lang’ is not impicitly imported. Use fully qualified name.

5. Done.
   Test your stored procedures.

   
   SQL> select decode_sequence(encode_sequence('EH_')) from dual;
   
   
   DECODE_SEQUENCE(ENCODE_SEQUENCE('EH_'))
   ----------------------------------------
   EH_
   
   SQL> select encode_sequence('E') from dual;
   
   
   ENCODE_SEQUENCE('E')
   ----------------------------------------
   C
   
   SQL>
   

* Further information
Oracle 8.1.7 documentation