Avatar of GouthamAnand
GouthamAnand
 asked on

windows batch(.cmd) file

Hi,

I have windows .cmd file which has oracle sqlplus command to spool the data and also oracle sqlldr commands to load the spooled data.
The data in the .cmd file is like in the attached file.

I want to set the connection string "abc/abc_dev@10.157.26.16:1522/mnop"
for oracle at one place instead of muliple times mention. Because like this sqlldr loading, I have many tables.

Can you please suggest?

thanks.
test.txt
Microsoft Legacy OSOracle Database

Avatar of undefined
Last Comment
GouthamAnand

8/22/2022 - Mon
Joe Howard

Try this:

sqlplus abc_APPREAD/folder1#123@10.126.247.32:1522/XYZ11 @E:\folder1\SQLLDR\SPOOL_ALL_TABLES.sql
con=abc/abc_dev@10.157.26.16:1522/mnop
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA1.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data1.ctl log =E:\folder1\SQLLDR\LOG_FILES\data1_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data1_DATA.bad
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA2.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data2.ctl log =E:\folder1\SQLLDR\LOG_FILES\data2_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data2_DATA.bad
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA3.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data3.ctl log =E:\folder1\SQLLDR\LOG_FILES\data3_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data3_DATA.bad
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA4.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data4.ctl log =E:\folder1\SQLLDR\LOG_FILES\data4_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data4_DATA.bad
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA5.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data5.ctl log =E:\folder1\SQLLDR\LOG_FILES\data5_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data5_DATA.bad

Open in new window

Wasim Akram Shaik

Save this in a .bat file then this should work for you

@echo off

start sqlldr abc/abc_dev@10.157.26.16:1522/mnop ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA1.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data1.ctl log =E:\folder1\SQLLDR\LOG_FILES\data1_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data1_DATA.bad
pause
start sqlldr abc/abc_dev@10.157.26.16:1522/mnop ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA2.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data2.ctl log =E:\folder1\SQLLDR\LOG_FILES\data2_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data2_DATA.bad
pause

Open in new window


A Simpler version of this could be,
place everything in a single folder and then use the below code without any directory alias and also place the batch file in the same place along with other files.

@echo off

start sqlldr abc/abc_dev@10.157.26.16:1522 ERRORS=999999 data =DATA2.txt control =data2.ctl log =data2_DATA.log bad =data2_DATA.bad
pause

Open in new window


EDIT: I was typing from mobile so the post was a little bit late. expert Macroshadow suggestion was better one though
GouthamAnand

ASKER
Hi,

con=abc/abc_dev@10.157.26.16:1522/mnop
sqlldr %con% ERRORS=999999 data =E:\folder1\SQLLDR\DATA_FILES\DATA1.txt control =E:\folder1\SQLLDR\CONTROL_FILES\data1.ctl log =E:\folder1\SQLLDR\LOG_FILES\data1_DATA.log bad =E:\folder1\SQLLDR\BAD_FILES\data1_DATA.bad

This is not working. Its gettting stopped after the con=abc/abc_dev@10.157.26.16:1522/mnop
and not reading sqlldr command.

Thanks.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
David Johnson, CD

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GouthamAnand

ASKER
Thank you. This worked for me.