I am trying to create a batch file for windows (XP) which will have several sqls and when its run it would 1. connect with oracle 2. set the userid/password/schema 3. run each sql in the loop and 4. output each sql outputs to its own flat file.
I have started the script
@ECHO off
SET STATE=fl
TABLE1=AGENCY
set SQL1="SELECT Column_ID||CHR(31)||column_ENTITY_CD||CHR(31) FROM AGENCY"
set TABLE2=FIRM
set SQL2="SELECT Column_ID||CHR(31)||Column_NM||CHR(31) FROM FIRM"
set TABLE3=FL_CO_LOB
Set SQL3="SELECT Column_ID||CHR(31)||Column_LOB_CODE||CHR(31) FROM FL_CO_LOB"
...
SET NumberOfTables=19
SETLOCAL ENABLEDELAYEDEXPANSION
FOR /l %%A IN (1,1,%NumberOfTables%) DO (
echo !SQL%%A!
)
endlocal
I can get the SQL out of the variable but don't have any clue how to connect to oracle and run the sql and get the output to a defined file.
please give me some direction.
one thing to notice that the echo is printing including the double quote. but If i don't have them then it just print the 1st word not the whole query.
Thanks
If you've installed the oracle client on your workstation, you have SQLPlus.exe for command line work with the database. Your TNSNAMES.ORA file needs to be up to date, and tnsping needs to be able to locate your service.
I think you'll need to change your approach. The scripts you want to run will need to be fed to the SQLPlus.exe program, rather than being delivered semi-interactively.
I suggest creating a .sql script instead of creating evnironment variables
Your batch file line might look like:
@ECHO off
SET STATE=fl
set TABLE1=AGENCY
set TABLE2=FIRM
echo SELECT Column_ID^|^|CHR(31)^|^|column_ENTITY_CD^|^|CHR(31) FROM %TABLE1% > tablecommands.sql
echo SELECT Column_ID^|^|CHR(31)^|^|Column_NM^|^|CHR(31) FROM %TABLE2% >> tablecommands.sql
echo SELECT Column_ID^|^|CHR(31)^|^|Column_LOB_CODE^|^|CHR(31) FROM FL_CO_LOB >> tablecommands.sql
...
<until your 19 SQL statements are declared>
SQLPlus User/Password@Database @tablecommands.sql
Add the sqlplus login command before your sql commands in your batch file. It executes and writes to logs file where the batch file resides
syntax : sqlplus -s ora_user_name/ora_user_password [as sysdba]@ora_sid@"path_to_sql_file" > output.log
ex. sqlplus -s scott/tiger@xe @"D:\Oralcle\scripts\sql_file.sql" > output.txt