OraFAQ Forum: Networking and Gateways » Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled

Home » RDBMS Server » Networking and Gateways » Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled [message #16106]

Tue, 09 November 2004 22:23

Peter Franken
Messages: 5
Registered: November 2004

Junior Member

I have a small SQL scheduled to run in SQLPlus by the Windows Task Scheduler to count active users every minute. Sometimes it hangs because there is a hickup in our connection (TNS Names could not resolve). The windows scheduler does not terminate it.

Is there a way to adapt the script to test the connection first ? Or a commandline option to have SQLPlus terminate itself rather thang hang on errors ?

Thanks,

Peter

Report message to a moderator

Re: Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled [message #16108 is a reply to message #16106]

Wed, 10 November 2004 00:07

Frank
Messages: 7901
Registered: March 2000

Senior Member

Sounds like a bad idea.
Creating sessions (that's what you do EVERY minute!!) is a very expensive thing to do.

Maybe dbms_job can do the trick for you:
Create a table to store the information gathered and run the script every x minutes using dbms_job.
Another option is to create an infinite loop with a dbms_lock.sleep call.

hth

Report message to a moderator

Re: Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled [message #16109 is a reply to message #16108]

Wed, 10 November 2004 00:55

Peter Franken
Messages: 5
Registered: November 2004

Junior Member

I understand that is is expensive on the connection issue. But it's a small script and a fat machine, so I should be OK.

And I don't have the option to add tables or procs to the database. I must do it externally.

I've been tinkering with the options
whenever sqlerror exit 0
But this comes too late, as the call of sqlplus already makes the connection.

So I'm still looking for solutions !

Report message to a moderator

Re: Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled [message #16121 is a reply to message #16109]

Wed, 10 November 2004 04:47

Frank
Messages: 7901
Registered: March 2000

Senior Member

The following command-line parameter stops SQL*Plus if the first logon does not succeed:
"
-L[[OGON]]

Specifies not to reprompt for username or password if the initial connection does not succeed. This can be useful in operating system scripts that must either succeed or fail and you don't want to be reprompted for connection details if the database server is not running. This option is not available with the Windows graphical user interface SQL*Plus executable.
"

So: sqlplus -l scott/tiger@db

hth

Report message to a moderator

Re: Abort SQLPlus on TNS error ? Called from commandline, hanging, scheduled [message #120098 is a reply to message #16121]

Tue, 17 May 2005 14:57

afmacdonald
Messages: 1
Registered: May 2005

Junior Member

The - l option doesn't appear to be avialable in 9i (specifically SQL*Plus: Release 9.0.1.3.0).

I tried:
sqlplus -s -l userid/password@database script_I_want_to_run.sql
Error message
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

I am executing SQLPLUS in a batch process, passing the userid/password@database on the command line. I want SQLPLUS to error if this is incorrect. Currently it hangs waiting for another signin attempt.

Report message to a moderator

Previous Topic:	Connecting to a remote computer w Oracle via internet, error Timeout
Next Topic:	how to transfer spatial data from one database to remote database by database link

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 14:13:05 CDT 2025