Shell Script with sqlplus and special characters on password

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

NAMES.DIRECTORY_PATH= (TNSNAMES,ezconnect)

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

oracle@esmd:~>oracle@esmd:~> sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:05:04 2018Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Release 11.2.0.3.0 - 64bit ProductionSQL> alter user "Scott" identified by "@T!ger";User altered.

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@$ezconnectoracle@esmd:~> ./test_echo.shusername: "Scott"password: "@T!ger"ezconnect 10.89.251.205:1521/esmdSQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 29 11:02:52 2018Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Release 11.2.0.3.0 - 64bit ProductionSQL> USER is "Scott"SQL>         1----------         1SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Example 2 Run script test_echo.sh in silent mode sqlplus

#!/bin/shusername=\"Scott\"password=\"@T!ger\"ezconnect=10.89.251.205:1521/esmdecho username:  $usernameecho password:  $passwordecho ezconnect  $ezconnectecho -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus -s  $username/$password@$ezconnectoracle@esmd:~> oracle@esmd:~> ./test_echo.shusername: "Scott"password: "@T!ger"ezconnect 10.89.251.205:1521/esmdUSER is "Scott"         1----------         1

Example 3 A little bit Another syntax

#!/bin/shusername=\"Scott\"password=\"@T!ger\"ezconnect=10.89.251.205:1521/esmdecho username:  $usernameecho password:  $passwordecho ezconnect: $ezconnecttestoutput=$(sqlplus -s $username/$password@$ezconnect  << EOFset pagesize 0 feedback off verify off heading off echo off;show userSELECT to_char(sysdate,'DD-MM-YYYY HH24:MI')||' Test passed' from dualexit;EOF)echo $testoutputoracle@esmd:~> ./test_Upper_case.shusername: "Scott"password: "@T!ger"ezconnect: 10.89.251.205:1521/esmdUSER is "Scott" 29-01-2018 11:55 Test passed

i assume you issued this to alter your user's password :

alter user johnF identified by "!p@ssword";  

since

alter user johnF identified by !p@ssword;  

doesn't conforms oracle password definition rules.

and then it's enough to write such a script in your file to connect your schema :

#!/bin/bash# cnn.shline '"!p@ssword"'echo linesqlplus johnF/$line@127.0.0.1:1521/yourSID

and call from prompt :

$ . cnn.sh

I've encountered the same problem here as well (which really drives me crazy), and this is my answer.

All the special characters allowed in Oracle could be found on this page:https://docs.oracle.com/cd/E11223_01/doc.910/e11197/app_special_char.htm#MCMAD416

1. If your Oracle password contains any of the special characters on the above page except for a single quotation mark. #o@%+!$(){}[]/^?:`~ for example.

You can use it like this:

sh test.sh oracle_user '"#o@%+!$(){}[]/\^?:`~"' ip port service_name

2. If your Oracle password contains a single quotation mark. #o@%+!$(){}[]/^?:`~' for example.

You can use it like this:

sh test.sh oracle_user '"#o@%+!$(){}[]/\^?:`~'"'"'"' ip port service_name

Please be noted that the single quotation mark ' should be replaced with '""'.