Invoking SQLLoader from java, issues loading a file with name that contains @-signs

Question

I have to load a file with a file name containing @ symbol.

i am invoking the sql loader from my java class.

Currently when i try to load a file with @ symbol in the file name, the sql loader is not able to load the file.

The SqlLoader log says:-

SQL*Loader-503: Error appending extension to file (/FileWith@Symbol.csv)

SQL*Loader-567: unable to derive file name

SQL*Loader-509: System error: Error 0

Commands sent to SqlLoader are below

String[] cmd = new String[]   
{  
                    sqlldrPath ,  
                    user + "/" + password + "@" + sid,  
                    "control=" + file.getAbsolutePath(),  
                    "direct=true",  
                    "log=" + log.getAbsolutePath() + File.separator  
                            + lr.getTempTable() +".log"};  

String[] env = new String[]  
 {  
                    "ORACLE_HOME="+oracleHome,
                    "LD_LIBRARY_PATH="+oracleLibPath+":"+oracleLib32Path+":$LD_LIBRARY_PATH"              
};

Calling SqlLoader here with the above cmd and env string arrays.

 Process p = Runtime.getRuntime().exec(cmd, env);

I found SQLLoader with a password that contains @-signs

which talks about escaping @ sign in the password but is it possible to escape @ sign if it is in the file name?

Thank you.

I guess you allready tested just escaping with backslash? because `ls test\@fi*` returne `test@filename` in example — evilive, Nov 17 '14 at 14:07
Hi, yes i replaced every occurrence of @ with \@ before creating the control file. I am getting the same SQL*Loader-567: unable to derive file name. — Santosh budhe, Nov 17 '14 at 14:12
Is the @-symbol in your controlfile- or in your datafile-name? Anyway: Have you tried to call sqlldr with named parameters ( with and without escaping ) i.e. `sqlldr control=foo.ctl, log=bar.log, bad=baz.bad, data=yourDataFile.dat, userid=scott/tiger` alternative you could try to use a parfile. — evilive, Nov 18 '14 at 06:42

Alex Poole · Accepted Answer · 2014-11-19T14:58:13.497

The @ is being interpreted as the ORACLE_SID value; you get this error if that environment variable is not set. If it is set then the variable value is inserted in the control file name. If your control file is called s@ntosh.ctl then you see:

$ export ORACLE_SID=XE; sqlldr user/password s@ntosh.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Nov 17 16:32:59 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-500: Unable to open file (sXEntosh.ctl)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory

If your control file has a more normal name but the data file is specified with an @ you see:

$ export ORACLE_SID=XE; sqlldr user/password santosh.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Nov 17 16:33:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-500: Unable to open file (/FileWithXESymbol.csv)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Note the @ has simply been substituted with XE in both cases. Since you're seeing this:

SQL*Loader-503: Error appending extension to file (/FileWith@Symbol.csv)

... that implies you do not have ORACLE_SID set at all, presumably because you're accessing the database remotely. Which is handy, because you can temporarily set your ORACLE_SID to the @ symbol so it expands to the same thing:

$ export ORACLE_SID=@; sqlldr user/password santosh.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Nov 17 16:34:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-500: Unable to open file (/FileWith@Symbol.csv)
SQL*Loader-553: file not found

... but that's because I actually don't have a file with that name; with an existing file it works. You can even have multiple @ symbols in the file name.

Of course this workaround isn't helpful for anyone accessing their database locally, since they will be relying on ORACLE_SID being set correctly; unless they can temporarily connect remotely even though they don't normally need to.

Those examples are running SQL*Loader from a shell. From Java you can set the ORACLE_SID at the same time you set the other environment variables:

String[] env = new String[]  
{  
                    "ORACLE_SID=@",
                    "ORACLE_HOME="+oracleHome,
                    "LD_LIBRARY_PATH="+oracleLibPath+":"+oracleLib32Path+":$LD_LIBRARY_PATH"              
};

... and use your original cmd value.

Sorry for the late reply, i was trying to add ORACLE_SID=@ in my list of commands which i am sending to my SqlLoder from my java call. i have not succeeding in doing so. Can you kindly tell me how to provide ORACLE_SID=@ in the list of commands which i am providing to Sql loader please. Kindly look at the code snippets i have added to the Question. Thank you again for your time Alex. — Santosh budhe, Nov 19 '14 at 14:48
@Santoshbudhe - I would think you can just include it as another element in the `env` array? It belongs there, not as part of `cmd`. My example was from a shell, I hadn't realised you were invoking SQL*Loader from Java. — Alex Poole, Nov 19 '14 at 14:55
Thank you very much Alex i used your update above in the environment list and it worked. thank you very much :) — Santosh budhe, Nov 19 '14 at 16:57

Invoking SQLLoader from java, issues loading a file with name that contains @-signs

1 Answers1