Using SQL * Plus on Command Line in Ubuntu

Published by abhishek -

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

Refer SQL*Plus FAQ's for more information.

ubuntu
sysadmin
sqlplus
oracle
howto