Inside a shell script, I have written below

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:

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

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";

... for example.

  • thanks, but im calling sqlq() inside shell script. Aug 3 '16 at 7:49
  • You may do the same inside the script. just sqlq q.sql >output.txt.
    – Kusalananda
    Aug 3 '16 at 7:53
  • that is not working.. Aug 3 '16 at 7:54
  • @SrikanthYadake Does it not work in any particular way? Do you get an error message? What is the symptom?
    – Kusalananda
    Aug 3 '16 at 7:55
  • finding issue with creating sql query with cat as mentioned by you, any other way to create sql query file? Aug 3 '16 at 8:01
  1. the output of a function can be redirected to a file, same as with any other command. e.g.
echo "select * from emp" > q.sql
sqlq q.sql > emp.txt
  1. I'd rewrite that function so that it didn't need a temporary file. I'd also be inclined to leave out the grep -v (or make it optional). e.g.
sqlq() {
  local filter

  # very primitive, use getopts for real option handling.
  if [ "$1" == "--delete-blank-lines" ] ; then
    filter='grep -v "^$"'

  # 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
  1. Many improvements are possible - e.g. other useful options in the function include:

    • reading the sql commands from stdin rather than using printf if it detects it's being piped to
    • an option to read the sql from a file
    • abiltity to pass arbitrary options to the sqlplus command.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.