Python & Oracle Instant Client connection setup on Linux – part 2

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.

---