ORACLE database supports stored java procedure. This article shows you how to load and use java stored procedure briefly.
* Using stored java procedures
- 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. - 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
- Modify parameters.
shared_pool_size = 50000000
java_pool_size = 20000000Make sure you update the init[SID].ora
- 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.
- 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
Leave a Reply