Install and Configure SQL Plus on *NIX

1./ Download SQL Plus and InstantClient from the Oracle site
Open a browswer and go to http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
The following packages are required

• SQLPlus: Additional libraries and executable for running SQL*Plus with Instant Client (e.g. instantclient-sqlplus-solaris.sparc64-12.1.0.1.0.zip)
• Basic Lite: Smaller version of the Basic, with only English error messages  (e.g. instantclient-basiclite-solaris.sparc64-12.1.0.1.0.zip)

2./ Copy these archves to the target machines, create a directory (e.g. /opt/bpm/scripts/database) and unpack the above packages in this directory 

3./ Set and export the following enviroment variables to point at the instantclient_<version> directory. For example...

• export LD_LIBRARY_PATH=/opt/bpm/scripts/database/instantclient_12_1:${LD_LIBRARY_PATH}
• export SQLPATH=/opt/bpm/scripts/database/instantclient_12_1:${SQLPATH}
• export TNS_ADMIN=/opt/bpm/scripts/database/instantclient_12_1

4./ Test basic operation 

• cd /opt/bpm/scripts/database*
• ./sqlplus

If the runtime is working correctly this message will be displayed

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 1 10:20:04 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: ERROR: ORA-12162: TNS:net service name is incorrectly specified

Enter user-name:

5./ Set up tnsnames.ora

• Create the file /opt/bpm/scripts/database/instantclient<version>/tnsnames.ora - e.g. /opt/bpm/scripts/database/instantclient_12_1/tnanames.ora
• Add an entry for each database. For example ... 

DATABASE_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jupiter.stack1.com)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BPM_UAT)
    )
  )

DATABASE_2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = saturn.stack1.com)(PORT = 1725))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = BPM_DEV)
    )
  )

6./ Test connectivity

• cd /opt/bpm/scripts/database
• ./sqlplus <user>/<password>@<database name>

For example

• ./sqlplus BPMN_USER/********#@DATABASE_1

You should now be logged in to the desired database as the specified user and be sitting at an SQL> prompt.

7./ Compete the test by querying the database... 

• select USERNAME from ALL_USERS where username like '%BPM' order by username;