Inside a shell script, I have written below
sqlq(){
result=`sqlplus -S sss/eee@sid < $1|grep -v '^$'`
echo $result
}
echo "select * from emp" > q.sql
sqlq q.sql
I need to direct the output to a file. I have tried spool inside q.sql like below but didn't work
echo "SPOOL /tmp/test.txt; select * from emp';SPOOL off" > q.sql
Your code:
sqlq(){
result=`sqlplus -S sss/eee@sid < $1|grep -v '^$'`
echo $result
}
echo "select * from emp" > q.sql
sqlq q.sql
The echo and the variable in the sqlq function are not needed:
sqlq () {
sqlplus -S "sss/eee@sid" < "$1" | grep -v '^$'
}
This will send the output of the function to standard output (as it did before).
When calling the function, you may redirect its output to a file:
cat >query.sql <<END_SQL
SELECT * FROM emp;
END_SQL
sqlq query.sql >result.out
The reason I'm using a here-document here is that it makes it very easy to create multi-line SQL queries:
cat >query.sql <<END_SQL
SELECT data.*
FROM data
JOIN attributes ON (data.attr_id = attributes.attr_id)
WHERE attributes.name = "Example";
END_SQL
... for example.
sqlq q.sql >output.txt.
echo "select * from emp" > q.sql
sqlq q.sql > emp.txt
grep -v (or make it optional). e.g.sqlq() {
local filter
filter='cat'
# very primitive, use getopts for real option handling.
if [ "$1" == "--delete-blank-lines" ] ; then
filter='grep -v "^$"'
shift
fi
# each arg is piped into sqlplus as a separate command
printf "%s\n" "$@" | sqlplus -S sss/eee@sid | $filter
}
sqlq --delete-blank-lines 'select * from emp' > emp.txt
# two sql commands:
sqlq 'select * from emp;' 'select * from foo' > empfoo.txt
# that last example works just as well as:
sqlq 'select * from emp; select * from foo' > empfoo.txt
Many improvements are possible - e.g. other useful options in the function include:
printf if it detects it's being piped tosqlplus command.