SQLines Data - Configure and Troubleshoot Connection to Oracle

If you need to connect to a localhost Oracle database you do not need to setup any additional libraries to use SQLines Data tool as they are already supplied with the Oracle server software.

If you need to connect to a remote Oracle database, you need to have Oracle OCI (Oracle Call Interface) installed in your system.

Before downloading check if you already have Oracle OCI libraries:

• Linux:
  • libclntshcore.so.xx.x (xx.x denotes Oracle version), for example at:
  • /app/oracle/product/12.1.0/dbhome_1/lib/libclntshcore.so.12.1
• Windows:
  • oci.dll, for example at:
  • C:\oraclexe\app\oracle\product\11.2.0\server\bin\oci.dll
  • C:\oracle\app\product\12.1.0\dbhome_1\bin\oci.dll

If Oracle client is not available, go to Oracle site https://www.oracle.com/downloads/index.html and download Oracle Instant Client for your platform (https://www.oracle.com/database/technologies/instant-client/downloads.html).

Download Oracle Instant Client for your platform, for example, instantclient-basic-linux.x64-12.2.0.1.0.zip and extract files:

unzip instantclient-basic-linux.x64-12.2.0.1.0.zip

You can find libclntsh.so.xx.x library at the Instant Client directory (xx.x denotes Oracle version, for example, libclntsh.so.12.1).

Create a symbolic link, so SQLines Data tool is able to load the required library:

cd <oracle_instant_client_dir>
ln -s libclntsh.so.12.1 libclntsh.so

Before running sqldata command, add Oracle Instant Client location to LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<oracle_instant_client_dir>

Download Oracle Instant Client for your 64-bit or 32-bit Windows platform, for example, instantclient-basic-nt-12.2.0.1.0.zip for 32-bit Windows, and extract files:

C:\instantclient_12_2

You can find oci.dll library at the Instant Client directory.

Before running sqldata command, add Oracle Instant Client location to the system PATH variable or specify -oci_lib option in sqldata.cfg file.

Contact us at support@sqlines.com if you still have any connection problems.

Create a directory for the Oracle wallet at $ORACLE_HOME/ssl_wallet

Obtain the root certificate and put the file in the ssl_wallet directory. If you use Amazon RDS, download the root certificate that works for all AWS Regions.

Edit the $ORACLE_HOME/network/admin/sqlnet.ora file, adding the following lines:

  WALLET_LOCATION =
     (SOURCE =
       (METHOD = FILE)
       (METHOD_DATA =
         (DIRECTORY = <path_to_wallet>)
       )
     )

  SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
  SSL_CLIENT_AUTHENTICATION = FALSE
  SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Modify $ORACLE_HOME/network/admin/tnsnames.ora file and include the following entry:

 <net_service_name> = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = 
        (PROTOCOL = TCPS) 
        (HOST = <endpoint>) 
        (PORT = <ssl_port_number>)
      )
    )
    (CONNECT_DATA = 
      (SID = <database_name>)
    )
  )