How to Install SQL*Plus Client on Linux

Its really very simple steps to do this, and very crazy while dependency for compatibility with OS (x32 bit or x64 bit).

I have done for x64 bit Linux UBUNTU. Generally Oracle provides .rpm packages , and you need to download those packages into  your machine.

Download here.

Assuming that you have already installed Oracle Database , else you are connecting some remote database. Because, this post only show the sqlplus installation to connect existing database.

Downloaded files :-

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm
Copy these files to your preferred location. I have  used below location.

/usr/java

Now time for convert these .rpm packages into Debian specific package and install on your Ubuntu. Use alien command to convert.

Follow the below steps and install one-by-one.

First install the sqlplus :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm

[sudo] password for dev:

dpkg --no-force-overwrite -i oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-sqlplus.

(Reading database ... 226951 files and directories currently installed.)

Unpacking oracle-instantclient12.1-sqlplus (from oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-sqlplus (12.1.0.1.0-2) ...

Second install the basic (packages) :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
dpkg --no-force-overwrite -i oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb

Selecting previously un-selected package oracle-instantclient12.1-basic.

(Reading database ... 226963 files and directories currently installed.)

Unpacking oracle-instantclient12.1-basic (from oracle-instantclient12.1-basic_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-basic (12.1.0.2.0-2) ...

Processing triggers for libc-bin ...

ldconfig deferred processing now taking place 

Third install the devel :-

dev@javadevelopersguide-developer-desktop /usr/java $ sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

dpkg --no-force-overwrite -i oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb

Selecting previously unselected package oracle-instantclient12.1-devel.

(Reading database ... 226981 files and directories currently installed.)

Unpacking oracle-instantclient12.1-devel (from oracle-instantclient12.1-devel_12.1.0.2.0-2_amd64.deb) ...

Setting up oracle-instantclient12.1-devel (12.1.0.2.0-2) ...

Once these installation complete, you can start sqlplus using below command. Either you can use your specific user/password with correct server address.

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

Good to go if no error, else follow with fixing below !! Good luck.

Note - You may face the below library missing issue while executing sqlplus command. Error below :-

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory


Now this issue saying the lib is not loading, the sqlplus is complaining about the missing library.

So you need to edit the oracle.conf and provide the correct lib path (i.e : /usr/lib/oracle/12.1/client64/lib).Add this path into oracle.conf file.

dev@javadevelopersguide-developer-desktop /usr/java $ sudo vi /etc/ld.so.conf.d/oracle.conf

/usr/lib/oracle/12.1/client64/lib

Now, you should be able to connect to your preferred Database. I have connected to our remote server as below :

dev@javadevelopersguide-developer-desktop /usr/java $ sqlplus my_user/my_password@z2customdb01.ztest/z2custom.zenv.mycompanyadd.com.in
SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 23 16:30:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Wed Dec 23 2015 16:18:22 +11:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning option
SQL>

SQL>

How to Generate SQL INSERT Statements from Existing Data in Oracle

Use this following format/query for generate script for insert data into database (Table) .When you export data or migrate data from one database to other database it may help you.With out creating the dumps you can export data from one database to other database.But it is table wise.

You can simply generate script for insert  and then run the generated script on command line.

Example 1 :-

 SELECT 'INSERT INTO DUAL VALUES ('''||dummy||''');' FROM DUAL;

Generated Output: INSERT INTO DUAL VALUES ('X');

Example 2 :-

SELECT 
    'INSERT INTO EMP_DETAILS VALUES (' ||
    '''' || REPLACE(EMP_NAME, '''', '''''') || ''', ' ||
    '''' || EMP_SEX || ''', ' ||
    '''' || TO_CHAR(EMP_JOIN_DT, 'DD-MON-YYYY') || ''');' AS insert_script
FROM M_EMPLOYEE;


Generated Output:

INSERT INTO EMP_DETAILS VALUES ('Rakesh', 'M', '03-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Manoj Kumar', 'M', '06-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Santosh Kumar', 'M', '02-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Rakesh Kumar', 'M', '05-JAN-2005');
INSERT INTO EMP_DETAILS VALUES ('Sunil Dev', 'M', '01-APR-2005');
INSERT INTO EMP_DETAILS VALUES ('Sheeba', 'F', '01-JAN-2005')

Use the generated output in command line and execute.