Sunday, April 4, 2010

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




4 comments: