This post is going to show how to configure SQLcl to
* Use Directory Naming method (ldap.ora) to resolve connection string
* Passwordless connection with saved credential in Oracle Wallet
The configuration applies on from version 23.1 to current 25.4.1. Configuration of old versions can be found at Oracle SQLcl installation and configuration.
SQLcl can use two type of JDBC drivers to access Oracle database, thin jdbc and thick jdbc.
* Thin jdbc used for most scenario, requires Java
* Thick jdbc is needed while directory naming used, requires Java and Oracle client
Connecting database with credential in Oracle Wallet and do not include username and password in connection string, it needs to configure environment variable TNS_ADMIN and file ojdbc.properties.
Using Directory Naming method needs to install Oracle Instance Client and configure environment variable PATH (on Windows) or LD_LIBRARY_PATH (on Linux).
1. Install Java 17
SQLcl is a Java application, it needs JDK 17 installed for SQLcl 23.1 to 25.4.1 (currently latest version).
Normally, JDK is released in a few different format. It is recommended to download Compressed Archive format, which installation is just one step - unzip downloaded file to a directory.
For example, on Windows, downloaded file is jdk-17.0.16_windows-x64_bin.zip, unzip it to a directory, such as
C:\Java\\jdk-17.0.16
On Linux, downloaded file is jdk-17.0.16_linux-x64_bin.tar.gz, extract files to a directory, such as
/u01/jdk-17.0.16
2. Install SQLcl
If you do not have Oracle Database installed on your machine, or you want to install latest version of SQLcl instead of the version shipped with Oracle Database, you can download and install standalone SQLcl.
SQLcl standalone version can be downloaded from SQLcl home page. Currently, the latest version is is 25.4.1 and the default file name is
sqlcl-25.4.1.022.0618.zip
Unzip the file with unzip command
unzip -q sqlcl-25.4.1.022.0618.zip
It will create directory 'sqlcl' under current directory.
3. Install Oracle Instant Client
It is optional unless Directory Naming method is used, which needs Oracle network configuration files sqlnet.ora and ldap.ora created.
When Directory Naming method is used, SQLcl needs thick jdbc driver which is shipped Oracle client software. It usually includes library file ocijdbc23.dll (Windows) or libocijdbc23.so (Linux). Here 23 is the client version. For Oracle client 19c, it would be ocijdbc19.dll (Windows) or libocijdbc19.so (Linux).
Current latest version is 23.26.1, download Oracle Instant Client and unzip to local directory.
4. Configure SQLcl
4.1 Set up environment variable JAVA_HOME
It is required to start SQLcl. For example,
set JAVA_HOME=C:\Java\jdk-17.0.16 -- On Windows
export JAVA_HOME=/u01/jdk-17.0.16 -- On Linux
Now, SQLcl connects to database with JDBC thin driver, and the connection string is similar as Oracle Network Easy Connect Naming (EZCONNECT) Method, it supports formats
(1) <username>[/<password>]@<host>[:<port>]/<service_name> [as sysdba]
(2) <username>[/<password>]@<host>:<port>:<instance_name> [as sysdba]
(3) <username>[/<password>]@<host>[:<port>]/<service_name>:<server>/<instance_name> [as sysdba]
(4) <username>[/<password>]@<host>[:<port>]/<instance_name> [as sysdba]
4.2 Set up variable TNS_ADMIN
It is required when accessing database with Local naming method (tnsnames.ora), Directory Naming method (sqlnet.ora, ldap.ora). It is also required when accessing database with user name and password saved as credential in Oracle Wallet.
Its value is the directory where Oracle network files (sqlnet.ora, tnsnames.ora, ldap.ora, etc.) are located. For example
set TNS_NAME=C:\oracle\network\admin -- On Windows
export TNS_NAME=/u01/oracle/network/admin -- On Linux
Now, SQLcl connects to database with JDBC thin driver and the connection string can use net service names defined in files tnsnamres.ora. It support format,
sql <username>[/<password>]@<net_service_name> [as sysdba]
Here, <net_service_name> is entry defined in tnsnames.ora.
4.3 Configuration file ojdbc.properties
It is required when SQLcl accesses database without specifying username and password in command line and read them from credential in Oracle Wallet.
The file is created under directory defined by variable TNS_ADMIN. Therefore, variable TNS_ADMIN is prerequisite to have SQLcl access file ojdbc.properties. Create the file if it does not exist, and add following line to the file,
oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
It tells SQLcl the location of Oracle Wallet.
Now, SQLcl connects to database with JDBC thin driver, and connection string does not include username and password. For example,
sql /@<net_service_name> [as sysdba]
<net_service_name> is resolvable by EZCONNECT, Local Naming or Directory Naming. It is also a credential name saved in Oracle Wallet with database username and password pair.
SQLcl finds credential named <net_service_name> from Oracle Wallet, the credential stores both username and password. SQLcl uses the username and password to access database <net_service_name>. Therefore, here <net_service_name> has two meanings (credential name and database address).
4.4 Set environment variable PATH or LD_LIBRARY_PATH to include Oracle Instant client home
It is required when SQLcl accesses database with Directory Naming method (need network files sqlnet.ora and ldap.ora).
For example,
set PAHT=c:\instantclient_23_0;%PATH% -- On Windows
export LD_LIBRARY_PATH=/u01/instantclient_23_0:$LD_LIBRARY_PATH -- On Linux
It tells SQLcl where to find Oracle JDBC thick driver ojdbc jar file (ojdbc11.jar, ojdbc17.jar, etc) and ocijdbc library file (ocijdbc23.dll, libocijdbc23.so, etc.).
Now, SQLcl connects to database with JDBC thick driver and connection string can use net service name defined in tnsnames. ora for ldap server (Microsoft AD, Oracle OID, or Oracle OUD).
Depends on the version of SQLcl, the connection format would be,
* Version 23.1 - 23.3
sql <username>[/<password>]@jdbc:oracle:oci:@<net_service_name> [as sysdba]
* Version 23.4 or higher
sql -thick <username>[/<password>]@<net_service_name> [as sysdba]
Here, the option '-thick' and '@jdbc:oracle:oci:' force SQLcl to use JDBC thick driver shipped with Oracle Instant Client.
5. Troubleshooting
5.1 no ocijdbcxx in java.library.path
Depends on the version of SQLcl, the ocijdbcxx could be ocijdbc21, ocijdbc18, ocijdbc12, etc. An example error of SQLcl 21.4
$ sql -oci system@db02
SQLcl: Release 21.4 Production on Thu Mar 03 12:02:57 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Password? (**********?) ******************
USER = system
URL = jdbc:oracle:oci8:@db02
Error Message = no ocijdbc21 in java.library.path
USER = system
URL = jdbc:oracle:thin:@db02
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=AEhQvjndR12Sh75PCY2BjA==)
USER = system
URL = jdbc:oracle:thin:@db02:1521/db02
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=OpQjxIdgQgyhXYxCc+ZdRA==)
Username? (RETRYING) ('system/*********@db02'?)
The issue happens when LD_LIBRARY_PATH (on Linux) or PATH (on Windows) does not include the valid location of ocijdbc library. The library file name should be
libocijdbcxx.so (on Linux)
ocijdbcxx.dll (on Windows)
Here, xx is version number. In the previous error message, the library file is libocijdbc21.so (on Linux) or ocijdbc21.dll (on Windows). Find out where the file is and set LD_LIBRARY_PATH (on Linux) or PATH (on Windows) to that location.
5.2 Incompatible version of libocijdbc[Jdbc:xxxxxx, Jdbc-OCI:xxxxxx
It happens when SQLcl JDBC drive (e.g. ojdbc11.jar) version does not match OCI JDBC driver version. Example error of SQLcl 21.4
sql -oci system@db02
SQLcl: Release 21.4 Production on Thu Mar 03 12:10:01 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Password? (**********?) ******************
USER = system
URL = jdbc:oracle:oci8:@db02
Error Message = Incompatible version of libocijdbc[Jdbc:214000, Jdbc-OCI:213000
USER = system
URL = jdbc:oracle:thin:@db02
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=ca3Z43RDSHunGAM3F42dWg==)
USER = system
URL = jdbc:oracle:thin:@db02:1521/db02
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=QbrZESLFQiqx4KkkdOgksA==)
Username? (RETRYING) ('system/*********@db02'?)
From the error message, we can see that SQLcl JDBC is 21.4 (214000) and OCI JDBC (Oracle Instant Client)is 21.3 (213000). The JDBC and OCI JDBC version can be verified with java command.
(1) Verify SQLcl JDBC driver version
java -jar <SQLCL_HOME>/lib/ojdbc11.jar
Here, <SQLCL_HOME> is SQLcl location. The SQLcl executable sql (on Linux) and sql.exe (on Windows) exist in subdirectory bin under <SQLCL_HOME>. For example, the standalone SQLcl is installed in directory /u01/app/oracle/sqlcl, the command output is
$ java -jar /u01/app/oracle/sqlcl/lib/ojdbc8.jar
Oracle 21.4.0.0.0 JDBC 4.2 compiled with javac 1.8.0_311 on Mon_Nov_08_14:15:50_PST_2021
#Default Connection Properties Resource
#Thu Mar 03 21:18:35 EST 2022
***** JCE UNLIMITED STRENGTH IS INSTALLED ****
It shows that the JDBC driver is 21.4.0.0.0.
(2) Verify OCI JDBC driver version
OCI JDBC library is provided by Instant Client installation
java -jar <INSTANT_CLIENT_HOME>/ojdbc8.jar
OCI JDBC library is provided by Oracle Database or traditional Oracle Client installation
java -jar <ORACLE_HOME>/jdbc/lib/ojdbc8.jar
Sample output
$ java -jar /u01/app/oracle/instantclient_21_3/ojdbc8.jar
Oracle 21.3.0.0.0 JDBC 4.2 compiled with javac 1.8.0_291 on Wed_Jul_21_00:50:05_PDT_2021
#Default Connection Properties Resource
#Thu Mar 03 21:33:12 EST 2022
***** JCE UNLIMITED STRENGTH IS INSTALLED ****
The version of OCI JDBC shipped with Oracle Instant Client is 21.3.0.0.0.
The verification of SQLcl JDBC and OCI JDBC confirmed that the error message is showing correct status.
The solution is to install matching version of SQLcl or Oracle Instant Client, in the example, install SQLcl 21.3 or Oracle Instant Client 21.4. If the required matching version is not available, as a workaround, you can copy OCI JDBC ojdbc8.jar to replace SQLcl ojdbc8.jar.
Note: make a copy of SQLcl ojdbc8.jar before override it with OCI JDBC ojdbc8.jar.
5.3 Internal Error: Fetch error message failed!
It happens when SQLcl is shipped with Oracle Database or the OCI JDBC is shipped with Oracle Database (not with Oracle Instant Client), but variable ORACLE_HOME is not set properly. Example output
$ sql -oci system/oracle@db02
SQLcl: Release 19.1 Production on Thu Mar 03 21:51:27 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
USER = system
URL = jdbc:oracle:oci8:@db02
Error Message = Internal Error: Fetch error message failed!
USER = system
URL = jdbc:oracle:thin:@db02
Error Message = IO Error: could not resolve the connect identifier "db02"
USER = system
URL = jdbc:oracle:oci8:@db02
Error Message = Internal Error: Fetch error message failed!
USER = system
URL = jdbc:oracle:thin:@db02
Error Message = IO Error: could not resolve the connect identifier "db02"
USER = system
URL = jdbc:oracle:thin:@db02:1521/db02
Error Message = IO Error: could not resolve the connect identifier "db02:1521/db02"
Username? (RETRYING) ('system/*********@db02'?)
Solution is to set proper value for variable ORACLE_HOME.
No comments:
Post a Comment