In the previous article I’ve described how to install Oracle Instant Client and setup cx_Oracle Python driver correctly.
You only need Basic package to install:
Version 18.3.0.0.0
Base – one of these packages is required
Basic Package – All files required to run OCI, OCCI, and JDBC-OCI applications
Download instantclient-basic-linux.x64-18.3.0.0.0dbru.zip (72,794,506 bytes) (cksum – 3435694482)
Although many articles advice to additionally install SQL*Plus Package, under the Tools optional packages ,
I’ve promised to provide better alternative.
SQL*Plus is one of possible choices for sure, but why not to use SQL*Plus on steroids?
Several years back I wrote article named “SQLcl tool – a modern SQL*Plus” on the following link:
https://www.josip-pojatina.com/en/sqlcl-tool-a-modern-sqlplus/
Oracle constantly makes improvements on Sqlcl and resolves minor bugs.
For that reason Sqlcl today is really SQL*Plus on steroids, and it comes from Oracle.
The only prerequisite for installation is to have JRE 8 or above already installed on your system.
On the other hand, you’ll get IntelliSense and many new features that are not available in regular SQL*Plus (details you can find in article from above).
After downloading sqlcl-18.4.0.007.1818.zip file, all you need to do is to unzip it.
I’m using the following directory: /home/user/home/user/sqlcl
You need to replace user with your account name.
Next step is to create symbolic link, to be able to call sqlcl tool from any location.
You need to have sudo on root privilege for the following command to work.
root@user-Ubuntu1804LTS:/usr/local/bin>ln -s /home/user/sqlcl/bin/sql sqlcl
In case you don’t have sudo right (or cannot ask sysadmin to do that for you), you can add alias in .profile or .bash_profile or .bashrc.
Now it’s time to test connection by using SID:
user@user-Ubuntu1804LTS:~>sqlcl scott@172.25.1.6:1521:orcl
SQLcl: Release 18.4 Production on Wed Jan 23 11:00:18 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Password? (**********?) **
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
or by using service name:
user@user-Ubuntu1804LTS:~>sqlcl scott@172.25.1.6:1521/orcl.localdomain
SQLcl: Release 18.4 Production on Wed Jan 23 11:31:10 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Password? (**********?) **
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Finally you can query your data like in this example.
SQL> SELECT table_name from USER_TABLES;
TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
I strongly believe that
sqlcl scott@172.25.1.6:1521/orcl.localdomain
is more easier to write and remember than:
sqlplus scott@\"172.25.1.6:1521/orcl.localdomain\"
Summary:
Here I’ve described the easiest way to create and setup environment to connect to Oracle database from command line.
As I point out, only Basic Instant Client Package is needed for establishing connection with Oracle database from Java or Python, and everything else you can do with Sqlcl.
Once you start using that tool, you’ll never go back to SQL*Plus.
In the next post I’ll describe how to install SQL Developer on Ubuntu, as Ubuntu become dominant platform on the cloud for developing modern apps.
Comments