Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle scripts for Windows

Oracle Database Tips by Burleson Consulting

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:

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:

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

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


 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.