1 Reply Latest reply on Jul 4, 2015 2:51 AM by Roy Jørgensen

    SQLcl batch mode

    Roy Jørgensen

      Don't know if this is the right place for feature request for SQLcl, but I have a request...

       

      I want a new command line option to run SQLcl in batch mode (in lack of a better term), so that when I add a @myscript.sql to the end of the command line, it runs the script and then exists, no matter what. Even if the last command was not a valid command.

      Would be great to have different exit codes for

      -no errors during the script

      -at least one ORA-error during the script

      -at least one syntax error in the script

      -both ORA-error(s) and syntax error(s) in the script

       

      In addition it would be nice to have a parameter where I could supply a list of ORA-errors that I wanted to be ignored when evaluating the the exit code "at least one ORA-error during the script". Maybe we want an exit code for "only ignorable ORA-errors" in that case?

      I am thinking of something like this:

         sql -b -i 955 -i 942 scott/tiger@mydb @myscript.sql

      -b means batch mode

      -i means ignore this error code.

      I have no strong oppinions of the format of the ignore option. Maybe it should include "ora" as well, in order to also support PLS- errors etc??? Would that make it more "strict" about upper/lower case, and number of leading zeros? "-i ORA-00955", "-i ora-00955", "-i ora-955" etc?

       

      My use case for the batch mode in general is installation scripts. Developers often develop and test scripts in sql developer, toad etc., but the installation might be run in sqplus (or SQLcl).

      If for instance I would run sqlplus/sqlcl as a command from Liquibase that requires that the script ends with exit (see https://liquibase.jira.com/browse/CORE-1621).

       

      My use case for supplying ignorable error codes is for installations that might be run several times. That might lead to the need to ignore errors like:

      ORA-00955 name is already used by an existing object

      ORA-00942 table or view does not exist

      etc.