Oracle Scripts for Windows Oracle Database Tips by Donald Burleson

 

 

 

This is the BC Oracle DBA Scripts collection with Oracle DBA Scripts for tuning, monitoring, a professional download of over 600 Oracle DBA Scripts.

 

 

 

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

For Oracle-only tasks it's best to write scripts using the dbms_job or dbms_scheduler packages:

• Dr. Hall has a great collection of downloadable Oracle scripts in his book "Oracle Job Scheduling".
   
• For UNIX and Linux Oracle scripts, see Jon Emmon's book "Oracle Shell Scripts".
   
• For scheduling Windows jobs, see Windows Oracle Job Schedulers examples

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

• Cygwin - The CYGWIN product supports UNIX shell scripting
   

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

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

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

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 environment:

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3
SET LOG=\temp\test\

ECHO spool %LOG%OraCall.log                                 > %LOG%OraCall.sql
ECHO set linesize 132                                      >> %LOG%OraCall.sql
ECHO COL x_tns      NEW_VALUE v_tns      NOPRINT           >> %LOG%OraCall.sql
ECHO COL x_dbid     NEW_VALUE v_dbid     NOPRINT           >> %LOG%OraCall.sql
ECHO COL x_dbname   NEW_VALUE v_dbname   NOPRINT           >> %LOG%OraCall.sql
ECHO SELECT '%DBTNS%'  x_tns      FROM dual;               >> %LOG%OraCall.sql
ECHO SELECT dbid       x_dbid     FROM v$database;         >> %LOG%OraCall.sql
ECHO SELECT name       x_dbname   FROM v$database;         >> %LOG%OraCall.sql
ECHO @SQLSelect.sql                                        >> %LOG%OraCall.sql
ECHO spool off                                             >> %LOG%OraCall.sql
ECHO exit                                                  >> %LOG%OraCall.sql
sqlplusw -s "%DBUser%/%DBPass%@%DBTNS%" @%LOG%OraCall.sql   > %LOG%OraCall.lst

Examples of Oracle Windows scripts

Here are some working examples of Windows Oracle scripts:

• Oracle export script for Windows
   
• Oracle e-mail interface for Windows
   
• Oracle Windows alert log script
   
• Monitor Oracle Windows dump and trace files