Friday, December 18, 2015

Golang connect to Oracle database

Purpose: setup Go Oracle driver, and a complete code to demo some basic DDL, DML, and bulk batching processing.

Minimum requirements are
- Go 1.3 with CGO enabled,
- a GCC C compiler,
- and Oracle 11g ( or Oracle Instant Client (

This demo is tested on Go 1.5.2, Oracle Virtual Box, imported OTN_Developer_Day_VM.ova appliance/image file.

** environment settings **

------- Linux ----------
export GOROOT=/usr/local/go
#export GOPATH=/home/oracle/go
export GOPATH=/media/sf_GTD/Project/Go
export PATH=$PATH:/usr/local/go/bin:$GOPATH/bin
mkdir -p $GOPATH/src/
cd $GOPATH/src/
echo $PATH
echo $GOPATH
# Oracle client driver, environment parameters
# Set the CGO_CFLAGS and CGO_LDFLAGS environment variables to locate the OCI headers and library,
export CGO_CFLAGS=-I$ORACLE_HOME/rdbms/public
export CGO_LDFLAGS="-L$ORACLE_HOME/lib -lclntsh"

-- install Oracle driver --
go get

-- manual install driver from source zip files --
download source zip files, and extract into $GOPATH/src/ , or rename ora-master to ora.v3

download address:

The files structure will be looks like,

To import this package, add the following line to your code:
import ""


// Copyright 2015, Author: Charlie Database Craftsman. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.

Package main demo OCI array interface.
It connects to a database, create table, insert data, and query the data.

package main

import (

func main() {
 // example usage of the ora package driver
 // connect to a server and open a session
 env, err := ora.OpenEnv(nil)
 defer env.Close()
 if err != nil {
 srvCfg := ora.NewSrvCfg()
 srvCfg.Dblink = "//localhost/orcl"
 srv, err := env.OpenSrv(srvCfg)
 defer srv.Close()
 if err != nil {
 sesCfg := ora.NewSesCfg()
 sesCfg.Username = "scott"
 sesCfg.Password = "tiger"
 ses, err := srv.OpenSes(sesCfg)
 defer ses.Close()
 if err != nil {

 //StmtCfg.PrefetchRowCount = 1000
 stmtTbl, err := ses.Prep(
  l_sql varchar2(32767);
  l_cnt pls_integer;
  l_sql := 'drop TABLE emp_go purge';
  select count(*) into l_cnt from user_tables where table_name='EMP_GO';
  if l_cnt > 0 then
    execute immediate l_sql;
  end if;
 defer stmtTbl.Close()
 if err != nil {
 rowsAffected, err := stmtTbl.Exe()
 if err != nil {
 fmt.Println(rowsAffected, " rows Affected. drop table emp_go if exists.")

 rowsAffected, err = ses.PrepAndExe("CREATE TABLE emp_go (empno number(5,0), ename VARCHAR2(50))")
 if err != nil {
 fmt.Println("table emp_go created")

 tx1, err := ses.StartTx()
 rowsAffected, err = ses.PrepAndExe("delete emp_go")

 stmt, err := ses.Prep("INSERT INTO emp_go (empno, ename) VALUES (:N1, :C1)")
 defer stmt.Close()
 rowsAffected, err = stmt.Exe(uint64(1001), "Charlie")
 rowsAffected, err = stmt.Exe(uint64(1002), "Vicky")
 if err != nil {
 fmt.Println(rowsAffected, "add")


 fmt.Println("Demo fetch records")
 stmtQry, err := ses.Prep("SELECT empno, ename FROM emp_go")
 defer stmtQry.Close()
 if err != nil {
 rset, err := stmtQry.Qry()
 if err != nil {
 for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
 if rset.Err != nil {


$> go run oracle_demo.go

0  rows Affected. drop table emp_go if exists.
table emp_go created
1 add
Demo fetch records
1001 Charlie
1002 Vicky



No comments: