 |
|
Oracle scripts for Windows
Oracle Tips by Burleson Consulting |
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:
-
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.
-
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.
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.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
