Connecting to Oracle DB using sqlplus

Shell Programming and Scripting
21

@shrutihardas: I'd think you're still echoing the "Connected." portion of the output from your script...possibly the same typo that was causing the seemingly blank output of the $my_sql variable.

22

This is the final script which I am using now

my_sql=$(sqlplus -s /nolog <<EOF
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'
set pages 0
select sysdate from dual;
EOF)
echo ResultIs = $my_sql

And i am using echo only in the end. I am still getting output as -
Connected. 06-MAY-10

Please advise.

23

Please make sure of the -s or silent switch in the call to sqlplus...

Otherwise, I'd expect to see something like this:

-> my_sql=$(sqlplus -s /nolog <<EOF^Jconnect bcurley/bcu001^Jset pages 0 ^Jselect sysdate from dual; ^Jexit^JEOF)
-> echo Result is: $my_sql                                                                                                                                                     
Result is: 06-MAY-10

If not, you might see garbage like, including both "Connected." and the date value:

-> my_sql=$(sqlplus /nolog <<EOF^Jconnect bcurley/bcu001^Jset pages 0 ^Jselect sysdate from dual; ^Jexit^JEOF)
-> echo Result is: $my_sql                                                                                    
Result is: SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 6 11:27:12 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> Connected. SQL> SQL> 06-MAY-10 SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
24

"Connected." is definitely coming from Oracle. It is coming out on standard output. It seem unaffected by Oracle "set .... off" commands. We can get rid of it with unix "grep -v" .
Another approach is to use an Oracle spool command to output the result of the query to a file, then read the file back.

25

Better yet, as grep -v might eliminate the line...if it echoes on the same line as the date, try this:

my_sql=$(sqlplus -s /nolog <<EOF|sed -e "s/Connect. *//g" 
connect scott/tiger
set pages 0 
select sysdate from dual; 
exit
EOF)
26

Thanks a lot curleb!

This code provided by you worked perfectly fine!

In the meanwhile i also tried another approach to truncate the string returned by sql by using the following:

${value##*.}

But the code provided by you using SED makes more sense.

Thanks again to all of you for your time and help!

Greatly appreciated.

27 
ksh-on-solaris$ x=$(cat <<E
a'bc'd
E)
ksh-on-solaris$ echo "$x"
a"bc"d