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
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)) )
To connect as DB super-user:
sqlplus sys/[email protected]_db_name as SYSDBA
To use command history (
up-arrow) and auto-completion (
TAB) you have to install
sudo apt-get install rlwrap
Words for autocompletion place into file (separated by spaces or newlines):
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;
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;
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.