Oracle <---> Ruby

Before I shed some light on the technicalities here is the mandatory introduction comprising the TOOLS USED:

1) Oracle 11g

2) SQL Developer: Oracle SQL Developer is the Oracle Database IDE. A free graphical user interface, Oracle SQL Developer allows database users and administrators to do their database tasks in fewer clicks and keystrokes

3) sqlPlus: SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.There is also the SQL*Plus Instant Client which is a stand-alone command-line interface available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to any available Oracle database, but does not require its own Oracle database installation.

4) Ruby and OCI8

Although we can use IRB (Interactive Ruby) to connect and then manipulate tables in Oracle, it’s suggested that we write a Ruby script and run that with the ruby interpreter command.OCI8 (Oracle Call Interface): ruby-oci8 is a ruby interface for Oracle using OCI8 API. It is available with Oracle 10g or later including Oracle Instant Client.

Prerequisites: Install oci8 and dbi

>gem install ruby-oci8 -v 2.2.1

>gem install dbi

Let’s see how we would generally fetch data using interfaces such as sqlPlus and sqlDeveloper respectively:

Now in the following examples we will see various DML in a ruby script using oci8:

OCI8/dbi provides fancy formatting ways to display results in tabular and XML formats. As depicted below:

Update:

HOWEVER....NOW....Time to reveal the magic trick, all of this can be done in a single command line statement:

ruby -r oci8 -e "OCI8.new('scott', 'tiger').exec('select * from states') do |r| puts r.join(','); end" > tableoutput.txt

Output in a notepad file will be like this:

See you later ...............