Hello.
I have to make a script with more than 1 SQL query and the problem is that i have to alternate from sql commands to unix commands.
what i would like to know if it's posible(and how) to keep the databse connection opened till last sql command is ran and execute unix commands while the connection is opened.
I was thinking something like this:
->open connection -sqlplus.....
->run sql commands
->run unix commands
->close connection.
I don't think that we can keep the oracle session open to do some querying and at the same time to run the unix command. Since oracle queries are run through sqlplus command we would need to come out of that command/session to run the next unix command. Else if you are all set with all your required queries then you can run all the sql queries in a single sqlplus command and re-direct the output to a file, close the connection and run the rest of the unix commands.
Something like this?
sqlplus -s <<!
/ as sysdba
set feed off pages 0
select sysdate from dual;
host uname -a
select count(1) from tab;
!
Output:
$ sqlplus -s <<!
> / as sysdba
> set feed off pages 0
>
> select sysdate from dual;
>
> host uname -a
>
> select count(1) from tab;
>
> !
31-MAR-11
SunOS xxxx 5.8 Generic_108528-27 sun4u sparc SUNW,Sun-Fire-880
2407
If you need to pass the output of the SQL statements to the Unix shell - back and forth - you could use co-processes (ksh93, zsh and bash4 support co-processes, if I recall correctly)