Using a variable in a batch script for SQLLDR

Question

Hi i am trying to set a variable in my .bat file and i want to use the variable in sqlldr code to state the file (infile)

This is what i have in my bat file for the variable:

set directroy_name= D:\Folder\Folder\Folder\File.csv

Then in my command file i have

load data
infile '%directory_name%'

When ever i try to run the .bat file from the command prompt i just receive the SQL_Loader_500: unable to ope file (%directory_name%.dat

I know the files in the correct location?

any ideas why its doing this?

Alex Poole · Accepted Answer · 2014-02-06 14:12:57Z

1

No, you can't do that - you're expecting the Oracle executable to understand Windows environment variable syntax. If it did that it would have to deal with $ variables in Unix, etc.

You can just pass the file name on the command line instead. In your control file omit the INFILE altogether, then when you call SQL*Loader add a a DATA command-line argument:

sqlldr user/password CONTROL=your.ctl DATA=%directory_name% ...

Assuming your variable is just oddly named and does have a full file path as you've shown.

If it's present, the INFILE argument will be overridden by the command-line argument, so you could include a default fixed value if you wanted to, I suppose.

You also appear to have a typo; you set directroy_name, but then use directory_name, which will have no value. You need to change that to:

set directory_name= D:\Folder\Folder\Folder\File.csv

edited Feb 6, 2014 at 14:12

answered Feb 6, 2014 at 13:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alex Poole Over a year ago

@RichardC - yes. It doesn't have to be a variable of course, you can just specify the full file path in the DATA argument, but I assume it's being used elsewhere in your script. You might want to set the BAD file name etc. in the same way.

rkyyk Over a year ago

thanks for the help. I have edited my sqlldr command: set directroy_name=D:\filename.csv sqlldr userid/password@database control= D:\file.sql log= D:\log.log DATA=%directory_name% in my command file i am getting syntax error now i have remvoed the infile, it reads: load data TRUNCATE into table tablename fields terminated by ',' TRAILING NULLCOLS (colm1, coml2)

rkyyk Over a year ago

When i run the .bat file from the command line i just recieve SQL*LOADER-100: syntax error on command line, does not tell me anything specfic

Alex Poole Over a year ago

And the variable name mismatch? Did you check that? It looks like the var you're actually using will be empty, which would probably explain that error?

rkyyk Over a year ago

the variables are called the same it is set like this: set directroy_name=D:\filename.csv and called and the end of the sqlldr like this: DATA= %directroy_name% what do you mean about the variable been empty it should be the .cvs file?

|

Collectives™ on Stack Overflow

Using a variable in a batch script for SQLLDR

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related