2 Replies Latest reply on Apr 24, 2018 7:17 PM by srg

    sql.bat not found in sqlcl-18.1.0 download


      I've downloaded the sqlcl-18.1.0.zip file for use on Windows. In previous versions of sqlcl I've used a custom command (bat) file that calls the command file (sql.bat) supplied by Oracle. In this version the bat file appears to be missing, though there is a bash script. Has the sql.bat file been deprecated in favor of sql.exe? For what its worth, I like the bat file approach because I can more easily customize the sqlcl execution environment... There appear to be some significant name changes in the libraries that affect how sqlcl is invoked.




        • 1. Re: sql.bat not found in sqlcl-18.1.0 download
          Glen Conway

          If sql.bat is no longer present in either the SQLcl standalone or SQL Developer IDE zip files, then that is a pretty strong hint to use sql.exe .


          You may recall how in the 17.4 release sql.bat was broken Errors in sql.bat


          If you start with a fixed up sql.bat version based on that discussion, then these changes take into account the library name changes:


          < SET CPFILE=%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar;%CPFILE%


          > SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%


          < SET CPFILE=%SQL_HOME%\lib\xmlparserv2.jar;%CPFILE%


          > SET CPFILE=%SQL_HOME%\lib\xmlparserv2-sans-jaxp-services.jar;%CPFILE%


          < SET CPFILE=%SQL_HOME%\lib\oracle.dbtools-common.jar;%CPFILE%


          > SET CPFILE=%SQL_HOME%\lib\dbtools-common.jar;%CPFILE%


          < SET CPFILE=%SQL_HOME%\lib\oracle.dbtools.http.jar;%CPFILE%


          > SET CPFILE=%SQL_HOME%\lib\dbtools-http.jar;%CPFILE%


          < SET CPFILE=%SQL_HOME%\lib\oracle.dbtools.jdbcrest.jar;%CPFILE%


          > SET CPFILE=%SQL_HOME%\lib\dbtools-jdbcrest.jar;%CPFILE%


          In addition, there seems to be a line with a typo where CLASSPATH is used rather than CPFILE:


          <       SET CPFILE=%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar;%CLASSPATH%) ELSE (SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\ojdbc8.jar;%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar;%CLASSPATH%


          >       SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%) ELSE (SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\ojdbc8.jar;%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%

          But even if you do all that, it is still possible to get the following error on the first or subsequent calls to sql.bat:

          The input line is too long.

          The syntax of the command is incorrect.

          which is a Windows Cmd or PowerShell limitation (see https://stackoverflow.com/questions/16821784/input-line-is-too-long-error-in-bat-file)

          with the simple workaround being to open a new command window.  Best to add "setlocal" as the second line of the script after the initial line "@echo off".


          So I recommend just using sql.exe to avoid this kind of headache!

          • 2. Re: sql.bat not found in sqlcl-18.1.0 download


            Thanks for taking the time to work through a possible solution to this issue. In fact, the additive process of listing each jar in the class path does create an input line that is too long for the command parser. So I've used a different approach to solving the problem - simply including all (*) of the jar files in the \lib folder. As one can determine by a fairly simple search, sql.bat has been recommended as "the" solution for a number of problems when sql.exe wouldn't work. Also, in the interests of pushing boundaries, I wanted to use java 9, which sql.exe will not seem to find.


            In rewriting the bat file, I've attempted to retain as many of the original folder and class path building commands as possible - in slightly modified form, because it isn't apparent as to how their exclusion might affect some installations. If those environments actually exist, they could conceivably trigger an "input line too long" error as well.


            Here is my version of the bat file - other interested parties be warned: your mileage may vary!



            @echo off

            setlocal enableextensions


            REM Switch codepage to UTF

            chcp 65001  >nul 2>&1


            SET SQL_HOME=%~dp0..


            REM If java home is not set, this is an opportunity to set it.

            if not defined JAVA_HOME (

                SET JAVA_HOME=C:\Program Files\Java\jdk-9.0.4



            if exist %JAVA_HOME%\ SET PATH=%JAVA_HOME%\bin;%PATH%;


            rem <! Use all jar files in lib -->

            set CPFILE=%SQL_HOME%\lib\*;%CPFILE%


            rem <!-- OH dependencies we will want to take onboard -->

            if exist %SQL_HOME%\modules\ SET CPFILE=%SQL_HOME%\modules\oracle.xdk\xmlparserv2.jar;%CPFILE%

            if exist %SQL_HOME%\rdbms\ SET CPFILE=%SQL_HOME%\rdbms\jlib\xdb6.jar;%CPFILE%

            if exist %SQL_HOME%\rdbms\ (

            SET CPFILE=%SQL_HOME%\jlib\orai18n.jar;%CPFILE%

            SET CPFILE=%SQL_HOME%\jlib\orai18n-mapping.jar ;%CPFILE%

            SET CPFILE=%SQL_HOME%\jlib\orai18n-utility.jar;%CPFILE%


            if exist %SQL_HOME%\jdbc\ (

            SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc6.jar;%CPFILE%

            SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc7.jar;%CPFILE%

            SET CPFILE=%SQL_HOME%\jdbc\lib\ojdbc8.jar;%CPFILE%



            rem <!-- When we ship embedded in SQLDeveloper, heres where we look -->

            if exist %SQL_HOME%\sqldeveloper\ SET CPFILE=%SQL_HOME%\sqldeveloper\sqlcl\lib\*;%CPFILE%



            REM Use internal simple X for awt in

            SET STD_ARGS=-Djava.awt.headless=true -Xss10M

            if "%ORACLE_HOME%" == "" (

                  SET CPFILE=%CPFILE%;%CLASSPATH%

            ) ELSE (

                  SET CPFILE=%ORACLE_HOME%\jdbc\lib\ojdbc8.jar;%ORACLE_HOME%\ojdbc8.jar;%CPFILE%;%CLASSPATH%


            REM Example: SET CUSTOM_JDBC="C:\thirdparty_jdbc_driver.jar"

            IF NOT "%CUSTOM_JDBC%" == "" (

                  SET CPFILE=%CUSTOM_JDBC%;%CPFILE%



            REM Have you downloaded a specific jre and dropped it into SQL_HOME as jre

            IF NOT EXIST "%SQL_HOME%\jre\" GOTO OK

              SET JAVA_HOME=%SQL_HOME%\jre\

              SET PATH=%JAVA_HOME%bin;%PATH%




              java %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*

              GOTO EXIT



            REM if java home is set, then this is where we use it.

            "%JAVA_HOME%\bin\java" %JAVA_OPTS% %STD_ARGS% %DEBUG% -cp "%CPFILE%" oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli %*