Installing Oracle ODBC driver on uBuntu 16.04 server

Note: Following steps are for ubuntu 16.04 server. Change the steps based on your Linux flavor

First start with unixodbc installation
$sudo apt-get install unixodbc
To conform the installation just type in isql from the shell and should work.

Install the alien utility (You need to install local .rpm packages)
$sudo apt-get install alien

Install libaio1 64 bit$sudo apt-get install libaio1

I have followed the steps from this post. But simplified it little bit.
https://help.ubuntu.com/community/Oracle%20Instant%20Client

You need to first download them from Oracle site.

Instant Client Home page:http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
Download page: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html (Linux 64 bit)

I went with the following packages

Instant Client Package - Basic Instant Client Package - JDBC Supplement Instant Client Package - SQL*Plus Instant Client Package - ODBC

Download the above .rpm files locally on your machine and upload it to the Linux server or directly download it on your Linux server. wget won't work since it requires you to sign in to Oracle site.

Next install the rpm's using the alien utility.

sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

Install the other rpms in the same way

Setup the Oracle environment variables globally. You can do it in your user .profile as well.

Create a file "sudo vi /etc/profile.d/oracle.sh" with the following content.

export ORACLE_HOME=/usr/lib/oracle/12.1/client64

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH}

logoff and login back and try sqlplus

user@ubuntu:~$ which sqlplus

/usr/lib/oracle/12.1/client64/bin/sqlplus

user@ubuntu:~$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 18 12:14:14 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: 

If you get the above response, you are almost done. At this stage you can connect to your Oracle database using EZConnect string

$ sqlplus scott/tiger@myoracledb.example.com:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 18 12:17:00 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 18 2017 02:22:27 -05:00

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> 

Just finish it up by setting up sqlnet.ora and tnsnames.ora

Create /network/admin directory under $ORACLE_HOME (/usr/lib/oracle/12.1/client64) folder

Under the same create sqlnet.ora. Sample below

names.directory_path = (EZCONNECT, LDAP, TNSNAMES, HOSTNAME)

Create tnsnames.ora Sample content below

MY_ORACLE_DB= (description=   (address_list=     (address = (protocol = TCP)(host = myoracledb.example.com)(port = 1521))   ) (connect_data =   (service_name=ORCL) ))

Now you can connect using $sqlplus scott/tiger@MY_ORACLE_DB

You are done and enjoy.

Configuring Oracle ORDS/Apex Listener with Oracle OID/LDAP/TNS/EZ Connect

I have been using ORDS since Apex Listener 1.0 beta, however didn't figure out how to connect it to an Oracle OID. I feel ORDS documentation should include sample configurations for each connection types. Till then here is your unofficial documentation.

Following is tested with Apex Listener 2.0.10.289.08.09. However I believe will work with ORDS 3.0 as well.


#1: Connecting to Oracle OID

<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">
jdbc:oracle:thin:@ldap://ldap.example.com:7777/ORCL,cn=OracleContext,dc=example,dc=com
</entry>
<entry key="db.password">@encrypted password</entry>

References:
ORDS Configuration: https://docs.oracle.com/cd/E56351_01/doc.30/e56293/config_file.htm#AELIG7204
LDAP Syntax http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#CHDBICFA
Use ldaps for SSL.
ldap.example.com:7777 => Is your ldap server name and port
ORCL  => Database Service name configured in OID. Replace it with the Database you want to connect
cn=OracleContext  => Keep it. I believe this is something defined at the OID level
dc=example,dc=com  => Thats your domain. If you have 3 parts in your domaninlike example.co.uk then use dc=example,dc=co,dc=uk 

#2: Connecting using TNS Names
You can do it in two ways. You can have a tnsnames.ora file with database connect strings defined in it. For that use the following (Note: connectionType=tns)
<entry key="db.connectionType">tns</entry>
<entry key="db.tnsDirectory">/home/oracle/admin/tnsnames.ora</entry>

<entry key="db.tnsAliasName">ORCL</entry>
<entry key="db.password">@encrypted password</entry>

Or you can specify the entire TNS Connect string using db.CustomURL like below (Note: connectionType=customurl)
<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=myhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
</entry>
<entry key="db.password">@Your_encrypted_password</entry>

#3: Default EZ Connect method
This is the default when you configure ords war using $java -jar ords.war
You don't need to specify db.connectionType here, which default to "basic"
<entry key="db.hostname">myhost</entry>
<entry key="db.password">@ Your_encrypted_password </entry>
<entry key="db.port">1521</entry>

<entry key="db.servicename">ORCL</entry>

Password less login between Unix/Linux/Mac machines

Good news is some one already figured this out. It's here.
http://www.linuxproblem.org/art_9.html

Java Performance Analyser

Occasionally I get "Out Of Memory" error in my Apache Tomcat and with out thinking too much I will just bounce the server to solve the problem. I was searching for the same in Google and it took me to a blog post http://opcodesolutions.com/tech/solve-java-lang-outofmemoryerror-java-heap-space/ in which I found an interesting tool Visual VM.
My goal is not to re-produce every detail about this tool, Name says it all.
http://visualvm.java.net/
I just downloaded it and got up and running in seconds. I have not yet started playing with the tool, but I believe this will solve some of my Java Memory issues.
You can go through the screen cast of this tool from this page.
http://visualvm.java.net/gettingstarted.html

Connecting to Oracle from KDB+ (Using Babel)

I have recently started messing around KDB+ and playing with cryptic Q language.

I want to compare the performance of Oracle vs KDB+, and in order to do that I have to duplicate the Oracle table in KDB+. I tried through ODBC layer but didn't work. Then I found another elegant solution posted on the code.kx.com site using Babel.
http://code.kx.com/wiki/Babel
"Babel for kdb+ is a SQL gateway process that allows kdb+ to query other vendors databases via jdbc."
Babel act as a broker between KDB+ and any database that can be connected through JDBC library. Meaning KDB knows how to talk to Babel and Babel knows how to talk to Databases through JDBC Layer. Now lets get into details.


Step 1:
Download Babel:  wget https://github.com/CharlesSkelton/babel/raw/master/dist/babel.zip
and unzip it.

Step 2:
Download ojdbc7.jar from oracle and copy it to bin directory under Babel. Note: odbc7 is for JDK 1.7 and if your Java version (java -version) is 1.6 you should use odbc6.jar.
http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html
Update: If you are using Java 8/JDK1.8 you can download ojdbc8 from here
http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html
Update: For SQL Server use this thread
https://stackoverflow.com/questions/5307048/where-do-i-install-a-jdbc-driver-on-ubuntu


Step 3: Start the babel on port 6868

$ java -Xmx1024m -Doracle.jdbc.defaultRowPrefetch=10000 -cp "babel.jar:lib/ojdbc7.jar" de.skelton.babel.Babel 6868 oracle.jdbc.OracleDriver

For SQL Server
java -Xmx1024m -cp "babel.jar:sqljdbc42.jar" de.skelton.babel.Babel 6868 com.microsoft.sqlserver.jdbc.SQLServerDriver

Step 4:

Start q (I assume you already have working instance of q). If not you should first learn about q from http://code.kx.com

q)h:hopen 6868

q)r:h("query";"jdbc:oracle:thin:scott/tiger@your_oracle_server:1521:orcl";"select * from emp");

PS: You know Scott/Tiger is user id/ password and ORCL is the Oracle instance.

More References:

KDB$: http://code.kx.com/

White Paper http://code.kx.com/wsvn/code/contrib/jludlow/docs/odbc.pdf?format=raw

Babel: http://code.kx.com/wiki/Babel

Oracle JDBC Driver: (ojdbc7.jar): http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html

JDBC Wiki: http://www.orafaq.com/wiki/JDBC

Extracting Image meta data using Oracle ORDSYS.Image

Let's say you have a table "PICTURE" has a blob column "PIC_BLOB" which contains some image and you want to extract the meta data of the image, here is the query,

Don't ask me any more questions. I spilt my hair to get it working.

PS: If you are asking me why did I do it. Because Photos taken with iPhone are rotated randomly and learned that, rotation angle is stored in the meta data


select VALUE(xml_type).getstringval() 
FROM table(
    select ORDSYS.ORDImage.getMetadata(
                ordsys.ordimage(ordsys.ordsource( PIC_BLOB, null, null, null, null, 1),null, null, null, null, null, null, null)
            )
      from PICTURE WHERE ID=1
)xml_type

Your Output will be something like this.

<ordImageAttributes xmlns="http://xmlns.oracle.com/ord/meta/ordimage" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/ordimage http://xmlns.oracle.com/ord/meta/ordimage"></ordImageAttributes>
<iptcMetadata xmlns="http://xmlns.oracle.com/ord/meta/iptc" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/iptc http://xmlns.oracle.com/ord/meta/iptc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

<exifMetadata xmlns="http://xmlns.oracle.com/ord/meta/exif" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/exif http://xmlns.oracle.com/ord/meta/exif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <TiffIfd>
      <Orientation tag="274">top left</Orientation> -- This is what I am interested in
   </TiffIfd>
   <ExifIfd tag="34665">
      <ColorSpace tag="40961">sRGB</ColorSpace>
      <PixelXDimension tag="40962">3264</PixelXDimension>
      <PixelYDimension tag="40963">2448</PixelYDimension>
   </ExifIfd>
</exifMetadata>


Tip:If you are just interested in Orientation tag then apply Regular Expression like this
replace
VALUE(xml_type).getstringval() 
with
 max(REGEXP_SUBSTR(
               VALUE(xml_type).getStringval(),'<Orientation (.*)>(.*)</Orientation>',1,1,'i',2))

Setting up automatic Wireless Connection for Raspberry Pi

Well, like many others I also bought Raspberry pi (I think a year ago) but didn't do much with it. I decided to do a Garage door opener project and bought Relay & USB Wireless adapter (http://www.amazon.com/gp/product/B003MTTJOY/ $9.99). I started fresh with Rpi new install from http://www.raspberrypi.org/downloads and used "NOOBS (offline and network install)" to install Raspbian OS. Then enabled remote SSH so that I can connect to it from my Mac and I can use the USP ports for attaching Wireless receiver. I have used tightvncserver & tightvnc viewer java client to RDP into Rpi.

From there launch "WiFi Config", and select "scan".

 It will list all available networks and from that select your network and "Double Click" on it. It will bring another window with settings preselected, you just need to enter the password and hit "add"

This will automatically save your wireless network profile to "/etc/wpa_supplicant/wpa_supplicant.conf" file.
You can as well list the contents using 
$sudo cat /etc/wpa_supplicant/wpa_supplicant.conf

--------------------------------------------------
pi@raspberrypi ~ $ sudo cat /etc/wpa_supplicant/wpa_supplicant.conf
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
ssid="Your SSID Name"
psk="Your SSID Password"
proto=RSN
key_mgmt=WPA-PSK
pairwise=CCMP
auth_alg=OPEN
}

-------------------------------------------------------------------

At this time, you can unplug your ethernet cable and still you will be able to access internet through wifi adapter. However if you restart your Rpi, your Wifi connection will not start automatically.
Follow the steps below to enable the wireless connection to startup automatically.


Note: You can't edit interfaces file directly since it is owned by root. You have to use sudo for the same

Either use nano or vi editor to match the content similar to mine

pi@raspberrypi ~ $sudo nano /etc/network/interfaces
or
pi@raspberrypi ~ $sudo vi /etc/network/interfaces
-------------------------------------------------------------------------
auto wlan0

iface lo inet loopback
iface eth0 inet dhcp

allow-hotplug wlan0
iface wlan0 inet dhcp
wpa-conf /etc/wpa_supplicant/wpa_supplicant.conf
iface default inet dhcp
-------------------------------------------------------------------------

Next edit wpa_supplicant.conf in the sameway

pi@raspberrypi ~ $sudo nano /etc/wpa_supplicant/wpa_supplicant.conf
or
pi@raspberrypi ~ $sudo vi /etc/wpa_supplicant/wpa_supplicant.conf

-------------------------------------------------------------------------

ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev

update_config=1

network={

ssid="Your Network SSID"

psk="Your Network Password"

proto=RSN

key_mgmt=WPA-PSK

pairwise=CCMP

auth_alg=OPEN

}

-------------------------------------------------------------------------

Note: proto, keymgmt can be different based upon your network security.

You can use the following blog for that reference, it's kind of the same blog, but didn't work for me.

http://www.howtogeek.com/167425/how-to-setup-wi-fi-on-your-raspberry-pi-via-the-command-line/

Now you can restart your pi and enjoy it.

PS: I have used the following blog to setup my wifi, but neither did work.

http://www.raspberryshake.com/raspberry-pi-wireless-network-setup/

http://www.howtogeek.com/167425/how-to-setup-wi-fi-on-your-raspberry-pi-via-the-command-line/

Hence wrote this blog to show what worked for me.

Geo Coding address from Oracle

Have you ever dreamed about Geo Coding an address using Google Geo Location services through Oracle, your dream has come true.


WITH GEOCODE AS(
    SELECT UTL_HTTP.REQUEST('http://maps.googleapis.com/maps/api/geocode/xml?address='|| replace('1 Main St,Trumbull,CT-06611',' ','+') || '&sensor=false') GEO_XML
     FROM DUAL
)
SELECT EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/status') STATUS
     , EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/result[position()=1]/geometry/location/lat') LATITUDE
     , EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/result[position()=1]/geometry/location/lng') LONGITUDE
     , GEO_XML
  FROM GEOCODE
WHERE EXTRACTVALUE(XMLTYPE(GEO_XML),'/GeocodeResponse/status') = 'OK'

Warning 1: You have to substitute spaces with '+' in the address, otherwise the request will fail.

Warning 2: Some time Google may return bigger XML, however UTL_HTTP.REQUEST will return only the first 2000 characters and it will thrown an error. To over come this issue, replace UTL_HTTP.REQUEST with my dirty version of Oracle CURL function(See my prior blog)

Warning 3: Read the licensing requirements before using Google geocoding services, you might need an business license to use this service.

CURL implementation in Oracle

Have you ever wondered, you wish you had an Oracle CURL function, no problem, your wish is fulfilled.
PS: This will only work for text files and you can one for binary files too.

CREATE OR REPLACE FUNCTION CURL(i_URL VARCHAR2) RETURN CLOB IS
    -- HTTP Portion
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_clob           CLOB;
    l_text           VARCHAR2(32767);
BEGIN
    -- Initialize the CLOB.
    DBMS_LOB.createtemporary(l_clob, FALSE);
    -- Make a HTTP request and get the response.
    l_http_request  := UTL_HTTP.begin_request(i_URL);
    l_http_response := UTL_HTTP.get_response(l_http_request);
    -- Copy the response into the CLOB.
    BEGIN
        LOOP
            UTL_HTTP.read_text(l_http_response, l_text, 32766);
            DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
        END LOOP;
    EXCEPTION  WHEN UTL_HTTP.end_of_body THEN
        NULL;
    END;
    UTL_HTTP.END_RESPONSE(l_http_response);

    RETURN l_clob;

END CURL;
/

Spooling output in SQL PLUS

I am not a big fan of SQLPLUS, typically use GUI tools to access Database. If you happen to use SQLPLUS for spooling output/cretaing file out of SQL Query to a file, here is the code snippet

set echo off;

set linesize 600

set newpage 0

set trimspool off

set heading off;

set tab off;

set serveroutput off;

set termout off;

spool output_file.dat;

< SQL GOES HERE>;

spool off;

quit;