Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQLcl on 19c Database Servers... will anything above 22.4 ever work the way it should out of the box?

Richard SouleJul 17 2023

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?

Comments

BluShadow Mar 18 2024

Oracle support documents are available to paying customers through the support portal: support.oracle.com

If you are not a paying customer then I'm afraid you won't have access to the document, and it is a breach of any customer's agreement with Oracle to supply such support documents to others.

1 - 1

Post Details

Added on Jul 17 2023
9 comments
2,134 views