Subscribe

Share

Application Development

Getting Started with Autonomous

Connect to Oracle Autonomous Transaction Processing with multiple programming languages.

By Blaine Carter

April 3, 2019

By now you’ve probably heard about Oracle Autonomous Transaction Processing. If not, follow the link for more information.

One major feature of Oracle Autonomous Transaction Processing is enhanced security. For example, Oracle Autonomous Transaction Processing uses machine learning and automation to improve its own security, but it also takes advantage of an Oracle wallet. There are many ways you can use an Oracle wallet to enhance the security of database connections, but with Oracle Autonomous Transaction Processing, it is already set up, packaged, and ready to go.

I am going to walk you through the process of preparing to connect securely to your Oracle Autonomous Transaction Processing database instance with an Oracle wallet, followed by some code examples in a few languages you can use to test the connection.

Prerequisites

Here are the prerequisites for following along with the steps in this article:

  • Have an Oracle Autonomous Transaction Processing instance provisioned and running. (If you don’t have a current instance, you can sign up for a free trial.
  • Have a database schema and password created that you can use for testing.
  • Have access to the Oracle Autonomous Transaction Processing service panel or have someone with access available to help.
  • Download and install Oracle Database Instant Client.
  • (Optional but a good idea) Have Oracle SQLcl or Oracle SQL Developer installed to verify the connection in a neutral environment.
  • Download and install the database driver for your preferred language. Make sure the driver you choose uses Oracle Database Instant Client to make its connection. I will provide links to the language-specific drivers used in my examples.

Download Client Credentials (Oracle Wallet)

With the prerequisites complete, download the client credentials for your Oracle Autonomous Transaction Processing database instance.
  1. Go to the Oracle Autonomous Transaction Processing Database Details page for your Oracle Autonomous Transaction Processing database instance.
  2. Click the DB Connection button, shown in Figure 1.
  3. Click the Download button, shown in Figure 2.
    The client credentials zip file you download will contain a tnsnames.ora file that includes the TNS Name values listed in the Connection Strings panel. You can refer to the documentation for an explanation of when to use each of these. For my examples, I will be using the BlaineATP_MEDIUM TNS Name value.
  4. Enter a password (see Figure 3), and click Download.
    Remember this password. If you lose it, you will need to download a new credentials file.
  5. Save the file in a secure location. Remember, this file can be used to access your database, so keep it secure.
  6. Create a directory, and extract the client credentials zip file into that directory. You should now have the following files:
    • cwallet.sso
    • ewallet.p12
    • keystore.jks
    • ojdbc.properties
    • sqlnet.ora
    • tnsnames.ora
    • truststore.jks
  7. Edit the sqlnet.ora file. Set the DIRECTORY value to the directory used in step 6, for example: 
    (DIRECTORY="/home/demouser/projects/ATP/Wallet_Creds")
    

Figure 1: Making the DB connection

Figure 1: Making the database connection

Figure 2: Downloading the client credentials

Figure 2: Downloading the client credentials

Figure 3: Creating a password for your client credentials

Figure 3: Creating a password for your client credentials

Test the Connection: Optional but Recommended

Now test the connection from your Oracle SQLcl or Oracle SQL Developer tool.

Oracle SQLcl. To test the connection from Oracle SQLcl, do the following:

  1. Start Oracle SQLcl in nolog mode.
    sql /nolog
    
  2. Set the location of your credentials zip file.
    set cloudconfig /home/demouser/projects/ATP/Wallet_Creds/
    client_credentials.zip
    Operation is successfully completed.
    Using temp directory:/tmp/oracle_cloud_config903805845690230771
    
  3. Connect with a schema/password that is safe for testing.
    connect myschema/mypassword@BlaineATP_MEDIUM
    Connected.
    
  4. If all goes well, you should now be connected and able to run a test query.
    select 'Connected to Oracle Autonomous Transaction
    Processing from SQLcl!' "Test It" from dual;
    
    Test It                                                          
    -----------------------------------------------------------------
    Connected to Oracle Autonomous Transaction Processing from SQLcl!
    
  5. Exit Oracle SQLcl.
    exit
    
    Disconnected from Oracle Database 18c Enterprise Edition 
    Release 18.0.0.0.0 – Production
    Version 18.4.0.0.0
    

Oracle SQL Developer. To test the connection from Oracle SQL Developer, do the following:

  1. Create a connection and give it a connection name, as shown in Figure 4.
  2. Enter the username for a schema that’s safe to use for testing.
  3. Enter the password.
  4. Choose Cloud Wallet from the Connection Type menu, as shown in Figure 4.
  5. For Configuration File, select the zip file for the client credentials you downloaded earlier.
  6. Select the service you want to use for your connection. (This list is populated from the Oracle wallet.)
  7. Click Test. If the test is a success, you can save the connection.
  8. (Optional) Open a SQL worksheet for that connection, and run a test query.

If the Oracle SQL Developer connection or query tests were a success, you know that your client credentials file is valid and that you are able to connect to the Oracle Autonomous Transaction Processing database instance.

Figure 4: Entering the connection name, username, password, connection type, configuration file, and service in Oracle SQL Developer

Figure 4: Entering the connection name, username, password, connection type, configuration file, and service in Oracle SQL Developer

Instructions for Connecting with Most Languages

To connect to Oracle Autonomous Transaction Processing with most programming languages, do the following:

  1. Download and install the Oracle Database driver for that language. Choose a driver that connects through Oracle Database Instant Client.
  2. Set the TNS_ADMIN environment variable to point to the directory from which you extracted the client credentials files.
  3. Create a database connection with the username, password, and service used in the above tests.
  4. Use the connection as you would any other Oracle Database connection in your application.

Language-Specific Examples

For the following examples, I have defined environment variables for TNS_ADMIN and the user, password, service, and connect string: 
export TNS_ADMIN="/home/demouser/projects/ATP/Wallet_Creds"
export OADB_USER='demo'
export OADB_PW='demoPassword'
export OADB_SERVICE='BlaineATP_MEDIUM'
export OADB_CONNECTSTRING="$OADB_USER/$OADB_PW@$OADB_SERVICE"

Python. To connect to the Oracle Autonomous Transaction Processing database instance with Python, do the following:

  1. Download and install the cx_Oracle driver.
  2. Create the python_demo.py file, with the following code:
    import cx_Oracle
    import os
    
    con = cx_Oracle.connect(os.environ['OADB_USER'], 
                            os.environ['OADB_PW'],
                            os.environ['OADB_SERVICE'])
    
    cur = con.cursor()
    
    cur.execute("select 'Connected to Oracle Autonomous Transaction 
    Processing from Python!' from dual")
    res = cur.fetchall()
    for row in res:
        print(row)
    
    cur.close()
    con.close()
    
  3. Run the python_demo.py file in Python with the following command:
    python python_demo.py
    
    The following response confirms your connection from Python to Oracle Autonomous Transaction Processing:
    ('Connected to Oracle Autonomous Transaction Processing 
    from Python!',)
    
Node.js. To connect to the Oracle Autonomous Transaction Processing database instance with Node.js, do the following:
  1. Download and install the Node-oracledb driver.
  2. Create the nodeDemo.js file, with the following code:
    var oracledb = require('oracledb');
    
    oracledb.getConnection({
        user: process.env.OADB_USER,
        password: process.env.OADB_PW,
        connectString: process.env.OADB_SERVICE
      },
      function(err, connection) {
        if (err) {
          console.error(err.message);
          return;
        }
        connection.execute(
          "select 'Connected to Oracle Autonomous 
           Transaction Processing from Node.js!' 
           from dual",
          function(err, result) {
            if (err) {
              console.error(err);
              return;
            }
            console.log(result.rows);
          });
      });
    
  3. Run the nodeDemo.js file in Node.js with the following command:
    node nodeDemo.js
    
    The following response confirms your connection from Node.js to Oracle Autonomous Transaction Processing:
    [ [ 'Connected to Oracle Autonomous Transaction
    Processing from Node.js!' ] ]
    

Ruby. To connect to the Oracle Autonomous Transaction Processing database instance with Ruby, do the following:

  1. Download and install the ruby-oci8 driver.
  2. Create the ruby-demo.rb file, with the following code:
    require 'oci8'
    
    con = OCI8.new(ENV['OADB_USER'],
                   ENV['OADB_PW'],
                   ENV['OADB_SERVICE']);
    
    statement = "select 'Connected to Oracle 
    Autonomous Transaction Processing from Ruby!' 
    from dual";
    cursor = con.parse(statement)
    cursor.exec
    cursor.fetch() {|row|
      printf "%s\n", row[0]
    }
    
  3. Run the ruby-demo.rb file in Ruby with the following command:
    	ruby ruby-demo.rb
    	
    The following response confirms your connection from Ruby to Oracle Autonomous Transaction Processing:
    Connected to Oracle Autonomous Transaction Processing
    from Ruby!

Go. To connect to the Oracle Autonomous Transaction Processing database instance with Go, do the following:

  1. Download and install the goracle driver.
  2. Create the goDemo.go file, with the following code:
    package main
    
    import (
      "database/sql"
      _ "gopkg.in/goracle.v2"
      "fmt"
      "os"
    )
    
    func main() {
      connectString := os.Getenv("OADB_CONNECTSTRING")
    
      db, err := sql.Open("goracle", connectString)
      if err != nil {
        panic(err)
      }
      defer db.Close()
    
      row := db.QueryRow("select 'Connected to 
             Oracle Autonomous Transaction 
             Processing from Go!'
             from dual")
    
      var strCol string
      err = row.Scan(&strCol)
    
      if err != nil {
        panic(err)
      }
    
      fmt.Printf("%s\n", strCol)
    }
    
  3. Run the goDemo.go file in Go with the following command:
    go build goDemo.go
    ./goDemo
    
    The following response confirms your connection from Go to Oracle Autonomous Transaction Processing:
    Connected to Oracle Autonomous Transaction Processing from Go!
    

Other languages. Figure 5 contains a list of some of the Oracle Database drivers available for a few other languages—there are many more.

Figure 5: Oracle Database languages and drivers supported

Figure 5: Oracle Database languages and drivers supported

I have not personally tested an Oracle Autonomous Transaction Processing connection with every one of the drivers in Figure 5, but the methods used in this article should work for all of them. If you have difficulty making the connection with one of these drivers, please reach out to me, and I will do my best to help make it work.

Next Steps

LEARN more about Oracle Autonomous Transaction Processing.

TRY Oracle Autonomous Transaction Processing.

Illustration by Wes Rowell