Shell script that starts sqlplus utility and executes a query, gathers output

Question

I'm trying to do this:

In sample.sh:

#!/bin/bash
sqlplus / as sysdba
select * from dual;

And just when I run this shell script, it opens sqlplus utility, but it cannot execute the next line that I have written. When I manually exit out of sqlplus, only then the shell executes the remaining line and I get error message saying 'Select * from dual' is not a valid command.

How can I make the script execute the SQL text within the context of sqlplus?

Solution

To run sql script in sqlplus you need to do it on this way:

#!/bin/bash
sqlplus / as sysdba <<EOF
select * from dual;
exit
EOF

or you can put sql commands in script like this:

# cat a.sql
select * from dual;
exit

and run the command on this way:

#!/bin/bash
sqlplus / as sysdba @a.sql

Comments

[Shantharam Puranik]

It worked, thank you! Is there a provision to read the outputs and store it in a variable?

[Atul Vekariya]

@ShantharamPuranik, you can store it in file. Check spool command (in sqlplus)