Import/Export - Yak Shaving Experience

Quick:

1. You cant export data from Synonym view [Exports only sql desc of Synonym]
2. You cant create table using 'create table as' [It throws column name zero errpr
3. Collect view's desc of Synonym view
4. Export Synonym view data into csv file (SQLDeveloper is handy)
5. Create the table in destination DB using modified desc (from step 2)
6. Import csv file using SQLDeveloper - Created table -> right click -> Import (has csv option by default in SQLDeveloper 4.0.3.16)

TL;DR:

Stage 0: Intro

As developer environment isolated from other environments, some of live feed tables were copied to local database (Oracle 11g) as table, instead of accessing them using Synonym (view) -> DB Links. 

Almost 100K records needed to be copied to the dev DB. 

Stage 1: Create the table using 'create table as'

  Ref: http://dba.stackexchange.com/questions/60175/export-synonyms-content-to-tables

 Create table as failed with  'zero-length columns are not allowed'. 
 create table HUGE_TABLE NOLOGGING PARALLEL 4 as select * from syn_table@dblink;

Stage 2: Describe table

 Using Desc of synonym view shows only the synonym info. After trying many options, 'DESC syn_table' gave table/view description only where the real view/table exists. 

 Searched 'dba_objects' [Alternative: all_objects, usr_objects] to verify view existence. Describe table info needed some modification to create the table!

 So table is created.

Stage 3: Export data - insert option

 The SQLDeveloper has an option to export the 'Query Result' window [Right click to see the options]. First trial, I used the Format option as 'Insert', which creates the SQL file with insert queries. This failed in Mac Os (10.10.2) with error 'File ** was not opened because it exceeds the maximum automatic open size'. So, I had to go pagination

Stage 4: Pagination with ROWNUM

Ref:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

 Important: The following query wont return any results. Worth reading the article!! 

      select *   from t  where ROWNUM > 1; 

 Finally, after careful document reading, I found a query which could help with pagination.

select * from (select rec.*, rownum rnum from (select * from items order by items_id asc) rec where rownum <= 10) where rnum >= 5;

Stage 5: Text file limitation

 When I tried to export 10K per file, I realized that only 469 rows can be exported with record size for the given table. It is because, every insert line includes column names too.

Stage 6: CSV file

  Simple common sense thought told me that removing columns names may double the number of rows can be accommodated in single export. While exploring the format options, such 'loader', 'csv', only option works smoothly was 'csv'. Tried with 1000 records exported to csv. 

Stage 7: Import CSV

  In SQLDeveloper, tables list, right clicking on table gives import option and it accepts csv file. And, 1000 records worked smooth.

Stage 8: Working option

 Tried to export all 100K records into single csv file and it went smooth; Above all, SQLDeveloper has nice and informative tool to import csv file. It showed the import progress in Message log. 

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