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.


grep lines having a text string from compressed files

The log of an application in my job is archived in a file, 1 file per day. I was requested to extract the activity of one client from these logs for the whole August, i.e., from 31 compressed files.

I placed all compressed log files into a folder called /database and I created a file to search and extract the log lines of my client. (If your *.gz  files are located somewhere else then pay attention to line 11 of tje script and modify it properly)


# author: tomeu.mir
# filename:
# desc  : search string in all *gz files and spools the output to a file
# usage : . <search string> <output filename>

for f in database/*.gz
  now=$(date +”%Y/%m/%d %T”)
  echo “[$now] Processing $f file with search string $search_string and output file $output_file”   
  zgrep -i “$search_string” $f >>  $output_file
now=$(date +”%Y/%m/%d %T”)
echo “[$now] done.”

Finally I executed the following:

$ . MYCLIENTNAME output.log

SQLDeveloper. Change the IDE language and font size

First you have to shutdown your SQLDEveloper.

IDE language

Edit the file sqldeveloper.conf for changing the IDE language adding the following lines:

# Tomeu Mir – forcing English UI language
AddVMOption -Duser.language=en

The location of the sqldeveloper.conf is at /Applications/

IDE Font Size

Edit the file for setting the desired font size of the IDE. Comment any line like Ide.FontSize.* and add the following one:


For finding the location of the file you can do the following from a shell command and choose the one corresponding to the highest edition.

$ cd ~/.sqldeveloper/
.sqldeveloper$  find . -name “”

Now you can restart your SQLDeveloper and enjoy working again.

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 create a manual ODBC connection to an OLite database


Sometimes I required to troubleshoot in the OLite database of a mobile component having only temporary access to it. In this case it is very useful to copy the database files in your computer, connect to it and take your time for investigating any issue.

You can follow next steps for it:

  1. Copy the OLite *.obs and odb files into a local path
  1. Register the ODBC driver for the product in the Windows Registry, as follows:


  • Create a new KEY:
  • Oracle Lite 40 ODBC Driver
  • Add next values:
  • 32Bit = 1
  • ApiLevel = 0
  • ConnectFunctions = YYN
  • Driver = c:\mobileclient\bin\olod2040.dll
  • DriverODBCVer = 02.00
  • SQLLevel = 0
  • Setup = c:\mobileclient\bin\olad2040.dll


  • Add a new value:
  • Oracle Lite 40 ODBC Driver = Installed
  1. Create a new ODBC DSN entry

Using the ODBC Data Source Administrator taking special attention to:


  • Data Source Name: How you want to identify that connection
  • Database Directory: The path where the files have been copied
  • Database: the database name (without extensions)


  1. Test your connection with msql:

Note: You need to know the password of the user which the database has been copied from.

C:\>msql system/<user_password>@jdbc:polite:<data_source_name>


Send query result in HTML format by email

Send query result in HTML format by email

You can create the following function which returns a CLOB with the query result formatted in HTML. Then you can symply send the email calling the UTL_MAIL.send procedure as follows:

sys.UTL_MAIL.send ( sender => v_sender,
recipients => ‘your@email’,
cc => NULL,
bcc => NULL,
subject => ‘your subject’,
message=> get_html_report( ‘select * from employees’ ),
mime_type => ‘text/html’

More info at

And here the function get_html_report  that I have copied from

create or replace FUNCTION get_html_report(p_query IN VARCHAR2) RETURN  CLOB IS

ctxh            dbms_xmlgen.ctxhandle;

xslt_tranfsorm  XMLTYPE;

l_mail_body     CLOB;


ctxh:= dbms_xmlgen.newcontext(p_query);


— XSLT Transformation to HTML

xslt_tranfsorm := NEW XMLTYPE(‘

<xsl:stylesheet xmlns:xsl=”; version=”1.0″>

<xsl:template match=”/ROWSET”>



<xsl:for-each select=”ROW[1]/*”>

<th> <xsl:value-of select=”name()”/></th>






<xsl:template match=”ROW”>



<xsl:template match=”ROW/*”>

<td style=”text-align:right;”><xsl:value-of select=”.”/></td>




dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);


dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);


l_mail_body := dbms_xmlgen.getxml(ctxh);




RETURN l_mail_body;

END get_html_report;