Monday, July 19, 2021

How to Connect a Go program to Oracle Autonomous Database

In this blog post, we will see how to connect Oracle Autonomous Database in Go programming language. This can be divided into three parts.

Part 1) We need to download Autonomous Database client credentials.  More details

In the Autonomous database OCI console, open to the service console of the DB which we need to connect. There in the Administration tab, we will find the option to download the client credentials.



 Once It is downloaded, unzip the same to a directory of choice.

Part 2) To connect to the database we need the database user, password, and the connect string.

We can use the admin user, which was provided as part of the database provision, or any other database user. Here we need to form the connect string.

example: protocol://host:port/service_name?wallet_location=/my/dir&retry_count=3&retry_delay=20

We need to form our connect string for the autonomous database we want to connect. We will get details in the tnsnames.ora file which is part of the zip we had downloaded in above Part 1.

There we will find many connection services (more details about them), We can pick the one we want to use for our connection.

example: 

db202107181649_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

Now by referring to this we can create our connection string like below.

tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/

The wallet_location is the path where we had unzipped our client credentials in above Part 1.

Part 3) Now let's write our go code.

We will use godror package to connect. 

Install godor # go get github.com/godror/godror

We need runtime dependency for Oracle Client libraries, We can download the free basic or free light version from https://www.oracle.com/database/technologies/instant-client/downloads.html.

package main

import (
"database/sql"
"fmt"

_ "github.com/godror/godror"
)

func main() {
connectToADb()
}

func connectToADb() {
fmt.Println("Connecting to Oracle Autonoumus database !!!")
db, err := sql.Open("godror", `user="admin" password="testPwd"
connectString="tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/"
libDir="/Users/pallab/tool/instantclient_19_8/"`)
if err != nil {
fmt.Println(err)
return
}
defer db.Close()

rows, err := db.Query("select 'hello' from dual")
if err != nil {
fmt.Println("Error running query")
fmt.Println(err)
return
}
defer rows.Close()

var resData string
for rows.Next() {

rows.Scan(&resData)
}
fmt.Printf("The response is: %s\n", resData)
}

Note: In sql.open() we have passed our oracle client libraries which we downloaded and unzipped as libDir  parameter.

Useful Links

1. https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

2. https://blogs.oracle.com/developers/how-to-connect-a-go-program-to-oracle-database-using-goracle

3. https://godror.github.io/godror/doc/installation.html