Shell Programming and Scripting

#!/bin/bash

#Connect to the database using sqlplus

sqlplus 'DBUSER/dbuser@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PHYGD1)))' >> outputlogfile.log

# Fire the query on database

DCOFCVALUE=select sync_cmpl
from table(dbdev.db_inv_dtl_hdr)
where dc_id ='30'
and to_char(sync_req_dttm,'YYYYMMDD')= '20100331';

ENDSQL

# Check if [/B]DCOFCVALUE  was succesful or not
if [ $DCOFCVALUE = Y ]
then
    echo "DCOFCVALUE  Succesfull\n"
  exit 2
else
  if [ $DCOFCVALUE = N]
  then
     echo "DCOFCVALUE [B] Unsuccesfull\n"
  exit 3
  fi
fi

exit 0

line 29: sync_cmpl: command not found
.line 30: syntax error near unexpected token `('
line 30: `from table(dbdev.db_inv_dtl_hdr)'

$ 
$ 
$ # First, let's check what we have in the table "T" of schema "TEST" of my local database
$ (echo "select * from t;") | sqlplus -s test/test

     X
----------
       100

$ 
$ # So, there's just one column "X" and one row and the one value in the table is 100
$ # Now show the contents of the shell script that connects to this database and
$ # fetches this value
$ 
$ cat -n fetch_value.sh
     1    #!/bin/bash
     2    
     3    #Connect to the database using sqlplus
     4    x=`sqlplus -s /nolog <<EOF
     5    connect test/test
     6    set pages 0 
     7    select x from t;
     8    exit
     9    EOF`
    10    
    11    # print the value of x fetched from the database
    12    echo "Value fetched from database, x = $x"
    13    
    14    # check if this value equals 100
    15    if [ $x == 100 ]; then
    16      echo "Yes, the value of x is 100"
    17    else
    18      echo "No, the value of x is $x"
    19      return 1
    20    fi
    21    return 0
    22    
$ 
$ # Now run the script
$ . fetch_value.sh
Value fetched from database, x =        100
Yes, the value of x is 100
$ 
$

...
from table(dbdev.db_inv_dtl_hdr)
...

...
from dbdev.db_inv_dtl_hdr
...

sqlplus 'DBUSER/dbuser@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PHYGD1)))' <<ENDSQL >> outputlogfile.log

--# Fire the query on database

select 
         sync_cmpl
from 
         dbdev.db_inv_dtl_hdr
where 
         dc_id 
            =        '30'
   and   to_char(sync_req_dttm,'YYYYMMDD')
            =        '20100331'
;

ENDSQL

##Intiliase the required variables....
 
USERID='SCOTT'
USERPWD='TIGER'
dbname='US.ORACLE.COM' 

##Spool the query output into a unix variable 

Output_variable=`sqlplus -s $USERID/$USERPWD@$dbname <<!
set heading off feedback off trimspool on 
--your query here;
select sysdate from dual;
exit
!`

echo Output_variable : $Output_variable

ERROR:
ORA-12545: Connect failed because target host or object does not exist

VOMSD1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))

echo $ORACLE_SID
tnsping VOMSD1

username/password@<value_of_ORACLE_SID>