How to use sql*plus in Windows command script to control flow?

batch-file, oracle, oracle10g, sqlplus, windows

Solution

This is what I ended up using.

My .cmd script:

@ECHO OFF
ECHO Checking Oracle...

for /f %%i in ('sqlplus -s user/password@database @script.sql') do @set count=%%i
echo %count%

IF %count% GTR 0 GOTO :skipped
GOTO :runprocess

Where script.sql:

SELECT COUNT(*)
FROM table
WHERE criteria = 1;

exit

Problem

I'm trying to use sql*plus to control a small Windows command script. Basically, I want to execute some PL/SQL (perhaps select from a view or table or execute a function) which shows me the status of some rows in the database, and then depending upon the state of the rows, perform some Windows commands. My problem is how to get the results back into the command script. ``` sqlplus user/password@server @script.sql IF <CONDITIONAL HERE BASED on script.sql results> GOTO :runprocess REM log and email that process had to be skipped EXIT :runprocess REM run various Windows service commands ```

Original source