Golang chatting with Oracle


20.03.2019
by Kamil Stawiarski

Python is awesome. I like Python very much, but there are some cases where Python’s performance is just not enough. What other options do we have?
Well, there’s for example GoLang

Problem with GoLang is that there is not a lot on the Internet about using it with Oracle Databases. Let me show you some basic steps for configuring the environment.

The first step is to install GoLang – instructions can be found in here: https://golang.org/doc/install

To connect to Oracle, you need Oracle Client (it can be Oracle Instant Client).
I will configure my environment on my MAC and use Oracle Instant Client for MAC

piclerick:~ inter$ mkdir oracle
piclerick:~ inter$ cd oracle
piclerick:oracle inter$ ls
piclerick:oracle inter$ unzip ~/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/BASIC_README
  inflating: instantclient_18_1/adrci
  inflating: instantclient_18_1/genezi
    linking: instantclient_18_1/libclntsh.dylib  -> libclntsh.dylib.18.1
  inflating: instantclient_18_1/libclntsh.dylib.18.1
  inflating: instantclient_18_1/libclntshcore.dylib.18.1
  inflating: instantclient_18_1/libnnz18.dylib
    linking: instantclient_18_1/libocci.dylib  -> libocci.dylib.18.1
  inflating: instantclient_18_1/libocci.dylib.18.1
  inflating: instantclient_18_1/libociei.dylib
  inflating: instantclient_18_1/libocijdbc18.dylib
  inflating: instantclient_18_1/libons.dylib
  inflating: instantclient_18_1/liboramysql18.dylib
   creating: instantclient_18_1/network/
  inflating: instantclient_18_1/ojdbc8.jar
  inflating: instantclient_18_1/ucp.jar
  inflating: instantclient_18_1/uidrvci
  inflating: instantclient_18_1/xstreams.jar
   creating: instantclient_18_1/network/admin/
  inflating: instantclient_18_1/network/admin/README
finishing deferred symbolic links:
  instantclient_18_1/libclntsh.dylib -> libclntsh.dylib.18.1
  instantclient_18_1/libocci.dylib -> libocci.dylib.18.1
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/SQLPLUS_README
  inflating: instantclient_18_1/glogin.sql
  inflating: instantclient_18_1/libsqlplus.dylib
  inflating: instantclient_18_1/libsqlplusic.dylib
  inflating: instantclient_18_1/sqlplus
piclerick:oracle inter$ unzip ~/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
Archive:  /Users/inter/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
  inflating: instantclient_18_1/TOOLS_README
  inflating: instantclient_18_1/exp
  inflating: instantclient_18_1/expdp
  inflating: instantclient_18_1/imp
  inflating: instantclient_18_1/impdp
  inflating: instantclient_18_1/libnfsodm18.dylib
  inflating: instantclient_18_1/sqlldr
  inflating: instantclient_18_1/wrc
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
Archive:  /Users/inter/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
   creating: instantclient_18_1/sdk/
   creating: instantclient_18_1/sdk/admin/
  inflating: instantclient_18_1/sdk/admin/oraaccess.xsd
   creating: instantclient_18_1/sdk/demo/
  inflating: instantclient_18_1/sdk/demo/cdemo81.c
  inflating: instantclient_18_1/sdk/demo/demo.mk
  inflating: instantclient_18_1/sdk/demo/occidemo.sql
  inflating: instantclient_18_1/sdk/demo/occidemod.sql
  inflating: instantclient_18_1/sdk/demo/occidml.cpp
  inflating: instantclient_18_1/sdk/demo/occiobj.cpp
  inflating: instantclient_18_1/sdk/demo/occiobj.typ
  inflating: instantclient_18_1/sdk/demo/oraaccess.xml
  inflating: instantclient_18_1/sdk/demo/setuporamysql.sh
   creating: instantclient_18_1/sdk/include/
  inflating: instantclient_18_1/sdk/include/ldap.h
  inflating: instantclient_18_1/sdk/include/nzerror.h
  inflating: instantclient_18_1/sdk/include/nzt.h
  inflating: instantclient_18_1/sdk/include/occi.h
  inflating: instantclient_18_1/sdk/include/occiAQ.h
  inflating: instantclient_18_1/sdk/include/occiCommon.h
  inflating: instantclient_18_1/sdk/include/occiControl.h
  inflating: instantclient_18_1/sdk/include/occiData.h
  inflating: instantclient_18_1/sdk/include/occiObjects.h
  inflating: instantclient_18_1/sdk/include/oci.h
  inflating: instantclient_18_1/sdk/include/oci1.h
  inflating: instantclient_18_1/sdk/include/oci8dp.h
  inflating: instantclient_18_1/sdk/include/ociap.h
  inflating: instantclient_18_1/sdk/include/ociapr.h
  inflating: instantclient_18_1/sdk/include/ocidef.h
  inflating: instantclient_18_1/sdk/include/ocidem.h
  inflating: instantclient_18_1/sdk/include/ocidfn.h
  inflating: instantclient_18_1/sdk/include/ociextp.h
  inflating: instantclient_18_1/sdk/include/ocikpr.h
  inflating: instantclient_18_1/sdk/include/ociver.h
  inflating: instantclient_18_1/sdk/include/ocixmldb.h
  inflating: instantclient_18_1/sdk/include/ocixstream.h
  inflating: instantclient_18_1/sdk/include/odci.h
  inflating: instantclient_18_1/sdk/include/orastruc.h
  inflating: instantclient_18_1/sdk/include/oratypes.h
  inflating: instantclient_18_1/sdk/include/oraxml.h
  inflating: instantclient_18_1/sdk/include/oraxml.hpp
  inflating: instantclient_18_1/sdk/include/oraxmlcg.h
  inflating: instantclient_18_1/sdk/include/oraxsd.h
  inflating: instantclient_18_1/sdk/include/oraxsd.hpp
  inflating: instantclient_18_1/sdk/include/ori.h
  inflating: instantclient_18_1/sdk/include/orid.h
  inflating: instantclient_18_1/sdk/include/orl.h
  inflating: instantclient_18_1/sdk/include/oro.h
  inflating: instantclient_18_1/sdk/include/ort.h
  inflating: instantclient_18_1/sdk/include/xa.h
  inflating: instantclient_18_1/sdk/include/xml.h
  inflating: instantclient_18_1/sdk/include/xml.hpp
  inflating: instantclient_18_1/sdk/include/xmlctx.hpp
  inflating: instantclient_18_1/sdk/include/xmldav.h
  inflating: instantclient_18_1/sdk/include/xmldf.h
  inflating: instantclient_18_1/sdk/include/xmlerr.h
  inflating: instantclient_18_1/sdk/include/xmlev.h
  inflating: instantclient_18_1/sdk/include/xmlotn.h
  inflating: instantclient_18_1/sdk/include/xmlotn.hpp
  inflating: instantclient_18_1/sdk/include/xmlproc.h
  inflating: instantclient_18_1/sdk/include/xmlsch.h
  inflating: instantclient_18_1/sdk/include/xmlsoap.h
  inflating: instantclient_18_1/sdk/include/xmlsoap.hpp
  inflating: instantclient_18_1/sdk/include/xmlsoapc.hpp
  inflating: instantclient_18_1/sdk/include/xmlurl.h
  inflating: instantclient_18_1/sdk/include/xmlxptr.h
  inflating: instantclient_18_1/sdk/include/xmlxsl.h
  inflating: instantclient_18_1/sdk/include/xmlxvm.h
  inflating: instantclient_18_1/sdk/ott
 extracting: instantclient_18_1/sdk/ottclasses.zip
  inflating: instantclient_18_1/sdk/SDK_README

To connect to Oracle, we will use goracle – it is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga’s excellent OCI wrapper, ODPI-C.

Let’s get the package:

piclerick:~ inter$ go get gopkg.in/goracle.v2

Now we can set the environment variables and create our source code:

piclerick:~ inter$ export  ORACLE_HOME=/Users/inter/oracle/instantclient_18_1
piclerick:~ inter$ export DYLD_LIBRARY_PATH=/Users/inter/oracle/instantclient_18_1
piclerick:~ inter$ vim chatt_with_oracle.go

The base for our script will look like this:

package main

import (
        "fmt"
        "database/sql"
        "log"
        _ "gopkg.in/goracle.v2"
)

func main() {

}

Please note that we have to import goracle a bit differently than other packages – with "_" prefix. That’s because we will connect to the database like this: sql.Open("goracle", conn). So the "goracle" won’t be used directly in a code but as a string literal to SQL driver.

Let’s create a connection to the database and execute a simple query

package main

import (
        "fmt"
        "database/sql"
        "log"
        _ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
        log.Panic(err)
  }

  //execute a query and get pointer to the rows
  rows, err := db.Query("select count(1) from employees")

  //check if there were some errors during execution
  if err != nil {
        log.Panic(err)
  }

  //define variable for the output
  var emps_cnt int

  //if there are some rows - put the output to the variable address by reference
  if rows.Next() {
        rows.Scan(&emps_cnt)
  }

  fmt.Println(emps_cnt)

}

Now we can compile our program and execute it:

piclerick:~ inter$ go build chatt_with_oracle.go
piclerick:~ inter$ ./chatt_with_oracle
107

Great! Let’s add more rows to the output and additional bind variable:

package main

import (
	"fmt"
	"database/sql"
	"log"
	_ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
	log.Panic(err)
  }

  //execute a query and get pointer to the rows
  deptId := 20
  rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)

  //check if there were some errors during execution
  if err != nil {
	log.Panic(err)
  }

  //define variable for the output
  var salary int
  var fname, lname string

  //if there are some rows - put the output to the variable address by reference
  for rows.Next() {
  	rows.Scan(&fname, &lname, &salary)
        fmt.Println(fname, lname, salary)
  }

}

And here’s the result:

piclerick:~ inter$ ./chatt_with_oracle
Michael Hartstein 26000
Pat Fay 12000

If you want to execute PL/SQL function or procedure and get the results, you can use sql.Out interface. For example, let’s create a sample PL/SQL function:

SQL> ed
Zapisano file afiedt.buf

  1  create or replace function f_get_dept_avg_sal(p_dept_id number) return number is
  2    v_avg_sal number;
  3  begin
  4    select avg(salary) into v_avg_sal
  5    from employees e
  6    where e.department_id=p_dept_id;
  7    return v_avg_sal;
  8* end;
  9  /

Utworzono funkcje.

Now we can add a simple PL/SQL block to our GO function and use Exec method and sql.Out interface:

package main

import (
	"fmt"
	"database/sql"
	"log"
	_ "gopkg.in/goracle.v2"
)

func main() {
  //connect to database
  db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")

  //close database connection at the end of program execution
  defer db.Close()

  //check if there were some errors during connection
  if err != nil {
	log.Panic(err)
  }

  //execute a query and get pointer to the rows
  deptId := 20
  rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)

  //check if there were some errors during execution
  if err != nil {
	log.Panic(err)
  }

  //define variable for the output
  var salary int
  var fname, lname string

  //if there are some rows - put the output to the variable address by reference
  for rows.Next() {
	rows.Scan(&fname, &lname, &salary)
        fmt.Println(fname, lname, salary)
  }

  //define variable for average salary
  var avgSal float64

  //execute anonymous block and use sql.Out interface for extracting bind variable value
  db.Exec("begin :1 := f_get_dept_avg_sal(:2); end;", sql.Out{Dest: &avgSal}, deptId)

  fmt.Printf("AVG salary in department %d is %f\n", deptId, avgSal)

}

And here’s the result:

piclerick:~ inter$ go build chatt_with_oracle.go
piclerick:~ inter$ ./chatt_with_oracle
Michael Hartstein 26000
Pat Fay 12000
AVG salary in department 20 is 19000.000000

And it’s simple as that 🙂

Search:


Kontakt

Database Whisperers sp. z o. o. sp. k.
Al. Jerozolimskie 200
Wejście B, III piętro/ pokój 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter zapisz się żeby być na bieżąco