Connect to an Oracle instance

To interact with Oracle databases (Cloud and private), when applying forward-engineering DDLs reverse-engineering, a user-provided Oracle client is required.  Just like major applications, including Tableau Desktop, we do NOT ship an Oracle Instant Client in our plugin for many reasons, including Oracle's licensing terms.  If you don't have an Oracle Client already installed on your machine, its download and installation will be required as part of the process so the connection settings can be pointed at it.

 

Important: when reverse-engineering an Oracle instance, non-privileged users can see the metadata of only their own objects, so to access others you need to be be granted SELECT_CATALOG_ROLE. More information is available here and here.

 

If you encounter an error ORA-16000: database or pluggable database open for read-only access, it has been confirmed by Oracle that pluggable and stand-by instances of Oracle are read-only which prevents the operation of the DBMS_METADATA.GET_DDL PL/SQL function which we need to perform reverse-engineering of the instance.  Hackolade Studio will not perform the reverse-engineering operation correctly if connected to a pluggable or stand-by instance.  

 

 

Note: There are many ways to configure connections to Oracle. Plus firewalls, proxies, and whitelist can be make it hard to access.  The easiest way to troubleshoot the connection settings in Hackolade is to make sure first that you have access from the same machine with another client tool.  Then it becomes easy to transpose the connection settings from that application to Hackolade.  Since SQL Developer is an Oracle product, it does not require a separate Oracle Instant Client, so having a working connection from SQL Developer is not alone sufficient proof that Hackolade could connect.

 

 

Thin vs Thick mode

The Hackolade Studio plugin for Oracle includes a thin client to facilitate connections to an Oracle instance, whether on-premises or in the cloud.  

 

You just need to give your connection a meaningful name, enter your host and port (default 1521), and either a Service Name or a SID.  Then move to the Authentication tab to enter your credentials.

 

If running on Oracle Cloud Infrastructure, things are even simpler if you fetch a Wallet, as described below.

 

ORACLE_HOME vs Instant Client

You may already have a full client installed on your machine.  ORACLE_HOME is a directory in the file system where the Oracle software is installed. The path to this directory is stored in an environment variable.  

 

To check if ORACLE_HOME has been set on your machine and available for Hackolade:

 

On Windows: At a command prompt, type C:\>echo %ORACLE_HOME%. If this gives you the directory path, then that means ORACLE_HOME is set.

If ORACLE_HOME is not set, the output will simply give back %ORACLE_HOME%. 

 

On Unix/Linux: type env  grep | ORACLE_HOME

If ORACLE_HOME is set, then you may choose this option:

 

 

If ORACLE_HOME is not set, you probably need to download and install an Instant Client.  You may have to get your IT department to push this to your machine.

 

Oracle Instant client is a basic lightweight client which can be unzipped in a location without any installation, it contains only the communication layer to be able to connect to oracle. When using Instant Client, just unzip it to a location of your choice.  Keep a note of the path to this folder, as you will have to specify it in the connection settings to your Oracle instance. 

 

You may set your ORACLE_HOME environment variable to this location, or simply specify the location in the Hackolade connection settings.

 

 

 

Oracle Instant Client installation on Mac

On MacOS, a trusted signed package is required.  When you download from Oracle, you must know that the .zip version won't be trusted by MacOS and will cause Hackolade to malfunction.  You must download one of the .dmg packages 

 

 

Once the package is downloaded, open the `<instant-client>.dmg` and copy all the files to a dedicated folder of your choice. For example, `/Users/<username>/databases/clients/oracle`. Keep a note of the path to this folder, as you will have to specify it in the connection settings to your Oracle instance. 

 

Connection method

We support 3 connection methods: basic, with an Oracle Cloud Wallet, or with a tnsnames.ora configuration file.

 

Basic connection settings

With the basic method, you must specify all the necessary parameters for the clinet to establish the connection.  This includes the hostname DNS or IP address, the port number, and either a service name or SID (Oracle system identifier)

 

 

or 

 

 

Oracle wallet

Oracle Wallet provides an simple and easy method to manage database credentials across multiple domains. It allows you to update database credentials by updating the Wallet instead of having to change individual datasource definitions. This is accomplished by using a database connection string in the datasource definition that is resolved by an entry in the wallet.

 

With Oracle Autonomous, you may also generate a wallet using these instructions. Once you have downloaded the Wallet .zip file, you simply need to specify the file path and name, and the service name

 

 

TNSnames.ora config file

TNS = Transparent Network Substrate. The tnsnames.ora file is a configuration file that contains network service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses. A net service name is an alias mapped to a database network address contained in a connect descriptor.

 

You may have a tnsnames.ora file on your machine or on a shared drive.  By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory, but it can also be in the directory specified by the TNS_ADMIN environment variable.  On a Windows machine, the system environment variable TNS_ADMIN, if any, generally indicates the location of your tnsnames.ora file.   At a command prompt, type C:\>echo %TNS_ADMIN% to find out.

 

You must specify the folder location of the tnsnames.ora file as well as the service name to be used.

 

 

Authentication

Hackolade does not yet support OS-based or Kerberos authentication.

 

You must provide the username, password, and optionally a role for your connection.

 

 

 

 

Advanced

Oracle instances can be quite large with many user schemas.  If you want to limit the scope of the discovery and you know the user schema you wish to access directly, you may enter it in the Advanced tab.  You may even create one connection per user schema you access often.

 

 

Connecting to Oracle on Amazon RDS

If you're running Oracle on Amazon RDS , the instructions to connect to SQL Developer can be followed to connect to Hackolade Studio in a similar way.  More details can also be found here.