We help IT Professionals succeed at work.

windows batch(.cmd) file

765 Views
Last Modified: 2014-05-04
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
Simple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you. This worked for me.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.