These instructions show how to connect to Oracle Database Cloud Service from on-premise applications using language APIs such Node.js's node-oracledb, Python's cx_Oracle, PHP's OCI8 and PDO_OCI, Ruby's ruby-oci8. They also apply to other drivers and applications (such as SQL*Plus) implemented using OCI or OCCI.

This tutorial assumes that you have already created an Oracle DBCS instance.

1. Install Oracle Database Language APIs

Install your programming language and Oracle Database API on an on-premise computer. For the OCI-based languages discussed here, Oracle Client libraries are required. Commonly the Oracle Instant Client is used.

• To install cx_Oracle for Python, use the binaries from PyPI or follow these instructions.
• To install node-oracledb for Node.js follow these instructions.
• To install ROracle for R follow these instructions.
• To install PHP OCI8 for PHP, follow these instructions. Windows DLLs are included in PHP and are also available from PECL.
• To install PHP PDO_OCI for PHP, follow these instructions. Windows DLLs are included in PHP.
• To install ruby-oci8 for Ruby, follow these instructions.
• To install DBD::Oracle for Perl, set ORACLE_HOME and your library search path such as LD_LIBRARY_PATH or PATH to the Instant Client directory and follow these instructions.

2. Create an SSH tunnel to the database service's Compute Node

An SSH tunnel is recommended for connection. For example, on Linux or macOS:

• On the database service console, identify the Public IP address and the 'SQL*Net Port'.
• Choose a local port. You can use any port not in use but it is often easier to keep the port numbers the same.
• Start the tunnel on a host where the ssl private key pairs with a public key known to the database service:
  Copy

     $ ssh -L local-port:public-ip-address:SQL*Net-port opc@public-ip-address
        

  For example, run:

  Copy

     $ ssh -L 1521:a.b.c.d:1521 opc@a.b.c.d
        

  Where a.b.c.d represents the service's Public IP address, and 1521 is its SQL*Net port.

  If your private key file is not in the default location add the -i option:

  Copy

     $ ssh -i private-key-file -L . . .
        

  If this is the first time you are connecting, ssh will confirm the public key. Enter 'yes' to allow connection.

  Leave the tunnel open while accessing the database service.

More information about SSH tunnels, including information about creating them on Windows, is in the DBaaS documentation. Also see this OBE tutorial.

3. Configure the application to use the database service

Your application can use standard Oracle network naming methods such as Easy Connect or tnsnames.ora syntax. See the Oracle Net Documentation

To construct the connect string, use these three pieces of information:

host: localhost or your host's IP address

port: the local-port used by the SSH tunnel

service_name: a string like [Oracle SID or PDB].[Cloud Identity Domain].oraclecloud.internal

The Easy Connect syntax is the most straightforward way:

   host:port/service_name
  

For example:

   localhost:1521/PDB1.myclouddomain.oraclecloud.internal
  

Configure the application to use the desired credentials and the connect string. For example, with node-oracledb:

   oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost:1521/PDB1.myclouddomain.oraclecloud.internal"
  },  . . .
  

or with cx_Oracle:

   conn = cx_Oracle.connect("hr", "welcome", "localhost:1521/PDB1.myclouddomain.oraclecloud.internal")
  

4. Start your application

Run your application on your computer. If you start it from the command line, use a new terminal - don't use the terminal that is running SSH.

To troubleshoot connectivity issues, test with the SQL*Plus command line utility:

   sqlplus hr@\"localhost:1521/PDB1.myclouddomain.oraclecloud.internal\"