Java stored procedure in ORACLE

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

Similar Posts:

Comments 1

  1. 민구 wrote:

    Java stored procedure is much slower than the other type of java classes because Aurora(Oracle’s own JVM) does not exploit JIT (Just in time compilation).

    Posted 29 Jun 2004 at 3:23 am

Post a Comment

Your email is never published nor shared.