Oracle Scripts for Windows Oracle 11g New Features Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Oracle Scripts for Windows

Every DBA will, from time to time, write his or her own utilities for managing Oracle.  Thus, the basics of writing scripts against the Oracle database will be briefly covered. There are several choices for writing Oracle scripts in a Windows environment:

• .bat files - Microsoft enhanced the DOS command functions starting with Windows 2000.  Even so, Unix adherents will argue that DOS commands are not nearly as powerful as a UNIX shell script.  No doubt, Windows adherents disagree with this.

• 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 more complicated and does not provide total compatibility for porting UNIX scripts to Windows.

• Unix Dos - The Unix Dos Toolkit from Professional Software Solutions provides all of the UNIX-like functions.  However, Unix Dos is not a free program.

What follows are some examples of writing simple Oracle scripts using windows .bat files.

Sample Invocation of Oracle from Windows

The DOS command line can be used to call Oracle. The trick is that the SQL*Plus invocation spools the output on a single command line:

Here is a Windows Oracle script with DOS commands to set the environment:

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

Running a Data Pump Export from Windows

Data Pump Export was first available in Oracle 10g and is used to replace the old export/import utilities.  See chapter xx for more information about Data Pump. Below is a very nice utility script that Jeff Hunter, Oracle Guru, wrote called oracle_export.bat.  This is a script for exporting data from Windows.

A key learning point here is that he builds the exp parfile arguments into a DOS variable called %PARFILE% by echoing the settings to a file using the >> append command:

REM +--------------------------------------------------------------------------+
REM | VALIDATE COMMAND-LINE PARAMETERS                                         |
REM +--------------------------------------------------------------------------+

if (%1)==() goto USAGE
if (%2)==() goto USAGE
if (%3)==() goto USAGE


REM +--------------------------------------------------------------------------+
REM | VALIDATE ENVIRONMENT VARIABLES                                           |
REM +--------------------------------------------------------------------------+

REM set ORABACKUP=C:\oracle\orabackup\JEFFDB\export
REM set ORALOG=C:\oracle\custom\oracle\log
REM set ORATMP=C:\oracle\custom\oracle\temp

if (%ORALOG%)==() goto ENV_VARIABLES
if (%ORATMP%)==() goto ENV_VARIABLES
if (%ORABACKUP%)==() goto ENV_VARIABLES


REM +--------------------------------------------------------------------------+
REM | DECLARE ALL GLOBAL VARIABLES.                                            |
REM +--------------------------------------------------------------------------+

set FILENAME=export_backup_online_full_9i
set DB_USERNAME=%1%
set DB_PASSWORD=%2%
set TNS_ALIAS=%3%
set PARFILE=%ORATMP%\%FILENAME%_%TNS_ALIAS%.parfile
set LOGFILE=%ORALOG%\%FILENAME%_%TNS_ALIAS%.log
set DUMPFILE=%ORABACKUP%\exp_full_%TNS_ALIAS%.dmp


REM +--------------------------------------------------------------------------+
REM | REMOVE OLD LOG AND PARAMETER FILE(S).                                    |
REM +--------------------------------------------------------------------------+

del /q %PARFILE%
del /q %LOGFILE%


REM +--------------------------------------------------------------------------+
REM | WRITE EXPORT PARAMETER FILE.                                             |
REM +--------------------------------------------------------------------------+

echo userid=%DB_USERNAME%/%DB_PASSWORD%@%TNS_ALIAS% > %PARFILE%
echo buffer=50000000 >> %PARFILE%
echo file=%DUMPFILE% >> %PARFILE%
echo compress=n >> %PARFILE%
echo grants=y >> %PARFILE%
echo indexes=y >> %PARFILE%
echo direct=no >> %PARFILE%
echo log=%LOGFILE% >> %PARFILE%
echo rows=y >> %PARFILE%
echo consistent=y >> %PARFILE%
echo full=y >> %PARFILE%
REM echo owner=(SCOTT) >> %PARFILE%
REM echo tables=(EMP, DEPT) >> %PARFILE%
echo triggers=y >> %PARFILE%
echo statistics=none >> %PARFILE%
echo constraints=y >> %PARFILE%


REM +--------------------------------------------------------------------------+
REM | MOVE OLD EXPORT (DUMP) FILE.                                             |
REM +--------------------------------------------------------------------------+

del /q %DUMPFILE%.backup
move %DUMPFILE% %DUMPFILE%.backup


REM +--------------------------------------------------------------------------+
REM | PERFORM EXPORT.                                                          |
REM +--------------------------------------------------------------------------+

exp parfile=%PARFILE%


REM +--------------------------------------------------------------------------+
REM | SCAN THE EXPORT LOGFILE FOR ERRORS.                                      |
REM +--------------------------------------------------------------------------+

echo ...
echo Analyzing log file for EXP- errors...
findstr /I /C:"EXP-" %LOGFILE%
if errorlevel 0 if not errorlevel 1 echo EXP- Errors:  %FILENAME% %TNS_ALIAS% %COMPUTERNAME% %DATE% %TIME% %LOGFILE%

echo ...
echo Analyzing log file for ORA- errors...
findstr /I /C:"ORA-" %LOGFILE%
if errorlevel 0 if not errorlevel 1 echo ORA- Errors:  %FILENAME% %TNS_ALIAS% %COMPUTERNAME% %DATE% %TIME% %LOGFILE%

echo ...
echo Analyzing log file for warnings...
findstr /I /C:"Export terminated successfully with warnings" %LOGFILE%
if errorlevel 0 if not errorlevel 1 echo WARNING: %FILENAME% %TNS_ALIAS% %COMPUTERNAME% %DATE% %TIME% %LOGFILE%

echo ...
echo Analyzing log file for errors...
findstr /I /C:"Export terminated unsuccessfully" %LOGFILE%
if errorlevel 0 if not errorlevel 1 echo ERROR: %FILENAME% %TNS_ALIAS% %COMPUTERNAME% %DATE% %TIME% %LOGFILE%


echo ...
echo END OF FILE REPORT
echo Filename      : %FILENAME%
echo Database      : %TNS_ALIAS%
echo Hostname      : %COMPUTERNAME%
echo Date          : %DATE%
echo Time          : %TIME%
echo EXP Log File  : %LOGFILE%

Copyright © 1996 -  2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.