Oracle SQL Developer Command Line (SQLcl) is a free command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, as well as supporting your previously written SQL*Plus scripts.
1. Install Java SE Runtime Environment (JRE) 8
SQLcl is a Java application, it needs JRE 8 or JDK 8 installed and configured properly on the machine.
The installation can be skipped if JRE 8 or Java SE Development Kit (JDK) 8 is already installed.
If SQLcl is installed with Oracle Database or Oracle Database Client, it can use JRE distribution shipped with Oracle Database. Therefore, JRE 8 is only needed when standalone SQLcl is installed.
1.1 Install JRE8 on Windows
Before installation, click and click Settings -> Apps to open "Apps & features" utility to check if any JRE or JDK is installed.
The installation will be listed if JRE 8 is installed with Windows installer (the exe file downloaded from official Java web site). If it is not there, the JRE is not installed or not properly configured. Then download latest update of JRE 8 from Java web site (www.java.com). Currently, it is Version 8 Update 321 and the downloaded Windows installation file name should be
jre-8u321-windows-x64.exe
Run the file and following the instruction, the installation is pretty straight forward. By default, it will be insalled under directory
C:\Program Files\Java\jre1.8.0_321
Note: Do not copy installation from other machine or use compress/decompress the archive file insteade of running official exe installation file. SQLcl 19.1 or higher retrieve JRE installation information from Windows registry, these information is created by exe installation utility.
1.2 Install JRE 8 on Linux
JRE 8 is usually distributed in two formats for Linux
Compressed Archive
RPM Package
The Compressed Archive is recommended because it is installed by decompressing the archive file to a directory and do not copy any files to other system directoies. It will not have impact on other applications running on same system even if there is other JRE is installed and used by other applications. Download the latest update of JRE 8 for Linux, the default downlad file name for JRE 8 update 321 is
jre-8u321-linux-x64.tar.gz
Unzip the file with tar command
tar zxf jre-8u321-linux-x64.tar.gz
It will create a directory 'jre1.8.0_321' under current directory.
2. Install SQLcl
2.1 Install with Oracle Database or Oracle Database Client
SQLcl is included with Oracle Database installation since Oracle Database 12.2. It will be installed when you install Oracle Database or Oracle Database Client 12.2 or higher.
The installation location varies in different version of Oracle Homes, follwing are examples of locating SQLcl executable (command used to start SQLcl)
Oracle 12.2: $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql (sql.bat for Windows)
Oracle 19.3: $ORACLE_HOME/sqldeveloper/sqldeveloper/bin/sql (sql.exe for Windows)
Usually, a script sql (sql.bat for Windows) is created under $ORACLE_HOME/bin, which calls the actual executable.
2.2 Install standalone version
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 insall standalone SQLcl.
SQLcl standalone version can be downloaded from SQLcl home page. Currently, the latest version is is 21.4.1 and the default file name is
sqlcl-21.4.1.17.1458.zip
Unzip the file with unzip command
unzip -q sqlcl-21.4.1.17.1458.zip
It will create directory 'sqlcl' under current directory.
3. Configure SQLcl
Based on database connection method, a couple of different configurations have to be done.
3.1 Configuration needed for all connection method
Add java executable location to PATH environment variable. SQLcl utility 'sql' will search java executable through the value of PATH variable.
On Windows, PATH is only needed for old version SQLcl, SQLcl 19 or higher, no matter shipped with Oracle Database version or standalone version, retrieves Java home informaiton from Windows registry and does not need PATH variable
set PATH=<JAVA_HOME>\bin;%PATH%
On Linux
export PATH=<JAVA_HOME>/bin:$PATH
Here, <JAVA_HOME> is the location where JRE 8 is installed. If SQLcl is installed with Oracle Database, <JAVA_HOME> should be <ORACLE_HOME>/jdk/jre. For example,
Example 1. The SQLcl is installed with Oracle database 12.2 on Windows and Oracle Home is C:\oracle\product\12.2.0\client_1, set PATH as following
set PATH=C:\oracle\product\12.2.0\client_1\jdk\jre\bin;%PATH%
Example 2. The SQLcl is standalone version on Linux, and JRE 8 is installed in directory /u01/app/oracle/jre1.8.0_321, set PATH as following
export PATH=/u01/app/oracle/jre1.8.0_321/bin:$PATH
Test if java is reachable on Windows
C:\>where java C:\oracle\product\12.2.0\client_1\jdk\jre\bin\java.exe C:\>java -version java version "1.8.0_91" Java(TM) SE Runtime Environment (build 1.8.0_91-b61) Java HotSpot(TM) 64-Bit Server VM (build 25.91-b61, mixed mode)
Test if java is reachable on Linux
$ which java /u01/app/oracle/jre1.8.0_321/bin/java $ java -version java version "1.8.0_321" Java(TM) SE Runtime Environment (build 1.8.0_321-b07) Java HotSpot(TM) 64-Bit Server VM (build 25.321-b07, mixed mode)
3.2 JDBC thin connection with native method
This method does not need extra configuration except the java executable PATH. 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]
The first format connects to database with Database Service name and is recommended for most senarios.
The second format connects to database with Database Instance name. You may need it when you startup or shut down database with SQLcl.
The thrid and fourth are supported by Oracle JDBC driver 19c or higher.
I have a two-node RAC database as following,
SCAN host name: rac-scan.dbaplus.ca
Physical host names of both nodes: rac01.dbaplus.ca, rac02.dbaplus.ca
Database name: orcl
Two instances orcl_1 and orcl_2 are running on nodes rac01.dbaplus.ca and rac02.dbaplus.ca respectively
The first format connection accessing database service orcl at SCAN address will be, based on workload, created to one of the two nodes, connection example
$ sql system/oracle@rac-scan.dbaplus.ca/orcl SQLcl: Release 19.1 Production on Wed Mar 02 22:22:48 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Mar 02 2022 22:20:10 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0 SQL>
This method support load balance and connection failover. You cannot startup database with this connection. You could shut down database with this connection, but you will lose the conneciton after the database is down and cannot connect back anymore.
Using second format, you can connect to instance orcl_1 on node rac01.dbaplus.ca or orcl_2 on node rac02.dbaplus.ca, the connection can be used to startup or shut down database if the instances have static registration in local listeners (not SCAN listeners). The connection does not supprt load balance and failover. Connection example
$ sql system/oracle@rac01.dbaplus.ca:1521:orcl_1 SQLcl: Release 19.1 Production on Wed Mar 02 22:23:01 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Wed Mar 02 2022 22:20:23 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0 SQL>
3.3 JDBC thin connection using Local Naming Method (tnsnames.ora)
To use Local Naming Method, it has to create file named tnsnames.ora and tell SQLcl where the file is. The tnsnames.ora file location is configured with environment variable TNS_ADMIN and SQLcl utility will search for tnsnames.ora in the path specified in the variable. The variable is set with following command
On Windows,
set TNS_ADMIN=<TNSNAMES_LOCATION>
On Linux,
export TNS_ADMIN=<TNSNAMES_LOCATION>
Here, <TNSNAMES_LOCATION> is the path where tnsnames.ora is located. You can create and save tnsnames.ora in any directory you like and point TNS_ADMIN to that directory. If SQLcl is shipped with Oracle Database, you may want to point <TNSNAMES_LOCATION> to <ORACLE_HOME>/network/admin. After tnsnames.ora is created and TNS_ADMIN is configured, SQLcl can connect to database using logon string
<username>[/password]@<network_service_name>
Here, <network_service_name> is an entry in tnsnames.ora. As an example, I created tnsnames.ora under directory /u01/app/oracle/network, the file contains
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.dbaplus.ca)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
db01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.dbaplus.ca)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db01)
)
)
And TNS_ADMIN is set to /u01/app/oracle/network
$ export TNS_ADMIN=/u01/app/oracle/network $ echo $TNS_ADMIN /u01/app/oracle/network
Connection example
$ sql system/oracle@orcl
SQLcl: Release 19.1 Production on Wed Mar 02 23:14:05 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Wed Mar 02 2022 23:14:06 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL>
SQL> show tns
TNS Lookup locations
--------------------
1. USER Home dir
/home/oracle
2. TNS_ADMIN
/u01/app/oracle/network
Location used:
-------------
/u01/app/oracle/network
Available TNS Entries
---------------------
orcl
db01
3.4 JDBC thick connection using Directory Naming Method (ldap.ora)
It takes two steps to implement this method
1) Create file ldap.ora under TNS_ADMIN. The environment variable TNS_ADMIN is used in same way as Local Naming Method discribed previously. If you already have ldap.ora in place, you can copy it to the location specified by TNS_ADMIN, or just point TNS_ADMIN to where the ldap.ora exists.
2) Configure the path of ocijdbc library. The ocijdbc library is used as thick JDBC driver when SQLcl uses Directory Naming Method. The library is shipped with Oracle Database and Oracle client software (both traditional Database Client and Instance Client). Oracle Instant Client is recommended if SQLcl is installed as standalone version. After Oracle Database or Oracle Client (traditional client or instant client) is ready, configure ocijdbc library path as following
On Windows:
If traditional Oracle Client home or Oracle Database home is used
set PATH=<ORACLE_HOME>\bin;%PATH%
If instant client home is used
set PATH=<ORACLE_INSTANT_HOME>;%PATH%
On Linux:
If traditional Oracle Client home or Oracle Database home is used
export LD_LIBRARY_PATH=<ORACLE_HOME>/lib:$LD_LIBRARY_PATH
If instant client home is used
export LD_LIBRARY_PATH=<ORACLE_INSTANT_HOME>:$LD_LIBRARY_PATH
After configuring properly, SQLcl can be started with option '-oci'
sql -oci <username>[/password]@<network_service_name>
Here, <network_service_name> is conneciton entry created in directory server (Oracle OID or Microsoft AD). Connection example
$ sql -oci system/oracle@db02 SQLcl: Release 21.4 Production on Thu Mar 03 20:37:58 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Last Successful login time: Thu Mar 03 2022 20:35:20 -05:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 SQL> show jdbc -- Database Info -- Database Product Name: Oracle Database Product Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0 Database Major Version: 19 Database Minor Version: 0 -- Driver Info -- Driver Name: Oracle JDBC driver Driver Version: 21.5.0.0.0 Driver Major Version: 21 Driver Minor Version: 5 Driver URL: jdbc:oracle:oci8:@db02 Driver Location: resource: oracle/jdbc/OracleDriver.class jar: /u01/app/oracle/product/sqlcl/lib/ojdbc8.jar JarSize: 5077628 JarDate: Fri Jan 07 15:02:04 EST 2022 resourceSize: 2604 resourceDate: Wed Jan 05 08:03:30 EST 2022 SQL>
4. Troubleshooting
4.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.
4.2 Incompatible version of libocijdbc[Jdbc:xxxxxx, Jdbc-OCI:xxxxxx
It happens when SQLcl JDBC drive (ojdbc8.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 is 21.3 (213000). The JDBC and OCI JDBC version can be verifed with java command.
(1) Verify SQLcl JDBC driver version
java -jar <SQLCL_HOME>/lib/ojdbc8.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 avaible, as a workround, 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.
4.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.
4.4 The system cannot find the path specified
It happens on Windows while starting SQLcl with command %ORCLE_HOME%\bin\sql.bat. Example output
C:\>sql.bat system/oracle@orcl
The system cannot find the path specified.
The cause is that the sql.bat is not correct programmed. The solution is to replace the sql.bat with following code,
For Oracle 12.2
set filepath=%~f0
for /F "delims=" %%i in ("%filepath%") do set dirname=%%~dpi
set filepath="%dirname%..\sqldeveloper\sqlcl\bin\sql.bat"
%filepath% %*
For Oracle 19.3
set filepath=%~f0
for /F "delims=" %%i in ("%filepath%") do set dirname=%%~dpi
set filepath="%dirname%..\sqldeveloper\sqldeveloper\bin\sql.exe"
%filepath% %*
No comments:
Post a Comment