SQLPlus: using special character for password in script

Question

I have issue with special character in password - '@'. Issue that i don't know how to pass password with this special character to script file and connect using sqlplus.

For example i have user 'test_user' with password 'temp123@'. For login via sqlplus i use following string with escaping: sqlplus test_user/\"temp123@\"@some-db:1521/SID

It's works well. For my case i need to pass password as parameter for sql script. For example, i have following script to connect (actually script using several pairs of login/password to update many users at once):

-- @deploy.sql
connect &&2./&&3.@&&1.

At have failure with that because password with '@' can't interpreted correctly by sqlplus. I tried many advices from google with escaping but mostly of them applicable for connect string at sqlplus invoke. For my case its required for parameter that passed to script.

Mikemikel

Answer 1

use : cmd.exe and not powershell window

if not : 

in powershell windows : sqlplus 'login/\"P@$$w0rd\"@TNS_NAME'

in cmde.exe : sqlplus login/\"P@$$w0rd\"@TNS_NAME

Nereidanereids

Answer 2

Just quotes required in connect string:

connect &&2./"&&3."@&&1.

Mikemikel

Answer 3

alter user scott identified by "T@!ger";

powershell script .\test-connect.ps1

    $username = "scott"
    $password = '\"T@!ger\"'
    $connect_string ="DEV19"
    $list_scripts = "select 1 ,sysdate from dual ;"
    $list_scripts | ./sqlplus   $username/$password@$connect_string; 


    C:\instantclient_19_15>powershell .\test-connect.ps1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                


C:\instantclient_19_15>powershell 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 3 16:57:38 2024 
Version 19.15.0.0.0 Copyright (c) 1982, 2021, Oracle.  All rights reserved.      
   
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
   
Last Successful login time: Wed Jul 03 2024 16:57:27 
    
Connected to:  
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production  
Version 19.12.0.0.0 

1          SYSDATE    
---------- --------- 
1          03-JUL-24 
SQL> 
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0 

                                  

Curfew