SQLplus with a human face

Oracle SQL*plus is awful and hard to install so I hope this post will help you to deal with it. You will learn

Installation

Oracle server installation includes sqlplus. Instruction below is for you if you do not need local Oracle server installation, just sqlplus to control remote DB server.

Download http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html and install:

sudo apt-get install alien
sudo alien -i oracle-instantclient*-sqlplus*.rpm   
sudo apt-get install libaio1
sudo sensible-editor /etc/ld.so.conf.d/oracle.conf
# указать в файле путь /usr/lib/oracle/12.2/client64/lib/
sudo ldconfig 
sudo ln -s /usr/bin/sqlplus64 /usr/bin/sqlplus

Add to .bashrc

export ORACLE_HOME=/usr/lib/oracle/12.2/client64/lib
export LD_LIBRARY_PATH="$ORACLE_HOME"
export LD_LIBRARY_PATH="$ORACLE_HOME"
export TNS_ADMIN=$ORACLE_HOME/admin/network

Save in the file $ORACLE_HOME/admin/network/tnsnames.ora your connection string:

your_db_name =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
      (SERVER=DEDICATED)
    )
    (CONNECT_DATA = (SERVICE_NAME = your_db))
  )

Usage

To connect as DB super-user:

sqlplus sys/sys_password@your_db_name as SYSDBA

To use command history (up-arrow) and auto-completion (TAB) you have to install rlwrap:

sudo apt-get install rlwrap

Words for autocompletion place into file (separated by spaces or newlines):

~/.command_completions

Create alias sql so we do not have to type all this long command:

alias -p sql='rlwrap -f ~/.command_completions sqlplus'

Note about sys remote connection

To connect as DB super-user (SYSDBA) remotely we have to set “external” password for user sys. That’s because by default user ‘sys’ have password only for internal login (from the same host as DBMS).

External password (in the example below sys_password) will work for internal and remote logins:

alter user sys identified by sys_password;

Compilation errors

If there are some errors in package or procedure, sqlplus just write “Warning: Package created with compilation errors.”.

To see the errors use

select * from dba_errors order by sequence;

Nuning

SET AUTOCOMMIT 1; - autocommit each statement, useful if you want to play file with inter-dependent inserts.

/ after procedure or package body to compile it.

EXIT in the end of file (@file_name), so SQLplus will exit after executing it.