Oracle sqlldr 是否可以接受 TNS 条目作为 Oracle 10 和 11 中的实例限定符？

发布于 2024-12-04 13:48:13 字数 1475 浏览 0 评论 0原文

是否可以使用与 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 加载器）直接使用 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, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-100: Syntax error on command-line

Oracle 希望强制用户使用本地实例以减少将数据推送到远程主机时的 I/O，这是有道理的。但支持的语法的偏差并不那么直观。还有其他人遇到类似的问题吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光磨忆 2024-12-11 13:48:13

fwiw，这个人发布了此问题的解决方案

http://www. simplemancomplexmachine.com/2011/10/sqlldr-one-liner-to-remote-database.html

是的，有一个单行解决方案，您可以使用 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

请注意，在原始博客文章中，他在“/dbpass”前面有一个空格。这会导致 sqlldr 给出错误：

LRM-00112：参数“userid”不允许使用多个值

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'

回复收藏 0 原文

魂归处 2024-12-11 13:48:13

您是否尝试过 ezconnect 格式使用 sqlldr 连接到远程数据库？

例如：

sqlldr user/password@//localhost:1521/orcl bad='bad_file.txt' control='control.ctl' data='data.txt' log='log.txt' direct='true'

参见：http://www.orafaq.com/wiki/EZCONNECT

Have you tried the ezconnect format to connect to a remote db using sqlldr ?

e.g:

sqlldr user/password@//localhost:1521/orcl bad='bad_file.txt' control='control.ctl' data='data.txt' log='log.txt' direct='true'

see: http://www.orafaq.com/wiki/EZCONNECT

回复收藏 0 原文

兲鉂ぱ嘚淚 2024-12-11 13:48:13

看起来您需要使用转义字符“\”（即反斜杠）转义“(”、“)”和“=”等字符，如此处。

回复收藏 0 原文

一抹微笑 2024-12-11 13:48:13

您还可以尝试：

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'

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'

回复收藏 0 原文

迷雾森÷林ヴ 2024-12-11 13:48:13

开尔文是 100% 正确的。它对我有用

您可以使用以下命令执行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'

回复收藏 0 原文

停顿的约定 2024-12-11 13:48:13

唯一对我有用的是使用两种引号：

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'

回复收藏 0 原文

仙女 2024-12-11 13:48:13

在 DOS 批处理脚本和命令行中单引号整个不那么 ezconnect 对我有用：

sqlldr 'user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))' control='control.ctl' data='data.txt'

Single quoting the whole not-so-ezconnect worked for me in a DOS batch script and on the command line:

sqlldr 'user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))' control='control.ctl' data='data.txt'

回复收藏 0 原文