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;

Mac Security-Running selected external applications

Mac Mountain lion has a new security feature(actually lot of features) which prevents running apps downloaded outside of Mac app store. If you try to run those apps you will get a warning something like this. For example, I have downloaded SQL Developer from Oracle.

When ever this happens everyone will Google it and look for a solution to disable this feature. I did the same thing but stumbled upon a new way of running those apps without changing the default security setting. Thanks to http://www.intego.com/mac-security-blog/new-security-features-in-os-x-mountain-lion/ You just need to hold "Control" key, right click on the app and select "Open. This time you will get a slightly different warning. Select Open and you need to enter Admin credentials to make it effective. That's it. Now you can open SQL Developer and it won't bother you again with that security message.

Why Windows 7 is better than Windows 8

In other words,  why Fidelity Windows 7 gadget  is better than Windows 8 finance app with Live tiles.

Dow Jones index is shown in red which kind of indicates it's down in Fidelity app.

 What's the point in having live tile, if it can't paint the picture using proper color. Why is it in green when the index is down in Windows 8.

Mapping multiple schemas to Apex Workspace

Unfortunately Apex Admin application doesn't let you map more than one schema to the workspace. However I had a requirement to map 500 schema's to one single workspace (For the creation of training application for 500 Users). APEX_INSTANCE_ADMIN package came in handy and I did it using PL SQL.
Here is how you do it.

SQL> APEX_INSTANCE_ADMIN.ADD_SCHEMA(
p_workspace  => 'MY_WORKSPACE', p_schema => 'MY_SCHEMA');

PS: You can use the same package REMOVE_SCHEMA to remove the mapping as well. Also SYS & SYSTEM schema's cannot be mapped using the same due to security restriction.

Reference to Oracle documentation: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_instance.htm