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

问题描述

是否可以使用与Oracle 10/11捆绑在一起的sqlldr使用完全合格的TNS条目?

Is it possible to use a fully qualified TNS entry using sqlldr bundled with Oracle 10/11?

例如，在SQLPlus中:

For example, in SQLPlus:

    sqlplus user/password@(description=(address=(host=localhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl))) @script.sql

但是使用sqlldr(SQL Loader)时，直接使用TNS条目似乎存在问题.具体来说:

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'

这是产生的错误消息:

    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条目封装在引号中会产生相同的错误.

Attempting to encapsulate the TNS entry in quotes produces the same error.

看看sqlldr文档，并尝试使用'userid'命令行参数无效.具体来说:

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

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

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

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给出错误:

Note that in the original blog post he had a space in front of '/dbpass'. This causes sqlldr to give the error:

LRM-00112:参数'userid'不允许使用多个值

LRM-00112: multiple values not allowed for parameter 'userid'

这篇关于Oracle sqlldr是否可以接受TNS条目作为Oracle 10和11中的实例限定符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！