Category Archives: Uncategorized

Indexing into Elasticsearch from Oracle with PLSQL

I started to play around with Elasticsearch and one of the use cases I thought that I could apply in my job was to remove all heavy searches from the Oracle database and make the web front end quicker when searching and filtering complex data grids.
As we keep multiple logs in XML when entities ara changing, I realized that I could take some of these XMLs, transform them to json and index them into an Elasticsearch cluster having synchronized the Oracle database with the search engine (ES) based on different database events that would fired a trigger when desired.
  • You may need some sys privileges for executing
  • Off course your database server needs to be able to make http or https requests to your Elasticsearch cluster ( http://yourelasticserver:9200)
At the end the solution is quite simple: Trigger an event and launch a html post request for indexing some data.
First at all you need to be able to use the utl_html package. 2 single steps:
1. Grant html privileges to your database user:
sql> grant execute on sys.utl_html to dbuser;
2. Create the ACL and grant it to your dbuser
dbms_network_acl_admin.create_acl (‘ACL_elastic.xml’ ,’Network Access Control for Html Service’ ,’DBUSER’,TRUE,’connect’);
dbms_network_acl_admin.assign_acl (‘ACL_elastic.xml’,’yourelasticserver’, 9200, 9200);
With the following package you should be able to send a HTML request. The public interfaces are 2 functions for indexing and bulk indexing.
create or replace package pac_elasticsearch as
function f_index( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_message clob
, p_id varchar2 default null
return clob;
function f_index_bulk( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_bulk_body clob
return clob;
end pac_elasticsearch;
create or replace package body pac_elasticsearch as

function post_request ( p_url varchar2, p_request_body clob)
return clob as
utl_req utl_http.req;
utl_resp utl_http.resp;
req_length binary_integer;
response_body clob;
res_length binary_integer;
buffer varchar2(2000);
amount pls_integer := 2000;
offset pls_integer := 1;
utl_req := utl_http.begin_request (p_url, ‘POST’, ‘HTTP/1.1’);
utl_http.set_header(utl_req, ‘content-type’, ‘application/json’);
req_length := dbms_lob.getLength( p_request_body );
if req_length <= 32767 then
utl_http.set_header(utl_req, ‘content-length’, req_length );
utl_http.write_text(utl_req, p_request_body);
elsif req_length > 32767 then
utl_http.set_header ( utl_req, ‘Transfer-Encoding’, ‘chunked’ );
while (offset < req_length)
loop p_request_body, amount, offset, buffer);
utl_http.write_text ( utl_req, buffer );
offset := offset + amount;
end loop;
end if;
utl_resp := utl_http.get_response( utl_req);
— utl_http.read_text( utl_resp, response_body, 32767);
utl_http.read_text( utl_resp, response_body);
utl_http.end_response( utl_resp );
return response_body;
end post_request;

function f_index( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_message clob
, p_id varchar2 default null
return clob
v_url clob;
v_url := ‘http://&#8217;||p_host||’:’||p_port||’/’||p_index||’/’||p_type;
if ( p_id is not null) then
v_url := v_url||’/’||p_id;
end if;
return request ( v_url, p_message );
end f_index;

function f_index_bulk( p_host varchar2
, p_port number
, p_index varchar2
, p_type varchar2
, p_bulk_body clob
return clob
v_url clob;
v_url := ‘http://&#8217;||p_host||’:’||p_port||’/’||p_index||’/’||p_type||’/_bulk’;
return post_request ( v_url, p_bulk_body );
end f_index_bulk;
end pac_elasticsearch;
Now you should be able to directly index data into Elastcisearch from Oracle.


How to install an Oracle Database on your MacBook

The best way I have found for installing an Oracle Database (EE 12c) on you MacBook is installing a Database Virtual Box Appliance / Virtual Machine.

Main steps are:

  1. Install Oracle VM VirtualBox Manager

You can download it from and the installation is very simple.

  1. Download the VM appliance from Oracle

I recommend to use a download manager as I failed twice just trying to download it as a normal file using Chrome. I installed DownThemAll from (note: it requires Firefox). The OVA file can be downloaded from:

  1. Create the Virtual Machine.

Open VM Virtual Box and go to File/Import appliance. Then select the file that you have download and just go forwards.

And that’s all. Everything is installed.

Start working with your database

Now you should start the VM for starting using your database. Then you will be ready to make a client connection from you Mac, either using a tnsnames entry as follows:






Or using a jdbc connection string like jdbc:oracle:thin:@

All passwords are oracle (including user sys).

For being able to share files between your Mac and the Oracle VM you can enable the drag and drop option in the Oracle VM VirtualBox Manager. Select the VM created, click on Settings. A new window will pop up. In the General screen you will find where to set up the Shared ClipBoard and the Drag & Drop in the tab Advanced.

Latest note: this should not be used for creating an environment for developers, testers, etc. And off course if you are thinking about a production environment just forget it.

How to install Tomcat in Mac OS

How to install Tomcat in Mac OS

 1. Check that java is installed in your computer

By default MAC comes with java installed, you do not have to do anything special expect that you want an specific java version running.

Open a Terminal and do the check:

$ java -version

java version “1.6.0_51”

Java(TM) SE Runtime Environment (build 1.6.0_51-b11-457-11M4509)

Java HotSpot(TM) 64-Bit Server VM (build 20.51-b01-457, mixed mode)

2. Download the apache tomcat

Tomcat is software that permits a web request to be directed to a piece of Java code (servlet) with some associated infrastruture to handle cookies and request/reply coordination. These functions are collectively referred to as a “web container”.   Tomcat used to also be referred to as Catalina. The Catalina name is still used in some of the config setup below

 Download Tomcat 7 Binary Distribution Core (tar.gz) from the Apache Tomcat 7 Downloads Page and uncompress it into a directory of your choice.

Copy the files to your /usr/local folder:

$ sudo mv /Users/<your_user>/Downloads/apache-tomcat-7.0.53.tar.gz /usr/local

Uncompress the downloaded file:

$ sudo gnutar -xzvf apache-tomcat-7.0.53.tar.gz

Now you are ready to startup, use and shutdown yout Toncat server:

$ cd apache-tomcat-7.0.53/bin/

$ ./

At this point you can test that your sever is up and running from a navigator with http://localhost:8080/

You can shutdown the server just typing:

$ ./

3. Set the environment variables

Edit the bash profile with an editor like vi and add the following lines:

$ sudo vi ~/.bash_profile

# Tomcat entries

export CATALINA_BASE=”/usr/local/apache-tomcat-7.0.53″

export CATALINA_HOME=”/usr/local/apache-tomcat-7.0.53″

export CATALINA_TMPDIR=”/usr/local/apache-tomcat-7.0.53/temp”

Save it and reload the bash profile:

$ . ~/.bash_profile

4. Deploy and test an application

copy or deploy your test application with its own folder in /usr/local/apache-tomcat-7.0.53/webapps/myTestApp

Open a navigator and type test your URL: http://localhost/myTestApp/myIndex.jsp