Java Embedded in OracleDB - 2

After the first posting of Java Embedded in Oracle DB, here is little advanced one with IN/OUT BLOB.

Note: 

1. Make sure java source code compiles without warnings, when you do create & compile java source

2. Sometimes you may have to run fetching query second time to avoid java class conflicts

--Java code

--drop java source OraHashMap;

create or replace and compile java source named OraHashMap as

import java.lang.Exception;

import java.io.ObjectInputStream;

import java.io.ByteArrayInputStream;

import java.util.HashMap;

import java.util.Set;

import oracle.sql.BLOB;

import java.io.OutputStream;

import java.io.ByteArrayOutputStream;

import java.io.ObjectOutputStream;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.driver.OracleDriver;

import java.lang.StringBuffer;

public class OraHashMap

{ 

  private static HashMap blobToHashMap(BLOB in_blob) throws Exception {

    int i_length = (int) in_blob.length();

    byte[] blobAsBytes = in_blob.getBytes(1, i_length);

    

    ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(blobAsBytes));

    HashMap hmap = (HashMap) ois.readObject(); 

    return hmap;

  }

  private static BLOB hashMapToBlob(HashMap hmap) throws Exception {

    ByteArrayOutputStream baos = new ByteArrayOutputStream();

    ObjectOutputStream oout = new ObjectOutputStream(baos);

    oout.writeObject(hmap);

    oout.close();

    OracleConnection conn = (OracleConnection) new OracleDriver().defaultConnection();

    BLOB out_blob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);

    OutputStream ostrm = out_blob.setBinaryStream(0);

    ostrm.write(baos.toByteArray());

    ostrm.flush();  

    return out_blob;

  }

  public static long getSize(BLOB in_blob) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    return hmap.size();

  }

  public static String getValue(BLOB in_blob, String str_key) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    return (String)hmap.get(str_key);

  }

  /*

   * Next 3 methods written in an assumption that

   * Both key and value are string

   */

  public static BLOB remove(BLOB in_blob, String key_str) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    String removed = (String) hmap.remove(key_str);

    System.out.println("Removed:\nKey: " + key_str + "\nValue: " + removed + "\n");

    BLOB out_blob = hashMapToBlob(hmap);

    return out_blob;

  }

  public static BLOB put(BLOB in_blob, String key_str, String val) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    String added = (String) hmap.put(key_str, val);

    System.out.println("Removed:\nKey: " + key_str + "\nValue: " + added + "\n");

    

    BLOB out_blob = hashMapToBlob(hmap);

    return out_blob;

  }

  

  public static String toVarchar(BLOB in_blob) throws Exception {

    HashMap hMap = blobToHashMap(in_blob);

    Set hKeys = hMap.keySet();

    StringBuffer sf = new StringBuffer();

    for(Object hKey: hKeys) {

      sf.append(hKey + ":" + hMap.get(hKey) + ",\n");

    }

    System.out.println("Java Print:\n" + sf.toString() + "\n");

    return sf.toString();

  }

};

--sql wrappers

--drop function hashmap_size;

create or replace function hashmap_size(hmap blob)

return number

as language java

name 'OraHashMap.getSize(oracle.sql.BLOB) return long';

--drop function hashmap_value;

create or replace function hashmap_value(hmap blob, var_key varchar2)

return varchar2

as language java

name 'OraHashMap.getValue(oracle.sql.BLOB, java.lang.String) return java.lang.String';

--drop function hashmap_remove;

create or replace function hashmap_remove(hmap blob, var_key varchar2)

return Blob

as language java

name 'OraHashMap.remove(oracle.sql.BLOB, java.lang.String) return oracle.sql.BLOB';

--drop function hashmap_put;

create or replace function hashmap_put(hmap blob, var_key varchar2, var_val varchar2)

return Blob

as language java

name 'OraHashMap.put(oracle.sql.BLOB, java.lang.String, java.lang.String) return oracle.sql.BLOB';

--drop function hashmap_varchar;

create or replace function hashmap_varchar(hmap blob)

return varchar2

as language java

name 'OraHashMap.toVarchar(oracle.sql.BLOB) return java.lang.String';

--Queries

select id, hashmap_size(<blob_fld>) from <table_name>; --where id = ### ;

select id, hashmap_value(<blob_fld>, '<key>') from <table_name>;

--To read the hashmap

select hashmap_varchar(<blob_fld>) from <table_name>; --where id = ###;

select id, hashmap_size(<blob_fld>) as num, hashmap_value(record, '<key>') as first_name from <table_name> 

--Following steps prints Java System.out.println

set serveroutput on;

CALL dbms_java.set_output (10);

Declare

  out_str varchar2(500);

BEGIN

select hashmap_varchar(<blob_fld>) into out_str from <table_name> where id = ###;

dbms_output.put_line(out_str);

end;

--To modify and save hashmap

--Edit commented lines to perform the tasks

set serveroutput on;

CALL dbms_java.set_output (10);

DECLARE

  record_1 BLOB;

  record_2 BLOB;

  

  record_id number := <xxx>;

  key_str_remove varchar2(32) := '<key>';

  key_str_add varchar2(32) := 'USR_TEST';

  add_value varchar2(32) := 'CHECK THIS';

BEGIN

  SELECT tn.<blob_fld> into record_1 from <table_name> tn where id = record_id;

  dbms_output.put_line(hashmap_size(record_1));

  dbms_output.put_line(hashmap_value(record_1, key_str_remove));

  record_2 := hashmap_remove(record_1, key_str_remove);

  record_2 := hashmap_put(record_1, key_str_add, add_value);

  update <table_name> set <blob_fld> = record_2 where id = record_id;

  dbms_output.put_line(hashmap_size(record_2));

  dbms_output.put_line(hashmap_value(record_2, key_str_add));

END;

Other Helps:

select utl_raw.cast_to_varchar2(<blob_fld>) from <table_name> where id = ###;

http://przemyslawkruglej.com/archive/2013/11/returning-blob-from-embedded-java/

https://docs.oracle.com/database/121/JJDEV/toc.htm

Next Level: Prepared statements inside java

Questions: 
 Can Java access the table fields directly? - Have to try with OracleConnector!
REF: http://docs.oracle.com/cd/B28359_01/java.111/b31225/chsix.htm#BABJJFJC

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!
   

Make Capybara and Selenium open a google page.

Here simple step by step to make bare minimum Capybara and Selenium to open a google page


1. Make a dirctory
      mkdir capy_open_google

2. Get into the directory and create following sub directories
       cd capy_open_google
   mkdir features
   mkdir features/support
   mkdir features/step_definitions

3. Create 'Gemfile' with following content (Check Note 1)

source "http://rubygems.org"
gem "cucumber"
gem "capybara"
gem "selenium-webdriver"
gem "rspec"

4. Create 'features/support/env.rb' file with following content

require 'capybara'
require 'capybara/dsl'
require 'capybara/cucumber'
require 'capybara/rspec'
require 'selenium/webdriver'
Capybara.default_driver = :selenium
Capybara.app = "Cuke alone"
Capybara.run_server = false

5. Create 'features/step_definitions/web_steps.rb' file with following content

     Given /^(?:|I am )on the "([^"]*)" page$/ do |url|
       visit(url)
     end

6. Create 'features/search.feature' file with following content

Feature: Sample demo
Scenario: Open google
  Given I am on the "www.google.com" page

7. In shell, execute 'bundle install'

8. then, 'bundle exec cucumber features/search.feature'

Note 1: 
 At the time of the blog post, Firefox 35.0.1 throws some driver error. It is suggested to use FF25 in Ubuntu 14.04. More info in https://code.google.com/p/selenium/issues/detail?id=6988

Note 2:
 This post is created in response to https://groups.google.com/forum/#!topic/ruby-capybara/-yZYB8i7aPs

I would suggest to check: 

https://github.com/sivajankan/standalone-cuke-selenium-download

https://github.com/thuss/standalone-cucumber

Nodejs - Express - Static Web Page Server

Ref: 

http://runnable.com/UWw3g0PKxoAWAA6K/how-to-serve-static-files-in-express-for-node-js

Assumption: npm, node, express got installed before this step!

1. Create nodejs project folder

2. Create package.json

{ "name": "express-static-files-example", "version": "0.0.1", "description": "A sample code project demonstrating serving static files using express", "dependencies": { "express": "*" }, "scripts": { "start": "node server.js" } }

3. Create server.js

var express = require('express'); var app = express(); app.use(express.static(__dirname + '/public')); app.listen(80);

4. Put all your HTML, javascript, and css files inside the public folder

npm install

npm start



Oracle OIM - Retrieve Organization info

   From Oracle Developer-Works got some code sample to collect act_key using organization name. By playing with Eclipse Debugging Mode, I realized that we can send any partial info of Organization in search Hashtable and collect complete set of Organization info. 

 In my case, I need have to collect 'act_key' from 'Organization Name'. Here is altered sample code!

Hashtable<String, Object> orgHash = new Hashtable<String, Object>(); 

orgHash.put("Organizations.Organization Name", orgName); 

long orgKey = 0;

try {

  //Connection might be established with OIM at this point

  tcOrganizationOperationsIntf orgIntf = Platform.getService(tcOrganizationOperationsIntf.class);

  tcResultSet orgSet = orgIntf.findOrganizations(orgHash);

  orgKey = orgSet.getLongValue("Organizations.Key");

} catch (Exception e) {

  e.printStackTrace();

  logger.error("Caught Exception when collecting Organization key for " + orgName + ": " + e);

}

return orgKey;

Java Debugging - Eclipse

Short Version:

Eclipse: (In Debug mode)

1. Window -> Show View -> Display

2. Enter your command

3. select the code to be run, then press CMD+Shift+D -> Shows results in the window

4. select the code to be run, then press CMD+Shift+I -> Shows results in popup - very helpful

5. System.out.println -> Shows in command

StackOverFlow Ref:

https://stackoverflow.com/questions/950129/how-to-execute-some-function-in-eclipse-while-debugging-a-java-program/24198463#24198463

If you want to run commands in Java Debugging mode, here you go!

In Eclipse: 

1. In Debug Mode, make 'Display' window visible: Window -> Show View -> Display

2. Enter your command in 'Display' console

3. Select the command and press CMD+Shift+D, which Shows results in the window

4. Select the command and press CMD+Shift+I, which Shows results in popup - very helpful

5. Ultimately, you can use System.out.println to put in console log