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

Question

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

RobW · Accepted Answer · 2012-04-03 20:15:19Z

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

AVA · Accepted Answer · 2014-11-14 06:53:25Z

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

answered Nov 14, 2014 at 6:53

AVA

2,5782 gold badges31 silver badges45 bronze badges

1 Comment

Rebin Over a year ago

what if my password has '@' in it?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related