Running SQL*Loader from Java without tnsnames.ora

Question

I'm trying to run SQL*Loader through Java on Unix.

I'm using the following command and it works when I run it on the command line (bash):

sqlldr userid=user/pass@\"\(DESCRIPTION = \(SDU = xxxx\) \(TDU = xxxx\) \(ADDRESS_LIST = \(ADDRESS = \(PROTOCOL = TCP\)\(HOST = xxxxx\)\(PORT = xxxx\)\) \(LOAD_BALANCE = on\) \(FAILOVER = on \) \)\(CONNECT_DATA = \(SERVICE_NAME = xxxx\) \)\)\" control='xxxx' log='xxxx' readsize=xxxx streamsize=xxxx 

I'm using escaping in order to run it without a tnsnames.ora file.

This is how I translated that in my Java code:

String unixCmd = "sqlldr userid=user/pass@\\\"\\(DESCRIPTION = \\(SDU = xxxx\\) \\(TDU = xxxx\\) \\(ADDRESS_LIST = \\(ADDRESS = \\(PROTOCOL = TCP\\)\\(HOST = xxxx\\)\\(PORT = xxxx\\)\\) \\(LOAD_BALANCE = on\\) \\(FAILOVER = on \\) \\)\\(CONNECT_DATA = \\(SERVICE_NAME = xxxx\\) \\)\\)\\\" control='xxxx' log='xxxx' readsize=xxxx streamsize=xxxx";
System.out.println(unixCmd); 
p = Runtime.getRuntime().exec(unixCmd);//not working
p = Runtime.getRuntime().exec("sh -c "+unixCmd);//not working 

I'm getting the following error message:

LRM-00116: syntax error at 'user/pass' following '='
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Jun 1 11:29:59 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

What am I doing wrong?

Answer 1

You are over-escaping. In a shell script you need to escape the parentheses because you don't want the shell itself to interpret those; within a Java string you don't:

String unixCmd = "sqlldr userid=user/pass@\"(DESCRIPTION = (SDU = xxxx) (TDU = xxxx) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = xxxx)) (LOAD_BALANCE = on) (FAILOVER = on ) )(CONNECT_DATA = (SERVICE_NAME = xxxx) ))\" control='xxxx' log='xxxx' readsize=xxxx streamsize=xxxx";
System.out.println(unixCmd); 
p = Runtime.getRuntime().exec(unixCmd);

Only the double-quotes need to escaped. With this code it doesn't get the LRM-00116. With your dummy values it will still error of course - initially on LRM-00104: 'xxxx' is not a legal integer for 'readsize', but putting in real values shows it's working as expected.