Integrating external command line tools with Oracle data

Integrating external command line tools with Oracle data

One thing I find interesting to explore is the integration of different tools with Oracle which may not always be obvious. For example, take the fantastic UNIX command line tool gnuplot. Using this we can very easily produce graphs and charts directly from Oracle data with very little effort. We can even extend that out further to other utilities such as Imagemagick’s convert.

Consider the following script which extracts the size of the tables in the HR schema in bytes using sqlplus, pipes that into gnuplot to generate a nice bar chart, and then finally adds a nice tomato coloured border around the image and saves the output to a .png file.

sqlplus -s myuser/mypassword@myinstance <<EOF | gnuplot -e "set terminal png; plot '<cat' using 1:3:xtic(2) title 'Table' with boxes" | convert - -bordercolor Tomato -border 10x10 out.png  set heading off set feedback off select rownum, table_name, num_rows*avg_row_len bytes from dba_tables ds where owner='HR';  exit; EOF 

And here is the result:

Graph Image Output

Just to prove the above code does work, here is the output from my 12c demo environment running on Slackware 14 Linux:

oracle@slax:/tmp/blogdemo$ ls oracle@slax:/tmp/blogdemo$ sqlplus -s xxxxx/xxxxx@pdbdev < set heading off > set feedback off > select rownum, table_name, num_rows*avg_row_len bytes from dba_tables ds where owner='HR'; > exit; > EOF oracle@slax:/tmp/blogdemo$ ls -l total 8 -rw-r--r-- 1 oracle dba 7141 Jul  1 16:18 out.png oracle@slax:/tmp/blogdemo$ 

Other possibilities include automatic generation of professional quality documents using command-line based typesetting software ( LaTeX is an excellent and extremely powerful tool for such tasks), to automatically generating documentation for configuration settings etc in HTML.
The command line reigns supreme again. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

twenty + 5 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.