When SQLcl 23.1 came out, we reported that SQLcl on 19c database servers wouldn't really work for DBAs because you couldn't have your ORACLE_HOME set. See: https://forums.oracle.com/ords/apexds/post/sqlcl-23-1-says-could-not-initialize-class-6209
The release notes for SQLcl 23.2 got updated with the following:
ORACLE_HOME usage
When using sqlcl in an ORACLE_HOME, it must be a minimum version of 21c.
But that's kind of confusing. Right now, on my Oracle Linux 8 server with 19.18 database, I have both SQLcl 22.4 and 23.2 installed. Each is installed in /opt. 22.4 is in /opt/sqlcl and 23.2 is in /opt/sqlcl_23.2. I wouldn't say that I am using SQLcl “in an ORACLE_HOME” since it's not deployed into my ORACLE_HOME. But maybe that note means that I can't have the ORACLE_HOME environment variable home set unless ORACLE_HOME is pointing to a 21c (or presumably higher) ORACLE_HOME. As I said, I'm confused by the note.
If I do what DBAs from time immemorial have been doing and attempt to connect to my database using sql / as sysdba using a bequeath connection which requires my ORACLE_HOME to be set, I get the following:
[oracle@databaseserver DB:dbacon ~]
$ . oraenv
ORACLE_SID = [dbacon] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora-rhel8 DB:dbacon 20:15:00 ~]
$ env | grep JAVA
JAVA_HOME=/usr/java/jdk-17.0.3.1
[oracle@databaseserver DB:dbacon ~]
$ sql -version
SQLcl: Release 22.4.0.0 Production Build: 22.4.0.342.1215
[oracle@databaseserver DB:dbacon ~]
$ sql / as sysdba
SQLcl: Release 22.4 Production on Sun Jul 16 19:49:07 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SYS@CDB$ROOT AS SYSDBA > exit <-- Awsome! Works as expected!
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
[oracle@databaseserver DB:dbacon ~]
$ /opt/sqlcl_23.2/bin/sql / as sysdba
SQLcl: Release 23.2 Production on Sun Jul 16 19:49:29 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
New version: 23.2.0 available to download
<-- Note, this isn't true!
USER = <-- Sadness! Errors out and won't connect!
URL = jdbc:oracle:thin:@127.0.0.1:1521:dbacon
Error Message = Could not initialize class oracle.jdbc.datasource.impl.OracleDataSource
As you can see, 22.4 works as expected. But 23.2 (and also 23.1 for that matter) gives the error we mentioned before.
Additionally, as I noted above, starting SQLcl 23.2 tells me that there is a ‘new version’ to download… which is the same version as the 23.2 I'm attempting to use.
Should I interpret the vague-ish message in the release notes as "No, you won't be able to do a bequeath connection to a 19c database on an Oracle database server anymore until you upgrade your database to 23c? (Our organization is using only long-term support releases.)
Or should I read that as "No, you won't be able to use any new version of SQLcl beyond 22.4 on your 19c database servers unless you also install a 21c (or higher) database client on your database server? If so, and correct me if I'm wrong, I still wouldn't be able to use a bequeath connection, right?
I could totally understand if I was complaining about not being able to connect to an Oracle 7 database, but come on, this is 19c, the most up-to-date long-term supported release of the database.
Note that in the previous thread on 23.1 user user-0w0kr mentioned that we could create a symbolic link in the ORACLE_HOME to ojdbc11.jar. I attempted that and got a different error, but it still didn't work. However, the error about ‘a new version of SQLcl’ did go away, so at least there was that…
[oracle@ora-rhel8 DB:dbacon 20:24:55 /u01/app/oracle/product/19.0.0.0/dbhome_2/jdbc/lib]
$ ln -s /opt/sqlcl_23.2/lib/ojdbc11.jar ojdbc11.jar
[oracle@ora-rhel8 DB:dbacon 20:26:36 ~]
$ /opt/sqlcl_23.2/bin/sql / as sysdba
SQLcl: Release 23.2 Production on Sun Jul 16 20:26:44 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
USER =
URL = jdbc:oracle:oci8:@
Error Message = no ocijdbc21 in java.library.path: /u01/app/oracle/product/19.0.0.0/dbhome_2/lib:/u01/app/oracle/product/19.0.0.0/dbhome_2:/u01/app/oracle/product/19.0.0.0/dbhome_2/lib:/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib
USER =
URL = jdbc:oracle:thin:@127.0.0.1:1521:dbacon
Error Message = ORA-01017: invalid username/password; logon denied
So…
SQLcl on 19c Database Servers... will anything above 22.4 ever work the way it should (or at least did for a very, very long time) out of the box?