当前，我正在使用sqlldr userid= {user}/{pass}@{SID}将文件加载到数据库中，并且此注释位于bash文件中。
密码是由第三个lib框架生成的。
但是，我发现有时会出现以下错误，
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 '='
我发现密码中有一个特殊字符
=，我检查了一下，看来sqlplus可以通过
userid= ={user}/\"{pass}\"@{SID}避免此异常但是sqlldr不能。
有人在这里有个主意吗？
最佳答案
sqlplus与引号中的用户名和密码建立连接。仅当名称不带引号时，sqlloader(sqlldr)才连接到数据库。
密码只能在引号中包含@!=字符，例如
password=\"YAu8D=5r\"
password=\"@!YAu8D=5r@\"
示例1
username=HR
password=\"YAu8D=5r\"
更改密码
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
测试连接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"
测试SQL * loader脚本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
测试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:~>
测试网络连接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
示例2
username=HR
password=\"@!YAu8D=5r@\"
用于连接测试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
测试连接sqlplus
oracle@esmd:~> ./test_Upper_case2.sh
username: "HR"
password: "@!YAu8D=5r@"
USER is "HR" 03-07-2018 12:44 Test passed
测试SQL * loader脚本test_sqlldr.sh
#!/bin/sh
username=HR
password=\"@!YAu8D=5r@\"
sqlldr userid=$username/$password control=ulcase2.ctl log=log.log
测试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
示例3
username=\"HR\"
password=\"@!YAu8D=5r@\"
用于连接测试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
测试连接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
测试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
关于oracle - Oracle中的sqlldr错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51128809/