Select SQL Queries Option

Shell Programming and Scripting
1

count.sh

#!/bin/ksh


SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3`
SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3`

LIST="Count Select_All"

select i in $LIST
do
if   [ $i = "Count" ]
then
      echo $SQL1
elif [ $i = "Select_All" ]
  then
      echo $SQL2


fi
done
break
#END

count.sql

select count(*) from table
where result='&1' and status='&2' and x_tran_datetime='&date';

exit

Could somebody help me with this. I want to create a script that could give query option to user, one to select & another one to count.

User choose either one option,then it will request for parameters,when the parameters inserted then it will give the output. Please help. Thanks

2

Something like this:

$ 
$ cat select_all.sql 
set feed off verify off heading off pages 0
select * from emp where 1=&1 and 2=&2;     
exit                                       

$ 
$ cat count.sql 
set feed off verify off heading off pages 0
select count(*) from emp where 1=&1 and 2=&2 and 3=&3;
exit                                                  

$ 
$ cat get_data.sh 
#!/usr/bin/bash   
ch=""; p1=""; p2=""; p3=""
echo "1)  Get all data"   
echo "2)  Get the count"  
echo "Enter your choice : "
read ch                    
case "$ch" in              
  "1") echo "Enter parameter 1: "; read p1
       echo "Enter parameter 2: "; read p2 ;;
  "2") echo "Enter parameter 1: "; read p1   
       echo "Enter parameter 2: "; read p2   
       echo "Enter parameter 3: "; read p3 ;;
esac                                         

if [ "$ch" = "1" ]; then
  sqlplus -s test/test @select_all.sql $p1 $p2 >alldata.txt
  echo "All data =>"                                           
  cat alldata.txt                                              
elif [ "$ch" = "2" ]; then                                     
  count=$(sqlplus -s test/test @count.sql $p1 $p2 $p3)         
  echo "The count = $count"                                    
fi                                                             

$ 
$ . get_data.sh
1)  Get all data
2)  Get the count
Enter your choice : 
2                   
Enter parameter 1:  
1                   
Enter parameter 2:  
2                   
Enter parameter 3:  
3                   
The count =     13  
$                   
$                   
$ . get_data.sh
1)  Get all data
2)  Get the count
Enter your choice :
1
Enter parameter 1:
1
Enter parameter 2:
2
All data =>
      7369 SMITH      CLERK           7902 17-DEC-80                 800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81                1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81                2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81                2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81                2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82                3000                    20
      7839 KING       PRESIDENT            17-NOV-81                5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81                1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83                1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81                 950                    30
      7902 FORD       ANALYST         7566 03-DEC-81                3000                    20
$
$

HTH,
tyler_durden

3

Thank you very much ,sir ....it works now :slight_smile: