0

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
2

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.

  • thanks, but im calling sqlq() inside shell script. – Srikanth Yadake 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.. – Srikanth Yadake 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? – Srikanth Yadake Aug 3 '16 at 8:01
1
  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
  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
  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.