Link to home
Start Free TrialLog in
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
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

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

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
Avatar of GouthamAnand
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.
ASKER CERTIFIED SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. This worked for me.