Skip to Main Content
  • Questions

  • Connecting to SQLLDR using quoted username

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nancy.

Asked: October 01, 2015 - 11:43 am UTC

Last updated: October 02, 2015 - 12:48 am UTC

Version: 11g, 12c

Viewed 10K+ times! This question is

You Asked

Environment: Oracle 11.2.0 Servers and Clients; Oracle 12.1.0 Servers and Clients.

We have a need to logon to Oracle Server using mixed cased usernames.
This worked fine with SQL*Plus using escaped double quotes on both Windows and Unix operating systems:

sqlplus \"MyUserName\"/MyPwd@MyServerName

But no such luck with SQL*Loader. The following showed different error messages with different attempts:

1) When double quotes were escaped then the first parameter becomes multi-valued:
sqlldr \"MyUserName\"/MyPwd@MyServerName control=MyLocalDataFile.ctl

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

sqlldr userid=\"MyUserName\"/MyPwd@MyServerName

then at the prompt of control file enter the control file name
LRM-00112: multiple values not allowed for parameter 'userid'

2) Double quote the entire username/pwd@instanceName:
sqlldr \"MyUserName/MyPwd@MyServerName\" control=MyLocalDataFile.ctl

SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied

3) Taking out the escape backslash:
sqlldr userid="MyUserName"/MyPwd@MyServerName

SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied

In one user forum I read that SQL*Loader unlike SQL*Plus, it requires single quote instead of double quote for control file path, so I thought I would try the username in single quotes;
4) 
sqlldr \'MyUserName\'/MyPwd@MyServerName control=MyLocalDataFile.ctl

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

sqlldr 'MyUserName'/MyPwd@MyServerName control=MyLocalDataFile.ctl

SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied

sqlldr 'MyUserName/MyPwd@MyServerName' control=MyLocalDataFile.ctl

SQL*Loader-128: unable to begin a session
ORA-01017: invalid username/password; logon denied

sqlldr \'MyUserName/MyPwd@MyServerName\' control=MyLocalDataFile.ctl

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error

5) using combination of single quote and double quote:
sqlldr "'MyUserName'"/MyPwd@MyServerName control=MyLocalDataFile.ctl

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

sqlldr '"MyUserName"'/MyPwd@MyServerName control=MyLocalDataFile.ctl

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

I'm running out of ideas.
Any help is appreciated!

Regards, Nancy

and Connor said...

How about a parfile ?, eg

SQL> create user "hello" identified by "there";

User created.

SQL> grant create session to "hello";

Grant succeeded.


then the following in a parfile

userid=\"hello\"/\"there\"
control=x.ctl


That worked for me on Windows 12.1.0.2

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nancy Shen, October 01, 2015 - 1:18 pm UTC

Thank you, Connor!
Please bear with me since I have never used a PARFILE before:
Right now I'm using two SQLLDR statements to load two datafiles into two tables using two control files.
Do I need two PARFILE, one for each table load? That might be the simplest solution and easier to maintain in the future.
Please comment.
Thank you.
Regards, Nancy
Connor McDonald
October 02, 2015 - 12:48 am UTC

A parfile is simply a means to hold all the things you might specify on the command line.

Hence if you used to run:

sqlldr control=... log=... userid=... direct=... silent=... bad=...

you just put all of that into a text file, ie,

control=...
log=...
userid=...
direct=...
silent=...
bad=...

and then run

sqlldr parfile=my_par_file.txt

one parameter file for each different control file.

Rajeshwaran Jeyabal, October 01, 2015 - 3:07 pm UTC

Yes one parameter file for each different control file.
C:\Users\179818>
C:\Users\179818>sqlldr rajesh/oracle@ora10g parfile=d:\par1.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 1 20:34:04 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table T1:
  4 Rows successfully loaded.

Check the log file:
  d:\log1.txt
for more information about the load.

C:\Users\179818>sqlldr rajesh/oracle@ora10g parfile=d:\par2.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Oct 1 20:34:34 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table T2:
  3 Rows successfully loaded.

Check the log file:
  d:\log2.txt
for more information about the load.

C:\Users\179818>type d:\par1.txt
control=d:\ctl1.txt data=d:\data1.dat log=d:\log1.txt
C:\Users\179818>type d:\par2.txt
control=d:\ctl2.txt data=d:\data2.dat log=d:\log2.txt
C:\Users\179818>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.