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

Last Comment

GouthamAnand

8/22/2022 - Mon

Joe Howard

5/4/2014

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

5/4/2014

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

5/4/2014

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

5/4/2014

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

5/4/2014

ASKER

Thank you. This worked for me.