CODEWITHSUNDEEP
linuxoracle-databasesqlplusoracle19c
Nacros
Nacros

Reputation: 11

How to use password containing @ with sqlplus 18, 19 or 21 on linux?

I have ORACLE account with at sign (@) into password.

I was using sqlplus 12 and I can handle this special character with this syntax :

sqlplus 'USER/\"@PWD\"@SERVER:1521/BASE'

Using now sqlplus 19, this doesn't work anymore. And after many tests (sqlplus v18 v19 and v21) on multiple OS (RHEL7 RHEL8 CentOS7), the problem is the same. I've tested a lot of escape methods and search all over internet without finding anything that works.

The problem seems specific to @ sign, I can escape a ! with a \ for example.

EDIT : I need to automate this, so solution with human interaction are not solution to my problem.

Upvotes: 1

Views: 13585

Answers (3)

Rafal Wojcicki
Rafal Wojcicki

Reputation: 21

This method requires user interactions, so it is not an answer to this topic, but it is yet another way of providing a password with @ sign. I used it as a workaround in my case where I couldn't use other answers, so I thought it might be useful for somebody as well.

Execute sqlplus without a password:

sqlplus <user>@<server>

SqlPlus prompts for the password:

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Feb 15 16:50:10 2024
Version 21.13.0.0.0

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

Enter password:

Wrap up your password with double quotes here, i.e., if your password is [password] then type "[password]"

I tested it on Windows and using SQL*Plus version 21.13.0.0.0

Upvotes: 1

Nacros
Nacros

Reputation: 11

Based on @Roberto Hernandez's answer, I've used his solution modified a bit to work inside script without the need to type password interactively :

sqlplus /nolog << EOF\nconn <user>/"<password>"@<server>:1521/<base>\n@"<script>"

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

You can overcome this issue by connecting inside sqlplus , which by the way is much better if you want to encapsulate the connection inside a shell script.

Let me show you ( Oracle 19c over Red Hat 7 )

SQL> select version from v$instance ;

VERSION
-----------------
19.0.0.0.0

SQL> create user test identified by "Ora@xde1" ;

User created.

SQL> grant connect to test ;

Grant succeeded.

If you connect in command line, it does not work

sqlplus test/"Ora@xde1"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 10 13:07:28 2021
Version 19.3.0.0.0

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

But, if you connect inside, it does work

sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 10 13:08:05 2021
Version 19.3.0.0.0

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

SQL> conn test/"Ora@xde1"
Connected.
SQL> select 1 from dual ;

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

Upvotes: 5

Related Questions