How to run sqlcl outside of SQL Developer directory

Question

I have installed the Oracle client WINDOWS.X64_213000_client.zip
and I want to use the bundled sql.exe to run SQL scripts in my project directory.

The sql.exe is located in the following directory which I have added to my Path env var:

E:\Oracle_21c\app\client\product\21.0.0\client_1\sqldeveloper\sqldeveloper\bin

The jar files are in

E:\Oracle_21c\app\client\product\21.0.0\client_1\sqldeveloper\sqldeveloper\lib

The following works:

cd E:\Oracle_21c\app\client\product\21.0.0\client_1\sqldeveloper

\sqldeveloper\bin\sql -LOGON user/password@tns_db_alias  
   select 'a' as a from dual;

But I can only run it from that directory, if I try to run sql.exe from the path directory by not qualifying it as bin\sql then an exception is thrown.

>sql -LOGON user/password@tns_db_alias

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hc/core5/http/ParseException

What can I do to make sql.exe runnable and accessible via the path?
So that I can be in any arbitrary directory e.g. \projects\myproj\
and run scripts using sql.exe

cd \projects\myproj\
sql -LOGON user/password@tns_db_alias my_db_script.sql

If you run sql /nolog does it also error? If not, what does show tns bring back ? — thatjeffsmith, Nov 24 '21 at 13:39

thatjeffsmith · Accepted Answer · 2021-11-24T16:49:59.183

There's a bug.

When I try to run the included 'sql' (SQLcl) program at all from this distribution -

c:\oracle\client\product\21.0.0\client_1\bin>sql /nolog
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hc/core5/http/ParseException
        at java.lang.Class.getDeclaredConstructors0(Native Method)
        at java.lang.Class.privateGetDeclaredConstructors(Class.java:2671)
        at java.lang.Class.getConstructor0(Class.java:3075)
        at java.lang.Class.newInstance(Class.java:412)
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.addListener(CommandRegistry.java:75)
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.addForAllStmtsListener(CommandRegistry.java:649)
        at oracle.dbtools.commands.NetCommands.registerCommands(NetCommands.java:28)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.initSqlcl(SqlCli.java:196)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:962)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:369)
Caused by: java.lang.ClassNotFoundException: org.apache.hc.core5.http.ParseException
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        ... 10 more

Go to oracle.com/sqlcl - download the standalone Zip. Put it in it's own directory, it will run just fine.

We'll have the 21c distribution updated ASAP to fix this for client downloads.

Workaround, update your path to include ..product\21.0.0\client_1\sqldeveloper\sqldeveloper\bin

If you run the sql.exe out of this directory, it works -

Thanks Jeff, the standalone version works, but only for a thin client url. if I use a TNSALIAS then I get an error " Incompatible version of libocijdbc[Jdbc:211000, Jdbc-OCI:213000 ". Also I don't want to run from the bin directory, because my scripts are in the project directory, and also include other scripts with relative paths. — crowne, Nov 25 '21 at 15:39

How to run sqlcl outside of SQL Developer directory

1 Answers1