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

Question

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

Answer 1

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 off
set echo off
set trimspool on
set termout off
set serveroutput on size 100000 format wrapped
set lines 500
set pages 0

-- create the bat file to be executed later:
spool windows_commands.bat

declare
  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 off

exit

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

@rem create oracle session, call script.sql
sqlplus %user%/%password%@%db% @script.sql

@rem script.sql has created windows_commands.bat.
@rem call this newly created bat file:
call windows_commands.bat

Answer 2

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

Answer 3

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 ...

Answer 4

I do something like this by creating a .bat file which does the windows stuff and calling sql scripts as needed. Use SQL to spool your results to a text file which you can read.

...dos commands here

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

sqlplus /nolog @C:\Dump\Cleanup.sql

...dos commands

In the sql use this command spool C:\yourResults.txt or for more sophisticated usages create a procedure, which, when called, writes the results to a text file using UTL_FILE

Answer 5

I encourage you to take a look at the two scripts included in the Oracle XE for backup and restore. These scripts have taught me a lot how to handle batch-scripting and Oracle on the Windows platform.

• C:\oraclexe\app\oracle\product\11.2.0\server\bin\Backup.bat
• C:\oraclexe\app\oracle\product\11.2.0\server\bin\Restore.bat

Answer 6

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @f:\testa.txt