TIL: SQLcl is not a full replacement yet
Sven Illert -
Today I learned, that the famous SQLcl is not a full replacement for the loved SQL*Plus yet. I encoutered an issue when using a local wallet to authenticate to the database. That becomes handy when you need a connection via SQL*Net instead of the local bequeath authentication, like with dgmgrl when you want to perform a switchover operation. With an inhouse tool I have generated wallet entries with the EZ-Connect String to avoid managing a tnsnames.ora file. With SQL*Plus I can use these entries in the following way:
% mkstore -wrl $TNS_ADMIN -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
1: vm100:1521/SI03V100 SYS
% sqlplus /@vm100:1521/SI03V100 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 24 08:38:43 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL>
So far, so easy. That also works when using dgmgrl and EZ-Connect within the Data Guard broker configuration. But the problem is, this does not work for SQLcl. Neither in a version that is deployed with an 19c database installation, nor with the latest version that you can download from Oracle (as of today, that is release 23.2). Let’s see what happens.
19c database home on Linux
% sql /@vm100:1521/SI03V100 as sysdba
SQLcl: Release 21.4 Production on Thu Aug 24 08:55:03 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
USER =
URL = jdbc:oracle:oci8:@vm100:1521/SI03V100
Error Message = ORA-01017: invalid username/password; logon denied
USER =
URL = jdbc:oracle:thin:@vm100:1521/SI03V100
Error Message = ORA-01017: invalid username/password; logon denied
23.2 slqcl on windows
% sql /@vm100:1521/SI03V100 as sysdba
SQLcl: Release 23.2 Production on Thu Aug 24 08:52:26 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved. Alle Rechte vorbehalten.
USER =
URL = jdbc:oracle:thin:@vm100:1521/SI03V100
Error Message = ORA-01017: invalid username/password; logon denied
The only way to workaround this issue as I know so far is to use aliases in the tnsnames.ora file and connect to that one.
% mkstore -wrl $TNS_ADMIN -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
1: SI03V100 SYS
% cat $TNS_ADMIN/tnsnames.ora
SI03V100=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=SI03V100))(ADDRESS=(PROTOCOL=tcp)(HOST=vm100)(PORT=1521)))
% sql /@SI03V100 as sysdba
SQLcl: Release 21.4 Production on Thu Aug 24 09:42:59 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.16.0.0.0
SQL>
If you have a better idea how that could work, just drop me a hint via contact form.