Forum Stats

  • 3,758,928 Users
  • 2,251,475 Discussions
  • 7,870,439 Comments

Discussions

sql.bat not found in sqlcl-18.1.0 download

srg
srg Member Posts: 21 Blue Ribbon
edited Apr 24, 2018 3:17PM in SQLcl

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.

Thanks,

-SRG

Erik van Roonjogrossuser8237325

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Apr 6, 2018 5:39PM

    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 17.4.0.354.2224.

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

    50c50< SET CPFILE=%SQL_HOME%\lib\oracle.sqldeveloper.sqlcl.jar;%CPFILE%---> SET CPFILE=%SQL_HOME%\lib\dbtools-sqlcl.jar;%CPFILE%67c67< SET CPFILE=%SQL_HOME%\lib\xmlparserv2.jar;%CPFILE%---> SET CPFILE=%SQL_HOME%\lib\xmlparserv2-sans-jaxp-services.jar;%CPFILE%69c69< SET CPFILE=%SQL_HOME%\lib\oracle.dbtools-common.jar;%CPFILE%---> SET CPFILE=%SQL_HOME%\lib\dbtools-common.jar;%CPFILE%72c72< SET CPFILE=%SQL_HOME%\lib\oracle.dbtools.http.jar;%CPFILE%---> SET CPFILE=%SQL_HOME%\lib\dbtools-http.jar;%CPFILE%75c75< SET CPFILE=%SQL_HOME%\lib\oracle.dbtools.jdbcrest.jar;%CPFILE%---> SET CPFILE=%SQL_HOME%\lib\dbtools-jdbcrest.jar;%CPFILE%87,88c87,88

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

    93c93

    <       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!

    Erik van Roon
  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Apr 24, 2018 3:17PM

    Glen,

    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!

    SQL.BAT@echo offsetlocal enableextensionsREM Switch codepage to UTFchcp 65001  >nul 2>&1SET 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%if NOT "%SQLCL_CLASSPATH%"=="" SET CPFILE=%SQLCL_CLASSPATH%;%CPFILE%REM Use internal simple X for awt in SET STD_ARGS=-Djava.awt.headless=true -Xss10Mif "%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 jreIF NOT EXIST "%SQL_HOME%\jre\" GOTO OK  SET JAVA_HOME=%SQL_HOME%\jre\  SET PATH=%JAVA_HOME%bin;%PATH%:OKIF NOT DEFINED JAVA_HOME (  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 %*:EXITendlocal
    jogrossuser8237325