2.8 Connecting to a Database

Learn how to connect to a database, manage persistent connections, and work with connections in the OCI Database Tools Service.

2.8.1 About the CONNECT Command

Use the CONNECT command to connect to a database.

The options specified automatically determine the kind of connection required, such as whether it is a traditional client-server connection or a wallet-based cloud connection. You can override this by using options that specify a particular connection type. For example, specify -wallet to use the wallet connection type.

The connection types supported in SQLcl are:

  • KERBEROS
  • NAME
  • OCI
  • ORACLE
  • OREST
  • RADIUS
  • SOCKS
  • THIRD
  • WALLET

For more information, see the CONNECT command.

2.8.2 Working with Persistent Connections

You can manage persistent connections with the Connection Manager (CONNMGR) command.

Persistent connections are referenced by a connection name. Connections may store a user name. If a user name is stored, a password may also be stored. Passwords are stored in a secure wallet. If a password is not stored, it is provided when prompted to create the connection. A stored connection may describe a connection in a variety of formats, including basic host name, port and service name values or a TNS name and a cloud configuration.

To add a stored connection, see NAME Connection Type.

To save a stored connection, see the options -save, -savepwd, and -replace in CONNECT.

For more information about the Connection Manager command, see CONNMGR.

2.8.3 Working with Connections in the Database Tools Service

You can use the Connection Manager (CONNMGR) command with the -OCI option to work with connection definitions stored in the Database Tools Service. This allows you to browse connections available in your Oracle Cloud Infrastructure (OCI) tenancy and import them as local, named SQLcl connections, simplifying how you connect to Oracle Databases in OCI.

Learn how to use the OCI configuration commands and the CONNMGR command to import a Database Tools connection into your local store and connect to an Oracle Database in OCI.

2.8.3.1 Configuring the OCI Profile

To import and connect to a Database Tools connection using the SQLcl connection store, you must a select a valid OCI profile. SQLcl uses the DEFAULT profile unless another is selected. Additionally, you must select a region to access OCI services. The region generally comes from the profile, but you can change it using the OCI REGION command.

Listing Available Profiles

List available profiles and view the current default profile (marked with *).

oci profile

Example

oci profile
Configured Profiles:
     UKWEST
     PERSONAL
     PHOENIX
    *DEFAULT

Setting the Required Profile

Use the following command to set the profile.

oci profile <profile_name>

Example

oci profile UKWEST

Setting the Required Region

Use the following command to set the region.

oci region <region_name>

Example

oci region uk-cardiff-1

2.8.3.2 Listing Available Database Tools Connections

Use the CONNMGR LIST command with the -OCI option to list the Database Tools connections that are available to you within your OCI tenancy, across all accessible containers.

Syntax

connmgr list -oci

Example

connmgr list -oci

Display Name        OCID
------------------- -------------------------------------------------------
DB23c-Free          ocid1.databasetoolsconnection.ocl.uk-london-1.xxxxx
DBTools-ATP-Conn    ocid1.databasetoolsconnection.ocl.uk-london-1.yyyyy

2.8.3.3 Showing Details of a Specific Connection

Use the CONNMGR SHOW command with the -OCI option to display detailed information of a specific Database Tools connection. The command looks up the connection by its display name, in a case-insensitive manner.

Syntax

connmgr show -oci <display_name>

Example

connmgr show -oci DB23c-Free

Property          Value
----------------- -----------------------------------------------------
Display Name      DB23c-Free
OCID              ocid1.databasetoolsconnection.ocl.uk-london-1.xxxxx
Compartment OCID  ocid1.compartment.oc1..zzzzz
Type              ORACLE_DATABASE

2.8.3.4 Importing a Database Tools Connection

Use the CONNMGR IMPORT command with the -OCI option to import a connection definition from the Database Tools Service into your local SQLcl connection store.

Specify the display name of the connection to import.

Syntax

connmgr import -oci <display_name>

Example

connmgr import -oci DB23c-Free

Connection 'DB23c-Free' saved.

2.8.3.5 Connecting to a Database Using an Imported Connection

After importing a connection, you can connect to it using the standard CONNECT (or CONN) command.

SQLcl uses the stored connection definition, including its OCID and the OCI profile information captured at the time of import, to establish the connection. The currently-active OCI profile in your SQLcl session is not used for connecting to an already imported connection.

Example

connect DB23c-Free
Connected.