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

Since you are using Unix, you should **NEVER** put the password on the command line. It is a security hole.

Read up on shell programming, particularly the use of "HERE" documents and return values from commands.

Also, as Sybrand pointed out, you really need to address the TNS issue.

--
Ron Reidy
Oracle DBA

samir wrote:
> Hi Thomas,
> 
> Thanks a lot for such a detailed reply. It has really helped. To keep 
> you updated, our operating system is Solaris. Now, as it is clear to me 
> why the error is thrown, I need to implement the your idea of providing 
> username and password for two more times to take care of service failure 
> condition. It will be much better if somehow we can capture the return 
> code from previous try and act accordingly.
> 
> Thanks again for clarifying.
> 
> with regards,
> samir
> 
> Thomas T wrote:
> 


>> "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

>>

>>

>