Running SQLPlus From DOS Batch File

[jtanner]

Hello,

On Sun systems I can easily run SQLPlus commands\session from a UNIX shell script as show below:

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << SQLPLUS_SESSION
SELECT * FROM emp;
exit
SQLPLUS_SESSION

I am clueless how to do this in a Windows 2000 standard batch file (.bat). I have been trying variations of this without success:

%ORACLE_HOME%\bin\sqlplus "/ as sysdba" << SQLPLUS_SESSION
SELECT * FROM emp;
exit
SQLPLUS_SESSION

What can you recommend?

Thanks JT

 

[dbtoo2001]

Here is one example from the web:

http://www.tiplib.com/42/scripts-backup-oracle-database-windows

Generally speaking, I have a bat file which executes a sql script.

Example:

export.bat Bat file contents:
set ORACLE_SID=ARSystem
set ORACLE_HOME=D:\oracle\product\10.2.0\db_1
d:
cd D:\oracle\product\10.2.0\admin\ARSystem\export
erase D:\ORACLE_BACKUP\arsystem\backup\export\*.dmp
erase D:\ORACLE_BACKUP\arsystem\backup\export\*.log
xcopy D:\oracle\product\10.2.0\admin\ARSystem\export\*.dmp D:\ORACLE_BACKUP\arsystem\backup\export\*.*
xcopy D:\oracle\product\10.2.0\admin\ARSystem\export\*.LOG D:\ORACLE_BACKUP\arsystem\backup\export\*.*
erase D:\oracle\product\10.2.0\admin\ARSystem\export\*.dmp
erase D:\oracle\product\10.2.0\admin\ARSystem\export\*.log
sqlplus system/xxx @export.sql xxx
/exit;
exit



SQL export.sql contents:
column instnc new_value v_inst noprint
column instdate new_value v_instdate noprint
SELECT name instnc
FROM v$database;
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy-hh24-mi-ss') instdate FROM dual;
host exp system/&1 full=y consistent=y file=d:\oracle\product\10.2.0\admin\&&v_inst\export\&&v_inst&&v_instdate.dmp log=d:\oracle\product\10.2.0\admin\&&v_inst\export\&&v_inst&&v_instdate.log;
exit;
exit


I then schedule the .bat file with at.

 

[dbtoo2001]

Here's another example:

http://www.dbasupport.com/forums/showthread.php?t=56316

 

[Welshbird]

Here is one I use myself as an example:

@echo off
title Load Ad-Hoc into Oracle
cd \
cd SQLLDR
cd adhoc40
REM  **********************************************
REM  * Tracker Production Process*
REM  **********************************************
REM
REM
REM  First the Ad-Hoc zip file has to be copied to:
REM  c:\SQLLDR\adhoc overwriting the original file
REM
unzip -o -q C:\SQLLDR\Adhoc40\AH__0040_0001.zip
REM
REM  That bit is done then....
REM
REM  Now to try and load the files into the load
REM  Tables in ORACLE
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\acomp1.ctl 
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\acomp2.ctl 
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\acomp3.ctl 
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\acomp4.ctl 
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\acomp5.ctl 
sqlldr fwilliams/pword@o_server control = c:\SQLLDR\adhoc40\ctl\dat1.ctl  
REM
REM  Now to make these into one table with the correct codes
sqlplus fwilliams/pword@o_server @C:\sqlldr\adhoc40\ctl\make_table.sql
REM Then to run the other stuff to produce the report
PRINT Now go and check the output files
PRINT which can be found in: c:\oracle\fees_scripts
PRINT and are named RXPACK.csv and CUPack.csv
print

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]