Tuesday, February 24, 2015

Java class embedded in OracleDB

Here is the Hello World Steps for starters

Ref: http://stackoverflow.com/questions/5899726/creating-function-in-an-oracle-database-using-java-directly


--Java code
create or replace and compile java source named first_hello as
public class first_one
{ public static String first_hello(String name) { return "Hello " + name;}};

--sql wrapper
create or replace function first_hello_sql(name varchar2)
return varchar2
as language java
name 'first_one.first_hello(java.lang.String) return java.lang.String';

--calling method
select first_hello_sql('Siva') from dual;


Read 'HashMap' BLOB 

Note: Make sure java source code compiles without warnings, when you do create & compile java source

--Java code
--drop java source hashmap_helper_source;
create or replace and compile java source named hashmap_helper_source as 
public class hashmap_helper

  public static long hashmap_size(java.sql.Blob in_blob) throws java.lang.Exception
  {
    int i_length = (int) in_blob.length();
    byte[] blobAsBytes = in_blob.getBytes(1, i_length);
    
    java.io.ObjectInputStream ois = new java.io.ObjectInputStream(new java.io.ByteArrayInputStream(blobAsBytes));
    java.util.HashMap hmap = (java.util.HashMap) ois.readObject();
    return hmap.size();
  }

  public static String hashmap_value(java.sql.Blob in_blob, String str_key) throws java.lang.Exception
  {
    int i_length = (int) in_blob.length();
    byte[] blobAsBytes = in_blob.getBytes(1, i_length);
    
    java.io.ObjectInputStream ois = new java.io.ObjectInputStream(new java.io.ByteArrayInputStream(blobAsBytes));
    java.util.HashMap hmap = (java.util.HashMap) ois.readObject();
    return (String)hmap.get(str_key);
  }
};

--sql wrappers
--drop function hashmap_size_sql;
create or replace function hashmap_size_sql(hmap blob)
return number
as language java
name 'hashmap_helper.hashmap_size(java.sql.Blob) return long';

--drop function hashmap_value_sql;
create or replace function hashmap_value_sql(hmap blob, var_key varchar2)
return varchar2
as language java
name 'hashmap_helper.hashmap_value(java.sql.Blob, java.lang.String) return java.lang.String';


--Example queries to test
select hashmap_size_sql(<blob_field>) from <your_table>;
select hashmap_value_sql(<blob_field>, '<valid_key>') from <your_table>;
select hashmap_size_sql(record) as num, hashmap_value_sql (<blob_field>'<valid_key>') as <some_name> from <your_table> 


Further Reference and Helps!
http://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm


Helpful Queries:

select dbms_java.longname(object_name), object_type, status from user_objects
where object_type = 'JAVA CLASS' and STATUS = 'INVALID';

select utl_raw.cast_to_varchar2(<blob_field>) from <your_table>;



Next Level: Modify the element inside Java and update the field 

Questions: 
 Can Java access the table fields directly? (I dont think so)
 So, IN/OUT BLOB and saving by using Procedures/Functions!
   



No comments:

Post a Comment