| 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
|
|
|
|
|
|
| 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 !
|
|
|
|
| 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
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
