Is it possible that Oracle sqlldr will accept the TNS record as an instance qualifier in Oracle 10 and 11?

Question

Is it possible to use a fully qualified TNS record 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 are problems with the direct use of the TNS record. In particular:

  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:

  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 record in quotation marks causes the same error.

I looked at the sqlldr documentation and tried to use the 'userid' command line argument to no avail. In particular:

 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 hopes to force the user to a local instance to mitigate I / O when transferring data to a remote host. But the deviation in the supported syntax is not so intuitive. Does anyone else face similar issues?

Answer 1

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 the TNS connection string to do this from the command line. The key formats the connection string is slightly different since it should be specified. In addition, quotes and brackets must be escaped (backslash):

 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 he had a place in front of '/ dbpass'. This causes sqlldr error:

LRM-00112: multiple values ​​are not valid for the 'userid' parameter

Answer 2

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

eg:

 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

Answer 3

It looks like you need to avoid characters like (,) and = with the escape character \, as described here

Answer 4

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' 

Answer 5

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'

Answer 6

Single quoting the whole not-so-ezconnect worked for me in the DOS script package 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' 

Answer 7

Kelvin is 100% fixed. It worked for me

YOU CAN PERFORM SQL DOWNLOAD WITH THE BELOW TEAM WITHOUT GETTING TNS INPUT

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 '