This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

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

dirkvanhaute
dirkvanhaute Member Posts: 27 Bronze Badge
edited Feb 5, 2020 9:05AM in Database Ideas - Ideas

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.

dirkvanhauteamadis
6 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Feb 5, 2020 9:30AM

    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
    dirkvanhaute Member Posts: 27 Bronze Badge

    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 ?