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 (11.2.0.4.0) or Oracle Instant Client (11.2.0.4.0).

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/github.com/user/hello
cd $GOPATH/src/github.com/user/hello
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 github.com/rana/ora

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

download address: https://github.com/rana/ora/tree/v3

The files structure will be looks like,
src\gopkg.in\rana\ora.v3\
                         ora.go
                         env.go
                         ...
                         \examples\...


To import this package, add the following line to your code:
import "gopkg.in/rana/ora.v3"

Code:

// 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.

E:\GTD\Project\Go\src\github.com\user\hello\oracle_demo.go
*/
package main


import (
 "fmt"
 "gopkg.in/rana/ora.v3"
)


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 {
  panic(err)
 }
 srvCfg := ora.NewSrvCfg()
 srvCfg.Dblink = "//localhost/orcl"
 srv, err := env.OpenSrv(srvCfg)
 defer srv.Close()
 if err != nil {
  panic(err)
 }
 sesCfg := ora.NewSesCfg()
 sesCfg.Username = "scott"
 sesCfg.Password = "tiger"
 ses, err := srv.OpenSes(sesCfg)
 fmt.Println("connected")
 defer ses.Close()
 if err != nil {
  panic(err)
 }

 //StmtCfg.PrefetchRowCount = 1000
 stmtTbl, err := ses.Prep(
  `declare
  l_sql varchar2(32767);
  l_cnt pls_integer;
begin
  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;
end;`)
 defer stmtTbl.Close()
 if err != nil {
  panic(err)
 }
 rowsAffected, err := stmtTbl.Exe()
 if err != nil {
  panic(err)
 }
 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 {
  panic(err)
 }
 fmt.Println("table emp_go created")

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

 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 {
  panic(err)
 }
 fmt.Println(rowsAffected, "add")

 tx1.Commit()
 tx1.Rollback()
 fmt.Println("commit")

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


Output:

$> go run oracle_demo.go

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

.

Reference:
https://github.com/rana/ora/blob/master/README.md
http://gopkg.in/rana/ora.v3

No comments: