CODEWITHSUNDEEP
oracle-databasebashsqlplusora-01017
rsinuhe
rsinuhe

Reputation: 177

Sqlplus login error when using bash variables: SP2-0306: Invalid option

I have a bash script that connects to an oracle 10g database.

In a first step it takes some variables from a "config" file with the following command 

. /path/to/my/configfile.ini

In the config file there are some variables:

export USRID=myUser
export USRID_PASS=myPassword
export USR_PASS="$USRID/$USRID_PASS@myDatabase"

Then it actually connects through sqlplus using the command:

sqlplus -s $usr_pass

Terrible Security and Design issues aside (this script has been around for 5 years). This is actually doing its job in one of our UNIX servers, but not in another.

When I run the script with bash -x, I can see that the command expanded to:

sqlplus -s myUser/myPassword@myDatabase

...which should do fine (and is actually working in one server), but the response in the failing server is:

ERROR: ORA-01017: invalid username/password; logon denied

SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where ::= [/][@] | SP2-0306: Invalid option.

I'm guessing it has to do more with bash than with oracle, but I'm no bash expert. Is there some configuration or detail I'm missing?

EDIT:

Trying to pin down the problem a bit more, I'm now running two versions of the script in a third development server, and in different tests, the login works if i do it with:

sqlplus -s $usrid/$usrid_pass@myDatabase

but not when i try:

sqlplus -s $usr_pass

So its a bit annoying.

Besides that, i'll have to check on te config file synchronization process... I'll let you know when i get to something new. Thanks everybody.

Upvotes: 4

Views: 80666

Answers (5)

Cassidy Brookland
Cassidy Brookland

Reputation: 1

Hi I had this issue too so I went to C:\app*yourcompnamehere*\product\21c\homes\OraDB21Home1\network\admin and then found my listener.org file and i just opened it with notes and it worked :) - potentially your machine isnt aware of it yet

Upvotes: -1

Subrata Nath
Subrata Nath

Reputation: 205

I solved this problem on my mac machine by changing the shell to 'sh' from 'bash'. Everything just worked smooth then.

Thanks to the original reporter of the problem who explained with a good hint.

Upvotes: 0

Darius
Darius

Reputation: 31

This worked for me: connect user/"password" I think if the password contains special characters like '@' we need to use "" for the password.

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

Daft question, but are you sure you are using the bash shell on both unix servers ?

I'd try replacing

export USR_PASS="$USRID/$USRID_PASS@myDatabase"

with export USR_PASS="${USRID}/${USRID_PASS}@myDatabase"

to make sure the variables get interpreted correctly

As a final, exotic though, does the password contain any characters other than basic alpha-numeric and punctuation. Because 10g isn't case sensitive, a lowercase password gets converted to uppercase, which can cause odd effects with things like accented characters

Upvotes: 0

DCookie
DCookie

Reputation: 43523

The message is pretty clear:

  • you've successfully contacted a database
  • the credentials supplied are wrong

This indicates there isn't really anything wrong with your client configuration.

So, that leaves you with

  • the user/pw combination is wrong
  • you've not contacted the database you think you have

Possibilites:

  • Make sure you can connect with the credentials supplied from the command line.
  • Use tnsping mydatabase to check the host and instance you're contacting, verify it's correct. Output from this command should tell you the host, port, and instance/service you're connecting to. If it's wrong, check the tnsnames.ora file for this alias.
  • As @OMG Ponies suggests, if you're using 11g, make sure the case in your passwords is correct

Upvotes: 6

Related Questions