具有sqlplus和密码特殊字符的Shell脚本 [英] Shell Script with sqlplus and special characters on password

问题描述

我有一个问题，混合使用Linux/Unix shell脚本和sqlplus(Oracle)，这使我发疯.:-)

I have a question that mix Linux / Unix shell-scripting and sqlplus (Oracle) that is driving me crazy. :-)

sqlplus使用如下语法:

sqlplus utilize a syntax like this:

./sqlplus johnF/mypassword@127.0.0.1:1521/SID

它工作正常.但是，我的密码不是简单的"mypassword"，而是使用！".和"@"，有时甚至是"\".对于此示例，假设我的密码是！p @ ssword

And it works fine. However my password is not simple as "mypassword", it utilize "!" and "@" and sometimes even "\". For this example, let's suppose that my password is !p@ssword

如果我在sqlplus中使用以下语法，则可以使用:

If I use the following syntax in sqlplus it works:

./sqlplus johnF/'"!p@ssword"'@127.0.0.1:1521/SID

太好了.但是我想在一个shell脚本中使用它，该脚本可以调用sqlplus并从文件中获取许多参数(用户名，密码，SID和SQL QUERY)，例如，让我使用简化的代码即可.

That's great. However I wanted to use it in a shell script that get call sqlplus and get many parameters from files (username, password, SID and SQL QUERY), just for example let me use a reduced code.

#!/bin/bash

while IFS=: read -r line
do

        echo "./sqlplus johnF/$line@127.0.0.1:1521/SID" 
        echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/$line@127.0.0.1:1521/SID

done < $1

我尝试了多种修复方式，包括:

I have attempted to fix it in many ways, including:

echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/'"$line"'@127.0.0.1:1521/SID
echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/'\"$line\"'@127.0.0.1:1521/SID
echo -e 'select 1 from dual;\nexit;' |  ./sqlplus johnF/\'\"$line\"\'@127.0.0.1:1521/SID

还有许多其他所有方法都失败了，在某些情况下，第一个回显会完全按照应将其输出传递给sqlplus的方式输出输出，但它永远无法正常工作，返回登录被拒绝(错误的密码)或连接问题(也许@被截获)作为错误的目标).

And many others and all fails, in a few cases the first echo print the output exactly as it should be passed to sqlplus, but it never works, returns login denied (wrong password) or connection issues (maybe the @ being intercepted as wrong target).

如何解决这个难题?

谢谢.

推荐答案

配置配置文件 sqlnet.ora 以便于连接.

Configure the config file sqlnet.ora for an easy connection.

NAMES.DIRECTORY_PATH= (TNSNAMES,ezconnect)

将密码@T！ger更改为用户"Scott".

Change the password @T!ger to the user "Scott".

oracle@esmd:~>
oracle@esmd:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:05:04 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 "Scott" identified by "@T!ger";

User altered.

示例1 脚本为test_echo.sh

Example 1 Script is test_echo.sh

    #!/bin/sh

    username=\"Scott\"
    password=\"@T!ger\"
    ezconnect=10.89.251.205:1521/esmd

    echo username:  $username
    echo password:  $password
    echo ezconnect  $ezconnect

 echo -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus  $username/$password@$ezconnect

oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
ezconnect 10.89.251.205:1521/esmd

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:02:52 2018

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


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

SQL> USER is "Scott"
SQL>
         1
----------
         1

SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

示例2 以静默方式sqlplus运行脚本test_echo.sh

Example 2 Run script test_echo.sh in silent mode sqlplus

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
ezconnect=10.89.251.205:1521/esmd

echo username:  $username
echo password:  $password
echo ezconnect  $ezconnect
echo -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus -s  $username/$password@$ezconnect

oracle@esmd:~> oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
ezconnect 10.89.251.205:1521/esmd
USER is "Scott"

         1
----------
         1

示例3 另一种语法

#!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
ezconnect=10.89.251.205:1521/esmd


echo username:  $username
echo password:  $password
echo ezconnect: $ezconnect

testoutput=$(sqlplus -s $username/$password@$ezconnect  << 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
exit;
EOF
)

echo $testoutput

oracle@esmd:~> ./test_Upper_case.sh
username: "Scott"
password: "@T!ger"
ezconnect: 10.89.251.205:1521/esmd
USER is "Scott" 29-01-2018 11:55 Test passed

这篇关于具有sqlplus和密码特殊字符的Shell脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！