Is it possible to use a fully qualified TNS entry using sqlldr bundled with Oracle 10/11?
For example, in SQLPlus:
sqlplus user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl))) @script.sql
But using sqlldr (SQL Loader) there appear to be issues with using the TNS entry directly. Specifically:
sqlldr user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl))) bad='bad_file.txt' control='control.ctl' data='data.txt' log='log.txt' direct='true'
Here is the error message produced:
LRM-00116: syntax error at 'address' following '('
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Sep 13 15:41:54 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-100: Syntax error on command-line
Attempting to encapsulate the TNS entry in quotes produces the same error.
Had a look at the sqlldr documentation, and attempted to use the 'userid' command-line argument to no avail. Specifically:
sqlldr userid='user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))' bad='bad.txt' control='control.ctl' data='data.txt' log='log.txt' direct='true'
LRM-00116: syntax error at 'password@(' following '='
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Sep 13 15:44:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-100: Syntax error on command-line
It makes sense that Oracle would hope to coerce the user to a local instance to mitigate I/O in pushing data to a remote host. But the deviation in supported syntax is not so intuitive. Anyone else experience similar issues?
fwiw, this guy posted a solution to this problem
http://www.simplemancomplexmachine.com/2011/10/sqlldr-one-liner-to-remote-database.html
Yes there is a one-line solution and you can use a TNS connect string to do this from the command line. The key is formatting the connection string a little different as it must be quoted. Additionally the quotes and parentheses must be escaped (backslashes):
sqlldr userid=dbuser@\"\(description=\(address=\(host=remote.db.com\)\(protocol=tcp\)\(port=1521\)\)\(connect_data=\(sid=dbsid\)\)\)\"/dbpass control=controlfilename.ctl data=data.csv
Note that in the original blog post he had a space in front of '/dbpass'. This causes sqlldr to give the error:
LRM-00112: multiple values not allowed for parameter 'userid'