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

Question

I am trying to use sql * plus to manage a small windows script command.

Basically, I want to execute some PL / SQL (maybe choose from a view or a table or execute a function) that shows me the state of some rows in the database, and then, depending on the state of the rows, it executes some Windows Commands.

My problem is how to return the results to a script command.

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

Here 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 2

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

For example, the following script.sql creates a .bat file for 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 

Then you can call script.sql from another .bat file as follows:

 @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 3

I highly recommend you not to use .bat files. You have many other alternatives: C / C ++ or VB, Windows or Powershell scripts, or even free downloads like Perl or Bash .

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

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

But please take a look at some of the links that I provided above. Avoiding .bat files will make your work easier and easier to support in the future.

IMHO ...

Answer 4

I am doing something like this by creating a .bat file that does Windows stuff and calls sql scripts as needed. Use SQL to generate the results in a text file that you can read.

... dos commands are here

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

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

... dos commands

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

Answer 5

I recommend that you take a look at the two scenarios included in Oracle XE for backup and recovery. These scripts taught me a lot about how to handle batch scripts and Oracle on the Windows platform.

• C: \ oraclexe \ application \ oracle \ product \ 11.2.0 \ server \ Bin \ backup.bat
• C: \ oraclexe \ application \ oracle \ product \ 11.2.0 \ server \ Bin \ Restore.bat

Answer 6

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64-bit production With partition parameters, OLAP, Data Mining, and Real Application Testing

 SQL> @f:\testa.txt