Build a Go CLI to Connect and Query Oracle DB in Minutes

Overview

This article explains how to develop a command‑line utility in Go that lets users provide connection details and a SQL statement to test login to an Oracle database and run a simple query.

Implementation

The existing loginWithServiceName function is wrapped in a CLI program. The flag package parses the following command‑line arguments:

host – Oracle database host address

port – Oracle database port

username – user name

password – password

serviceName – service name

sql – SQL query to execute

package main

import (
    "database/sql"
    "flag"
    "fmt"
    "log"
    "strconv"

    go_ora "github.com/sijms/go-ora/v2"
)

type OracleConnectRole string

const (
    OracleConnectRoleSysdba  OracleConnectRole = "sysdba"
    OracleConnectRoleSysoper OracleConnectRole = "sysoper"
    OracleConnectRoleNormal  OracleConnectRole = "normal"
)

func loginWithServiceName(host, port, username, password, serviceName string, role OracleConnectRole) (*sql.DB, error) {
    urlOptions := map[string]string{}
    switch role {
    case OracleConnectRoleSysdba:
        urlOptions["dba privilege"] = string(role)
    case OracleConnectRoleSysoper:
        urlOptions["dba privilege"] = string(role)
    }
    iPort, err := strconv.Atoi(port)
    if err != nil {
        return nil, err
    }
    connStr := go_ora.BuildUrl(host, iPort, serviceName, username, password, urlOptions)
    conn, err := sql.Open("oracle", connStr)
    if err != nil {
        return nil, err
    }
    // check for error
    err = conn.Ping()
    if err != nil {
        return nil, err
    }
    return conn, nil
}

func main() {
    host := flag.String("host", "", "Oracle DB host")
    port := flag.String("port", "1521", "Oracle DB port")
    username := flag.String("username", "", "Username")
    password := flag.String("password", "", "Password")
    serviceName := flag.String("serviceName", "", "Service name")
    sqlQuery := flag.String("sql", "", "SQL query to execute")

    flag.Parse()

    if *host == "" || *username == "" || *password == "" || *serviceName == "" || *sqlQuery == "" {
        log.Fatal("All parameters are required")
    }

    conn, err := loginWithServiceName(*host, *port, *username, *password, *serviceName, OracleConnectRoleNormal)
    if err != nil {
        log.Fatalf("Failed to connect to Oracle DB: %v", err)
    }
    defer conn.Close()

    rows, err := conn.Query(*sqlQuery)
    if err != nil {
        log.Fatalf("Failed to execute query: %v", err)
    }
    defer rows.Close()

    cols, err := rows.Columns()
    if err != nil {
        log.Fatalf("Failed to get columns: %v", err)
    }

    for rows.Next() {
        columns := make([]interface{}, len(cols))
        columnPointers := make([]interface{}, len(cols))
        for i := range columns {
            columnPointers[i] = &columns[i]
        }

        if err := rows.Scan(columnPointers...); err != nil {
            log.Fatalf("Failed to scan row: %v", err)
        }

        for i, colName := range cols {
            fmt.Printf("%s: %v
", colName, columns[i])
        }
        fmt.Println("-----------------------------------")
    }

    if err := rows.Err(); err != nil {
        log.Fatalf("Error during rows iteration: %v", err)
    }
}

Running the Tool

After compiling, invoke the binary with the required flags to connect and run a query.

go build -o oracle-cli
./oracle-cli -host=127.0.0.1 -port=1521 -username=sys -password=mypassword -serviceName=pdb1 -sql="SELECT * FROM mytable"

Conclusion

By following the steps and code example, you can quickly create a Go CLI that connects to an Oracle database, executes arbitrary SQL, and prints the results, providing a handy utility for database administration and debugging.