SQLPLUS -S option not working

Hi All,

I am using the following script to run some sql on database but i am not getting the result. When i tried the same by removing "-s" option it is working fine but getting other things as well with my input as shown below. Can anyone please suggest why "-s" option is not working in AIX and reply an alternate option if available.

This is my code without sqlplus -s option

OUTPUT=$( sqlplus <usrname>/<passwd> <<EOF
set heading OFF termout ON trimout ON feedback OFF
set pagesize 0
select column1,column2 from tablename where column1=${FILENAME1};
exit;
EOF
)

and the ouput is as below:

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 3 02:26:39 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> STI101.412026.SIZE.zip
       4012

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

How to remove all other data in the ouput apart from the values i actually required from sql

Thanks in advance.

In what way is it 'not working'? Do you get any output/errors that we can see?

Could I also suggest that you move your credentials to the next line. If anyone does a ps -ef | grep sql whilst your code is running, they will see your credentials in clear text. Something more like:-

OUTPUT=$(sqlplus -s <<EOF
$usrname/$passwd
set heading OFF termout ON trimout ON feedback OFF
set pagesize 0
select column1,column2 from tablename where column1=${FILENAME1};
exit;
EOF
)

Of course, keeping the credentials secret if you code them in the script is another problem.....:rolleyes:

Robin

Thanks Robin.

I have used " sqlplus -silent " instead of sqlplus -s and it worked fine.

But i will take your suggestion for adding username and password in the next line instead of in the 1st line to prevent them from seen by others during execution.