Re: sqlplus behaviour clarification : "could not resolve service name"

"samir" <samirks_at_operamail.com> wrote in message news:3E95C01A.6010909_at_operamail.com...
> Hi,
>
> Even after throwing back following error :
>
> ORA-12154: TNS:could not resolve service name
>
> sqlplus prompts for username/password.
>
> Please let me know why it does so and is there a way to disable it.
> Basically, we don't want to prompt the user for username/password when
> sqlplus client fails to connect to database (we are invoking sqlplus
> from a shell script and providing a .sql file on the command line ).
>
> Thanks and regards,
> samir

Samir,

The reason is to allow the user to choose another service, one that exists. I work on 3 Oracle systems, and if I'm in a hurry, I'll mistype one of the service names. If SQL*Plus kicked me out, I'd have to run it again to get the login... instead, SQL*Plus just asks me again for a correct login.

You could try experimenting with sqlplus /nolog (prevents sqlplus from logging in), but then you need to pass your login information to the SQL*Plus screen.

Hm; what about this? You can login through svrmgrl (Windows 2000/NT, Oracle 8i). If you run:

svrmgrl command="connect username/password_at_service"

from the command line, if the connect string fails, svrmgrl dumps you back at the command prompt. Then again, your user would need svrmgrl.exe access.

I wonder; what about an SQL from svrmgrl? You could try making test.sql, with the following in it:

connect username/password_at_service
spool myconnectionresults
select 'yes' from dual;
spool off
disconnect
exit

And then from your batch file:

svrmgrl command="@test.sql"

And then examine myconnectionresults.log to see if it contains "yes", or, if it contains an error. If the file does say "yes", then you could invoke SQL*Plus from your batch file. OR, skip the "select yes from dual" query completely, and insert your own query... but svrmgrl doesn't have the "wealth" of SQL available to it.

Or, maybe run: svrmgrl command="connect username/password_at_service", and see if it sets an evironment varible (such as Microsoft's ERRORLEVEL) to represent failure?

Is your operating system Windows NT or 2000? You might be able to develop a "quickie" Visual Basic 6 console program (using Sub Main instead of a startup form) that creates an ADO connection to Oracle. You'd use an ADO connection object. After "opening" the connection object, if an error occurred, you could query the ADO.Error object for the error. If an error -is- returned, set an environment variable's value to represent it. Then you could use an "IF (%1)==(text)" statement in your batch file. Of course, if you've created the ADO program, you might want to run the

Just some things off the top of my head... hope they help! Let us know if you find a way around the problem. AFAIK, you can't lower OR increase the number of login attempts to SQL*Plus.

One more "bad" way that works... you've got your script file, right? Put the username/password_at_service at the top, three times. If the first is successful, you just send SQL*PLUS "username/password_at_service" two extra times, it won't know what to do with them, and will display errors, but nobody will be there to see them! (Especially if you run SQLPLUS with silent mode, sqlplus -s @myscript.sql). If the first fails, the next two will fail, and the user won't have any opportunity to type something.

-Thomas Received on Thu Apr 10 2003 - 15:45:28 CDT