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

I'd probably write the script (or the conditional, depending on the requirements) from the called script.sql itself.

For example, the following script.sql creates a .bat file windows_commands.bat:

set feedback offset echo offset trimspool onset termout offset serveroutput on size 100000 format wrappedset lines 500set pages 0-- create the bat file to be executed later:spool windows_commands.batdeclare  c number;begin  select count(*) into c from dual;  -- depending on a conditional, write the stuff to be executed into the  -- bat file (windows_commands.bat)  if c = 1 then     dbms_output.put_line('@echo everthing ok with dual');  else     dbms_output.put_line('@echo something terribly wrong with dual');  end if;end;/spool offexit

You can then call script.sql from yet another .bat file like so:

@rem create oracle session, call script.sqlsqlplus %user%/%password%@%db% @script.sql@rem script.sql has created windows_commands.bat.@rem call this newly created bat file:call windows_commands.bat

This is what I ended up using.

My .cmd script:

@ECHO OFFECHO Checking Oracle...for /f %%i in ('sqlplus -s user/password@database @script.sql') do @set count=%%iecho %count%IF %count% GTR 0 GOTO :skippedGOTO :runprocess

Where script.sql:

SELECT COUNT(*)FROM tableWHERE criteria = 1;exit

I would strongly encourage you to not use .bat files. You've got lots of other alternatives: C/C++ or VB, Windows scripting or Powershell, or even free downloads like Perl or Bash.

But here's one example of returning error codes in .bat files:

• http://www.dbforums.com/oracle/1044496-sqlplus-return-value-help.html

But please do look at some of the links I gave above. Avoiding .bat files will make it easier for you, and make it easier to maintain in the future.

IMHO ...