Oracle sqlldr 是否可以接受 TNS 条目作为 Oracle 10 和 11 中的实例限定符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7409503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible for Oracle sqlldr to accept a TNS entry as an instance qualifier in Oracle 10 and 11?
提问by thetwan
Is it possible to use a fully qualified TNS entry using sqlldr bundled with Oracle 10/11?
是否可以使用与 Oracle 10/11 捆绑在一起的 sqlldr 使用完全限定的 TNS 条目？
For example, in SQLPlus:
例如，在 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 (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'
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.
尝试将 TNS 条目封装在引号中会产生相同的错误。
Had a look at the sqlldr documentation, and attempted to use the 'userid' command-line argument to no avail. Specifically:
查看了 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, 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?
Oracle 希望强制用户使用本地实例以减轻将数据推送到远程主机时的 I/O，这是有道理的。但是支持的语法的偏差并不是那么直观。还有其他人遇到过类似的问题吗？
回答by user331465
fwiw, this guy posted a solution to this problem
fwiw，这家伙发布了解决此问题的方法
http://www.simplemancomplexmachine.com/2011/10/sqlldr-one-liner-to-remote-database.html
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):
是的，有一种单行解决方案，您可以使用 TNS 连接字符串从命令行执行此操作。关键是连接字符串的格式有点不同，因为它必须被引用。此外，必须对引号和括号进行转义（反斜杠）：
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:
请注意，在原始博客文章中，他在“/dbpass”前面有一个空格。这会导致 sqlldr 给出错误：
LRM-00112: multiple values not allowed for parameter 'userid'
LRM-00112: 参数 'userid' 不允许有多个值
回答by Kevin Burton
Have you tried the ezconnect format to connect to a remote db using sqlldr ?
您是否尝试过 ezconnect 格式使用 sqlldr 连接到远程数据库？
e.g:
例如：
sqlldr user/password@//localhost:1521/orcl bad='bad_file.txt' control='control.ctl' data='data.txt' log='log.txt' direct='true'
回答by Senadheera
回答by Shiva
Kelvin is 100 % Correct. It worked for me
开尔文是 100% 正确的。它对我有用
YOU CAN EXECUTE SQL LOADER WITH BELOW COMMAND WITHOUT MAKING A TNS ENTRY
您可以使用以下命令执行 SQL 加载程序，而无需输入 TNS
sqlldr userid/password@//host:port/SERVICE_NAME bad='/PATH/FILENAME.bad' control='/PATH/FILENAME.ctl' data='/PATH/FILENAME.csv' log='/PATH/FILENAME.log' direct='true'
sqlldr userid/password@//host:port/SERVICE_NAME bad='/PATH/FILENAME.bad' control='/PATH/FILENAME.ctl' data='/PATH/FILENAME.csv' log='/PATH/FILENAME.日志'直接='真'
回答by Benoit
You can also try:
你也可以试试：
sqlldr user/password@TNS:(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'
回答by Alex Schr?der
The only thing that worked for me was using two kinds of quotes:
唯一对我有用的是使用两种引号：
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'
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'
回答by leo
Single quoting the whole not-so-ezconnect worked for me in a DOS batch script and on the command line:
在 DOS 批处理脚本和命令行中，单引号整个不那么 ezconnect 对我有用：
sqlldr 'user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))' control='control.ctl' data='data.txt'
