Shell script : Redirect sql query output to a file

Question

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

Kusalananda · Accepted Answer · 2016-08-03T08:05:02.350

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.

edited Aug 03 '16 at 08:05

answered Aug 03 '16 at 07:39

Kusalananda

320,670
36
633
936

thanks, but im calling sqlq() inside shell script. – Srikanth Yadake Aug 03 '16 at 07:49
You may do the same inside the script. just `sqlq q.sql >output.txt`. – Kusalananda Aug 03 '16 at 07:53
that is not working.. – Srikanth Yadake Aug 03 '16 at 07:54
@SrikanthYadake Does it not work in any particular way? Do you get an error message? What is the symptom? – Kusalananda Aug 03 '16 at 07:55
finding issue with creating sql query with cat as mentioned by you, any other way to create sql query file? – Srikanth Yadake Aug 03 '16 at 08:01
@SrikanthYadake You may do it as you did before if you wish. Using a "here-document" (with `<<`) allows you to easily add multiple statements on separate lines, but for a simple select statement you could obviously use `echo 'select ...' >q.sql`. – Kusalananda Aug 03 '16 at 08:04
Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/43398/discussion-between-srikanth-yadake-and-kusalananda). – Srikanth Yadake Aug 03 '16 at 08:39
log file is created whr its intended to but an empty file.. im have put like below in shell script echo "select * from emp" > q.sql sqlq q.sql – Srikanth Yadake Aug 03 '16 at 11:35
Are you able to the query from the command line as `sqlplus -S "sss/eee@sid" – Kusalananda Aug 03 '16 at 11:42

cas · Answer 2 · 2016-08-04T04:20:33.390

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

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

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.

Shell script : Redirect sql query output to a file

2 Answers2