Oracle scripts for Windows
Oracle Database Tips by Donald BurlesonConsulting
In Windows, any file with a .bat extension is considered an executable batch
file. This is the type of file we will place our commands in. The .cmd
extension can also be used, which is a legacy from 16 bit Windows that still
works, with some reported exceptions in Windows XP. For
Windows shell scripting, see the book
for the Oracle DBA.
Also see how to execute
multiple sql files in DOS Windows.
Writing Oracle scripts in a Windows environment can be problematic and there
are some important secrets for deploying Oracle scripts in a Windows server.
Oracle-only scripts in Windows
The only time that you need to have a script reside in a Windows environment
is when you need to do something to the Windows files, and even in these cases
you can write PL/SQL to use the utl_file utility to read flat files on
For Oracle-only tasks it's best to write scripts using the dbms_job or
Types of Oracle scripts in Windows
There are several
choices for writing Oracle scripts in a Windows environment:
DOS bat files -
Microsoft enhanced the DOS command functions starting with Windows 2000, but
DOS commands were nowhere near as powerful as a UNIX shell script.
MKS Toolkit - A way to run UNIX
shell scripts in Windows
- The CYGWIN product supports UNIX
SFU - Microsoft released Windows Services For
UNIX (SFU) to more
closely emulate a variety of UNIX shells and UNIX utilities to ease the
migration from a UNIX to a Windows environment. While SFU is
certainly a more comprehensive solution than the native command prompt, it
is a more complicated and does not provide total compatibility for porting
UNIX scripts to
UnixDos - The
UnixDosToolkit from Professional Software Solutions provides all of the UNIX-like functions.
Windows PowerShell for Oracle - This is a new command
line interface for Windows.
For simple examples of Oracle scripts in Windows, let's
review simple Oracle commands within an executable bat file using Windows DOS
Sample invocation of Oracle from Windows
The DOS command line can interface to Oracle, and we see this
working Windows bat file executable with an embedded SQL*Plus
The trick is that the SQL*Plus
invocation spool the output on a single command line:
sqlplusw -s "%DBUser%/%DBPass%@%DBTNS%"
@%LOG%OraCall.sql > %LOG%OraCall.lst
Here is a Windows Oracle script with DOS commands to set the
ECHO spool %LOG%OraCall.log
ECHO set linesize 132
ECHO COL x_tns NEW_VALUE v_tns
ECHO COL x_dbid NEW_VALUE v_dbid
ECHO COL x_dbname NEW_VALUE v_dbname
ECHO SELECT '%DBTNS%' x_tns
ECHO SELECT dbid x_dbid
ECHO SELECT name x_dbname
ECHO spool off
sqlplusw -s "%DBUser%/%DBPass%@%DBTNS%" @%LOG%OraCall.sql
Examples of Oracle
Here are some working examples of Windows Oracle scripts:
These are just a few examples of Windows scripts for Oracle and hopefully you
can see how to invoke Oracle from Windows and schedule Windows batch jobs for
Windows for the Oracle DBA
The landmark book
Windows for the Oracle DBA is a comprehensive overview of
everything an Oracle DBA needs to know to manage Oracle on
Windows. Order directly from Rampant and save 30%.