Reputation: 24184

SQLPlus: using special character for password in script

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.

Upvotes: 6

Answers (3)

Dmitry Demin

Reputation: 2113

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

Upvotes: 0