sqlldr error in Oracle

Question

Currently, I am using sqlldr userid= {user}/{pass}@{SID} to load the file into database, and this comment is in a bash file.

The password is generated by a third lib framework.

But, I found that sometimes I got the following error,

export TNS_ADMIN=/opt/config/uat/  
export PATH=$PATH:$ORACLE_HOME/bin 
sqlldr  userid=M_UAT/YAu8D=5r@My_UAT  
ERR>LRM-00116: syntax error at 'M_UAT/YAu8D' following '='

I found that there is a special character = in the password,

I checked something, seems sqlplus can avoid this exception by userid= ={user}/\"{pass}\"@{SID}

But sqlldr cannot.

Does anyone have an idea here?

did you try `userid="M_UAT/YAu8D=5r@My_UAT"`? – Jul 02 '18 at 05:51 — , Jul 02 '18 at 05:51

Dmitry Demin · Answer 1 · 2018-07-03T10:06:05.487

sqlplus makes a connection with the username and password in quotes. The sqlloader(sqlldr) connects to the database only if the name is written without quotes. The password can contain @!= characters only in quotation marks, for example

password=\"YAu8D=5r\"
password=\"@!YAu8D=5r@\"

Example 1

username=HR
password=\"YAu8D=5r\"

Change password

oracle@esmd:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 2 13:39:10 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> alter user hr identified by "YAu8D=5r" ;

User altered.

SQL> exit

Test connect sqlplus

#!/bin/sh

username=\"HR\"
password=\"YAu8D=5r\"

echo username:  $username
echo password:  $password

testoutput=$(sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual
@ulcase1.sql
exit;
EOF
)

echo $testoutput


oracle@esmd:~> ./test_Upper_case2.sh
username: "HR"
password: "YAu8D=5r"
USER is "HR"

Test SQL*loader script test_sqlldr.sh

oracle@esmd:~> more test_sqlldr.sh
#!/bin/sh

username=hr
password=\"YAu8D=5r\"

sqlldr userid=$username/$password  control=ulcase2.ctl log=log.log

Test SQL*loader

oracle@esmd:~> ./test_sqlldr.sh

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Jul 2 13:46:29 2018

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

Commit point reached - logical record count 7
oracle@esmd:~>

Test network connection sqlldr

oracle@esmd:~> more test_sqlldr.sh
#!/bin/sh

username=hr
password=\"YAu8D=5r\"

sqlldr userid=$username/$password@esmd  control=ulcase2.ctl log=log.log


oracle@esmd:~> ./test_sqlldr.sh

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Jul 2 13:53:24 2018

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

Commit point reached - logical record count 7

Example 2

username=HR
password=\"@!YAu8D=5r@\"

Script for connection testing sqlplus

#!/bin/sh

username=HR
password=\"@!YAu8D=5r@\"

echo username:  $username
echo password:  $password

testoutput=$(sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@ulcase1.sql
exit;
EOF
)
echo $testoutput

Test connect sqlplus

oracle@esmd:~> ./test_Upper_case2.sh
username: "HR"
password: "@!YAu8D=5r@"
USER is "HR" 03-07-2018 12:44 Test passed

Test SQL*loader script test_sqlldr.sh

#!/bin/sh

username=HR
password=\"@!YAu8D=5r@\"

sqlldr userid=$username/$password  control=ulcase2.ctl log=log.log

Test SQL*loader

oracle@esmd:~> ./test_sqlldr.sh

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Jul 3 12:48:53 2018

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

Commit point reached - logical record count 7

Example 3

 username=\"HR\"
 password=\"@!YAu8D=5r@\"

Script for connection testing sqlplus

#!/bin/sh

username=\"HR\"
password=\"@!YAu8D=5r@\"

echo username:  $username
echo password:  $password

testoutput=$(sqlplus -s $username/$password  << EOF
set pagesize 0 feedback off verify off heading off echo off;
show user
SELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dual;
@ulcase1.sql
exit;
EOF
)

echo $testoutput

Test connect sqlplus

oracle@esmd:~> ./test_Upper_case2.sh
username: "HR"
password: "@!YAu8D=5r@"
USER is "HR" 03-07-2018 12:51 Test passed


oracle@esmd:~> more test_sqlldr.sh
#!/bin/sh

username=\"HR\"
password=\"@!YAu8D=5r@\"

sqlldr userid=$username/$password  control=ulcase2.ctl log=log.log

Test SQL*loader

oracle@esmd:~> ./test_sqlldr.sh
LRM-00112: multiple values not allowed for parameter 'userid'

SQL*Loader: Release 11.2.0.3.0 - Production on Tue Jul 3 12:54:42 2018

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

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

sqlldr error in Oracle

1 Answers1