Using SQL * Plus on Command Line in Ubuntu

This blog post is an HowTo "Setup SQL*Plus to work on BASH" The commands illustrated in this howto are tested on Ubuntu 9.10 with Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Installed.

What is SQL * Plus ?

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.

Prerequisite

Oracle Database 10g Express Edition Release 10.2.0.1.0 Installed

Once you have Oracle 10g Installed successfully you can go to

Applications > Oracle Database 10g Express Edition > Run SQL Command Line

and successfully run all your SQL PL/SQL easily but using SQL*Plus directly on BASH is the real fun, Below are the steps which will enable you to use SQL*Plus on BASH

1) set ORACLE_HOME
2) set ORACLE_HOME/bin in $PATH

The above 2 steps can be performed by adding the following lines to .bashrc

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ export $PATH=$PATH:$ORACLE_HOME/bin

The above 2 Steps are sufficient to use SQL*Plus on Command Line once you have done the steps execute

$ sqlplus user/password@database

In the default install XE would be used as database.

Issues while using the default or only the above method

SQL*Plus does not support the Up/Down Arrow command scroll under linux environment which in itself is the biggest problem.
Default Editor is not Set which will confuse you while executing ED.
Various Other Environment variables are not set

Resolving the above mentioned issues

Install rlwrap this will enable the Up/Down Scroll function
$ sudo apt-get install rlwrap
now connect to oracle using
$ rlwrap sqlplus user/password@database
You will find that Up/Down Scroll is now enabled.
Set the most common Environment Variables
$ rlwrap sqlplus user/password@database SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 28 23:53:15 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> SET EDITFILE "afiedt.buf" SQL> DEFINE _EDITOR=vim SQL> SET LINESIZE 132 SQL> SET SERVEROUTPUT ON
This will set the default editor as VIM, Default Edit File as afiedt.buf LINESIZE as 132 & will mark SERVEROUTUT as ON
various other variables can also be set similar commands on the SQL prompt