Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sqlplus behaviour clarification : "could not resolve service name"

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

From: samir <samirks_at_operamail.com>
Date: Thu, 10 Apr 2003 14:47:06 -0700
Message-ID: <3E95E65A.2070903@operamail.com>


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
>
>
Received on Thu Apr 10 2003 - 16:47:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US