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

Question

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.

What version of Oracle are you connecting to? As of 11g, credentials became case sensitive -- prior to that, they weren't. — OMG Ponies
– OMG Ponies, Commented Jul 1, 2011 at 16:38
Does it only fail from the script? I.e., can you connect from the command line? — DCookie
– DCookie, Commented Jul 1, 2011 at 16:47
That looks an awful lot like a database error, not bash. Are you sure you're connecting to the correct instance of your db? — glenn jackman
– glenn jackman, Commented Jul 1, 2011 at 16:53
The oracle version is 10g, i do can connect from command line. And now that i'm running both tests in a single server i'm sure they both go to the same instance of DB. No luck yet, but i'll let you know. Thanks! — rsinuhe
– rsinuhe, Commented Jul 2, 2011 at 0:07

DCookie · Accepted Answer · 2011-07-01 17:56:02Z

6

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

edited Jul 1, 2011 at 17:56

answered Jul 1, 2011 at 16:57

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darius · Accepted Answer · 2018-01-01 01:54:27Z

3

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

answered Jan 1, 2018 at 1:54

Darius

311 silver badge2 bronze badges

1 Comment

Nick Perkins Over a year ago

If the password contains spaces, use double-quotes around it

Gary Myers · Accepted Answer · 2011-07-02 04:55:48Z

0

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

answered Jul 2, 2011 at 4:55

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Comments

Subrata Nath · Accepted Answer · 2019-11-06 18:26:07Z

0

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.

answered Nov 6, 2019 at 18:26

Subrata Nath

2052 silver badges3 bronze badges

Comments

Cassidy Brookland · Accepted Answer · 2024-04-26 06:57:38Z

-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

answered Apr 26, 2024 at 6:57

Cassidy Brookland

1

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related