How to connect to sqlplus from Shell?

   Sqlplus is an Oracle command line utility which is used to execute SQL and PL/SQL commands.  Connecting to sqlplus from UNIX box to retrieve data is one of the very common tasks and hence sqlplus becomes an important tool in shell scripting.  The data to be retrieved from Oracle could simply be a column value from a table or it could be set of data from more than one  table. This article explains the different ways to connect to sqlplus from shell and retrieve data.

   Let us consider the example of retrieving data from the EMPLOYEE table to get the employee-id for a given employee name:

 Example 1:
    This example shows a sample program which connects to the sqlplus and retrieve the employee-id for the given employee name.

#!/usr/bin/ksh emp_id=`sqlplus –s $USER/$PASSW@$INST   << EOF                 set pagesize 0                 set feedback off                 set verify off                 set heading off                 select EMP_ID from EMPLOYEE where EMP_NAME='Blogger';                 exit; EOF` echo $emp_id

Example 2:
   This example explains the same as above, except this uses a variable name as a parameter.

#!/usr/bin/ksh EMP="Blogger" emp_id=`sqlplus –s $USER/$PASSW@$INST   << EOF                 set pagesize 0                 set feedback off                 set verify off                 set heading off                 select EMP_ID from EMPLOYEE where EMP_NAME='$EMP';                 exit; EOF` echo $emp_id

Example 3:
      This example shows another way to connect to sqlplus from shell.

#!/usr/bin/ksh EMP="Blogger" emp_id=`echo "                 set pagesize 0                 set feedback off                 set verify off                 set heading off                 select EMP_ID from EMPLOYEE where EMP_NAME='$EMP';                 exit;" | sqlplus –s $USER/$PASSW@$INST  ` echo $emp_id

Example 4:
  This example shows sql file based approach to connect to the sqlplus. The sql code is written in a separate sqlplus script file emp.sql, and the shell script emp_id.sh invokes the sqlplus script :

#cat emp.sql set pagesize 0 set feedback off set verify off set heading off select EMP_ID from EMPLOYEE where EMP_NAME='&1'; exit; #cat emp_id.sh #!/usr/bin/ksh EMP="Blogger" emp_id=`sqlplus –s $USER/$PASSW@$INST  @emp.sql $EMP` echo $emp_id

Tweet