是否可以使用与Oracle 10/11捆绑的sqlldr使用完全限定的TNS条目？
例如,在sqlPlus中：
sqlplus user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl))) @script.sql
但是使用sqlldr(sql Loader)似乎直接使用TNS条目存在问题.特别：
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'
这是产生的错误消息：
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
尝试将TNS条目封装在引号中会产生相同的错误.
看一下sqlldr文档,并尝试使用’userid’命令行参数无济于事.特别：
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,Oracle and/or its affiliates. All rights reserved. sql*Loader-100: Syntax error on command-line
有意义的是,Oracle希望将用户强制转换为本地实例,以减轻将数据推送到远程主机的I / O.但支持语法的偏差并不那么直观.其他人遇到过类似问题吗？
fwiw,这家伙发布了这个问题的解决方案
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
请注意,在原始博客文章中,他在’/ dbpass’前面有一个空格.这会导致sqlldr给出错误：
LRM-00112：参数’userid’不允许多个值
