SQLPLUS and SQLCL should have a batch mode to make it crash instead of hang

dirkvanhaute

Sqlplus and sqlcl are widely used for batch access to the database, in a service process running somewhere in the background.

They already have an option to fail after the first login attempt : -L

Otherwise, after a bad password, you would hang forever if you did not provide any input.

But also for other failures, an input is requested.  E.g. when you accidentally have substitutions who are not yet resolved.

These kinds of hangs are difficult to find, because you expect some database lock or slowness instead.

On fora, you will find a solution in redirecting the input from a file.
But it would be better if there is an command line option to put sqlplus or sqlcl in batch mode, that makes it fail when sqlplus is going to read from stdin.

Sven W.

There are already many options to influence the behaviour of SQLPLUS.
It is not clear what issue you encountered, but it seems very unlikely that you are the first one.

Like

WHENEVER OSERROR EXIT
WHENEVER SQLERROR CONTINUESET DEFINE ON/OFF

any many more

There also is the silent mode option which should be used for batch jobs. But this is more because term output is then not shown on the screen and SQLplus runs much faster.

sqlplus -s

For batch jobs you would put your logic into a script file and call this script file. No need to read from STDIN.

sqlplus -s user/[email protected] @runthisfile.sql

There are also ways to avoid the connect string in the command line, which would be much better securitywise.

dirkvanhaute

There are already many options to influence the behaviour of SQLPLUS.
It is not clear what issue you encountered, but it seems very unlikely that you are the first one.

Like

WHENEVER OSERROR EXIT
WHENEVER SQLERROR CONTINUESET DEFINE ON/OFF

any many more

There also is the silent mode option which should be used for batch jobs. But this is more because term output is then not shown on the screen and SQLplus runs much faster.

sqlplus -s

For batch jobs you would put your logic into a script file and call this script file. No need to read from STDIN.

sqlplus -s user/[email protected] @runthisfile.sql

There are also ways to avoid the connect string in the command line, which would be much better securitywise.

I know the various options too, and I agree they are needed in background scripts.

Using the SET DEFINE OFF is not always an option, since it disables important functionality.

But substitution is often the cause of hangs during background processing.

These are the situations which I encountered in my daily life

• The substitute character (&) unexpectedly appearing in the script, leaves the process waiting for a replacement value
• No exit at the end of the script ( should this cause a hang, seriously ? )
  This is even the case if you put your code in runthisfile.sql

I agree, if you are careful, set all the correct options and test everything in interactive mode first, you will not have hangs.

But being able to tell sqlplus: "Hey don't ask for input whenever I do something wrong" looks as a better tool to me.

Shouldn't sqlplus make things easier for developers ?