Using Batch Script connect to oracle db run a select command and output all data to a flat file

Go To StackoverFlow.com

0

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

2012-04-03 19:39
by ak77


1

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
2012-04-03 20:15
by RobW


0

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

2014-11-14 06:53
by AVA