Connect to Oracle database through Unix

Due to my professional requirements nowadays I use Oracle most of the time and sometimes I need to use Oracle through Linux environment. Today I am going to discuss the basics of connecting to Oracle database through Linux. For this purpose Oracle provided us a pretty cool utility with a basic command line interface. Yes, SQL*Plus is the utility I am talking about.



Now one disclaimer, I have tested all the below in Redhat Linux environment only.
Connection to Database:
First you need to connect to the database. Use the sqlplus command to connect.

sqlplus <USER_NAME>/<PASSWORD>@<SERVICE_NAME>

Now Let your USER_NAME, PASSWORD and SERVICE_NAME is "U1UATSBRPK", "xzy321" and "ODSRUAT" respectively. So you will use like :
 

sqlplus U1UATSBRPK/xzy321@ODSRUAT

after you are connected successfully you will probably get a screen like this:

Formatting the output:
Now you are connected to database, so you can type your SQL queries and get the result. Ok , lets get data from a table.

SELECT * FROM TEST_TEMP;

wait a minute, what are these garbage on the screen. Nope they are not garbage, its the output of your query only in non-formatted form. You need to format the output. So you first run below commands:

set wrap off;
set linesixe 3000;

But beware, your output may get truncated now, if it doesn't fit on the specified line size.
I am not going in details for formatting , so you can have a look here and here.

Connection through bash script:
Most of the times you will need to connect to the database through bash script. Just place all the commands you used to connect to the database from a terminal in the script. Have a look in the below script:

sqlplus U1UATSBRPK/xzy321@ODSRUAT << END

WHENEVER SQLERROR EXIT SQL.SQLCODE
set wrap off
set linesize 3000
SELECT * FROM TEST_TEMP;
DESC TEST_TEMP; 

END

Spooling the result:
When you are connected to the database, you can no longer use unix command. So how you will store the result of the query you run. For that purpose you can use the concept of spooling a file.
You just define a spool file and start spooling it before execution of any database query. After execution of all the query don't forget to stop the spooling. You can alter the script to accommodate the concept of spooling like this:

touch spool_file.txt
sqlplus U1UATSBRPK/xzy321@ODSRUAT << END
WHENEVER SQLERROR EXIT SQL.SQLCODE
set wrap off
set linesize 3000
spool spool_file.txt
SELECT * FROM TEST_TEMP;
DESC TEST_TEMP;
spool off
END

Keep the screen clear:
Whenever you use some query in database through bash script, you will end up with a messy screen with all the result of the queries you made. If you are spooling the results and don't need the query result on the screen you can redirect the output to /dev/null . You can do this like:

touch spool_file.txt
sqlplus U1UATSBRPK/xzy321@ODSRUAT << END > /dev/null
WHENEVER SQLERROR EXIT SQL.SQLCODE
set wrap off
set linesize 3000
spool spool_file.txt
SELECT * FROM TEST_TEMP;
DESC TEST_TEMP;
spool off
END