Golang Oracle Database Driver

Package ora implements an Oracle database driver for the Go programming language.

An Oracle database may be accessed through the database/sql package or through the ora package directly. database/sql offers connection pooling, thread safety, a consistent API to multiple database technologies and a common set of Go types. The ora package offers additional features including pointers, slices, nullable types, numerics of various sizes, Oracle-specific types, Go return type configuration, and Oracle abstractions such as environment, server and session.

The ora package is written with the Oracle Call Interface (OCI) C-language libraries provided by Oracle. The OCI libraries are a standard for client application communication and driver communication with Oracle databases.

The ora package has been verified to work with

• Oracle Enterprise 12c (12.1.0.1.0), Windows 8.1 and AMD64.
• Oracle Standard 11g (11.2.0.4.0), Linux x86_64 (RHEL6)

---

• Installation
• Data Types
• SQL Placeholder Syntax
• Working With The Sql Package
• Working With The Oracle Package Directly
• Logging
• Test Database Setup
• Limitations
• License
• API Reference
• Examples

---

Installation

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

Install Oracle or Oracle Instant Client.

Set the CGO_CFLAGS and CGO_LDFLAGS environment variables to locate the OCI headers and library. For example:

// example OS environment variables for Oracle 12c on Windows
CGO_CFLAGS=-Ic:/oracle/home/OCI/include/
CGO_LDFLAGS=c:/oracle/home/BIN/oci.dll

// example OS environment variables for Linux with InstantClient 12.1
CGO_CFLAGS=-I/usr/lib/oracle/12.1/client64/lib
CGO_LDFLAGS=-L/usr/lib/oracle/12.1/client64/lib -lclntsh
// maybe you'll need to set LD_LIBRARY_FLAGS or a file under /etc/ld.so.conf.d/ with /usr/lib/oracle/12.1/client64/lib

CGO_CFLAGS identifies the location of the OCI header file. CGO_LDFLAGS identifies the location of the OCI library. These locations will vary based on whether an Oracle database is locally installed or whether the Oracle instant client libraries are locally installed.

The ora package has no external Go dependencies and is available on GitHub and gopkg.in:

go get gopkg.in/rana/ora.v3

Data Types

The ora package supports all built-in Oracle data types. The supported Oracle built-in data types are NUMBER, BINARY_DOUBLE, BINARY_FLOAT, FLOAT, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, LONG, CLOB, NCLOB, BLOB, LONG RAW, RAW, ROWID and BFILE. SYS_REFCURSOR is also supported.

Oracle does not provide a built-in boolean type. Oracle provides a single-byte character type. A common practice is to define two single-byte characters which represent true and false. The ora package adopts this approach. The oracle package associates a Go bool value to a Go rune and sends and receives the rune to a CHAR(1 BYTE) column or CHAR(1 CHAR) column.

The default false rune is zero 0. The default true rune is one 1. The bool rune association may be configured or disabled when directly using the ora package but not with the database/sql package.

SQL Placeholder Syntax

Within a SQL string a placeholder may be specified to indicate where a Go variable is placed. The SQL placeholder is an Oracle identifier, from 1 to 30 characters, prefixed with a colon :. For example:

// example Oracle placeholder uses a colon
INSERT INTO T1 (C1) VALUES (:C1)

Placeholders within a SQL statement are bound by position. The actual name is not used by the ora package driver e.g., placeholder names :c1, :1, or :xyz are treated equally.

Working With The Sql Package

You may access an Oracle database through the database/sql package. The database/sql package offers a consistent API across different databases, connection pooling, thread safety and a set of common Go types. database/sql makes working with Oracle straight-forward.

The ora package implements interfaces in the database/sql/driver package enabling database/sql to communicate with an Oracle database. Using database/sql ensures you never have to call the ora package directly.

When using database/sql, the mapping between Go types and Oracle types may be changed slightly. The database/sql package has strict expectations on Go return types. The Go-to-Oracle type mapping for database/sql is:

Go type		Oracle type

int64		NUMBER°, BINARY_DOUBLE, BINARY_FLOAT, FLOAT

float64		NUMBER¹, BINARY_DOUBLE, BINARY_FLOAT, FLOAT

time.Time	TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, DATE

string		CHAR², NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, LONG, CLOB, NCLOB

bool		CHAR(1 BYTE)³, CHAR(1 CHAR)³

[]byte		BLOB, LONG RAW, RAW


° A select-list column defined as an Oracle NUMBER with zero scale e.g.,
NUMBER(10,0) is returned as an int64. Either int64 or float64 may be inserted
into a NUMBER column with zero scale. float64 insertion will have its fractional
part truncated.

¹ A select-list column defined as an Oracle NUMBER with a scale greater than
zero e.g., NUMBER(10,4) is returned as a float64. Either int64 or float64 may
be inserted into a NUMBER column with a scale greater than zero.

² A select-list column defined as an Oracle CHAR with a length greater than 1
e.g., CHAR(2 BYTE) or CHAR(2 CHAR) is returned as a string. A Go string of any
length up to the column max length may be inserted into the CHAR column.

³ The Go bool value false is mapped to the zero rune '0'. The Go bool value
true is mapped to the one rune '1'.

You may specify an optional *DrvCfg to ora.SetDrvCfg to configure various configuration options including statement configuration and Rset configuration.

func init() {
	drvCfg := ora.NewDrvCfg()
	drvCfg.Env.StmtCfg.FalseRune = 'N'
	drvCfg.Env.StmtCfg.TrueRune = 'Y'
	drvCfg.Env.StmtCfg.Rset.TrueRune = 'Y'
	ora.SetDrvCfg(drvCfg)
}

When configuring the driver for use with database/sql, keep in mind that database/sql has strict Go type-to-Oracle type mapping expectations.

Working With The Oracle Package Directly

The ora package allows programming with pointers, slices, nullable types, numerics of various sizes, Oracle-specific types, Go return type configuration, and Oracle abstractions such as environment, server and session. When working with the ora package directly, the API is slightly different than database/sql.

When using the ora package directly, the mapping between Go types and Oracle types may be changed. The Go-to-Oracle type mapping for the ora package is:

Go type							Oracle type

int64, int32, int16, int8		NUMBER°, BINARY_DOUBLE, BINARY_FLOAT, FLOAT
uint64, uint32, uint16, uint8
Int64, Int32, Int16, Int8
Uint64, Uint32, Uint16, Uint8
*int64, *int32, *int16, *int8
*uint64, *uint32, *uint16, *uint8
[]int64, []int32, []int16, []int8
[]uint64, []uint32, []uint16, []uint8
[]Int64, []Int32, []Int16, []Int8
[]Uint64, []Uint32, []Uint16, []Uint8

float64, float32				NUMBER¹, BINARY_DOUBLE, BINARY_FLOAT, FLOAT
Float64, Float32
*float64, *float32
[]float64, []float32
[]Float64, []Float32

time.Time						TIMESTAMP, TIMESTAMP WITH TIME ZONE,
Time							TIMESTAMP WITH LOCAL TIME ZONE, DATE
*time.Time
[]time.Time
[]Time

string							CHAR², NCHAR, VARCHAR, VARCHAR2,
String							NVARCHAR2, LONG, CLOB, NCLOB, ROWID
*string
[]string
[]String

bool							CHAR(1 BYTE)³, CHAR(1 CHAR)³
Bool
*bool
[]bool
[]Bool

[]byte, [][]byte				BLOB

Lob, []Lob, *Lob				BLOB, CLOB

Raw, []Raw						RAW, LONG RAW

IntervalYM						INTERVAL MONTH TO YEAR
[]IntervalYM

IntervalDS						INTERVAL DAY TO SECOND
[]IntervalDS

Bfile							BFILE

° A select-list column defined as an Oracle NUMBER with zero scale e.g.,
NUMBER(10,0) is returned as an int64 by default. Integer and floating point
numerics may be inserted into a NUMBER column with zero scale. Inserting a
floating point numeric will have its fractional part truncated.

¹ A select-list column defined as an Oracle NUMBER with a scale greater than
zero e.g., NUMBER(10,4) is returned as a float64 by default. Integer and
floating point numerics may be inserted into a NUMBER column with a scale
greater than zero.

² A select-list column defined as an Oracle CHAR with a length greater than 1
e.g., CHAR(2 BYTE) or CHAR(2 CHAR) is returned as a string. A Go string of any
length up to the column max length may be inserted into the CHAR column.

³ The Go bool value false is mapped to the zero rune '0'. The Go bool value
true is mapped to the one rune '1'.

An example of using the ora package directly:

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 = "orcl"
	srv, err := env.OpenSrv(srvCfg)
	defer srv.Close()
	if err != nil {
		panic(err)
	}
	sesCfg := ora.NewSesCfg()
	sesCfg.Username = "test"
	sesCfg.Password = "test"
	ses, err := srv.OpenSes(sesCfg)
	defer ses.Close()
	if err != nil {
		panic(err)
	}

	// create table
	tableName := "t1"
	stmtTbl, err := ses.Prep(fmt.Sprintf("CREATE TABLE %v "+
		"(C1 NUMBER(19,0) GENERATED ALWAYS AS IDENTITY "+
		"(START WITH 1 INCREMENT BY 1), C2 VARCHAR2(48 CHAR))", tableName))
	defer stmtTbl.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err := stmtTbl.Exe()
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

	// begin first transaction
	tx1, err := ses.StartTx()
	if err != nil {
		panic(err)
	}

	// insert record
	var id uint64
	str := "Go is expressive, concise, clean, and efficient."
	stmtIns, err := ses.Prep(fmt.Sprintf(
		"INSERT INTO %v (C2) VALUES (:C2) RETURNING C1 INTO :C1", tableName))
	defer stmtIns.Close()
	rowsAffected, err = stmtIns.Exe(str, &id)
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

	// insert nullable String slice
	a := make([]ora.String, 4)
	a[0] = ora.String{Value: "Its concurrency mechanisms make it easy to"}
	a[1] = ora.String{IsNull: true}
	a[2] = ora.String{Value: "It's a fast, statically typed, compiled"}
	a[3] = ora.String{Value: "One of Go's key design goals is code"}
	stmtSliceIns, err := ses.Prep(fmt.Sprintf(
		"INSERT INTO %v (C2) VALUES (:C2)", tableName))
	defer stmtSliceIns.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtSliceIns.Exe(a)
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

	// fetch records
	stmtQry, err := ses.Prep(fmt.Sprintf(
		"SELECT C1, C2 FROM %v", tableName))
	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)
	}

	// commit first transaction
	err = tx1.Commit()
	if err != nil {
		panic(err)
	}

	// begin second transaction
	tx2, err := ses.StartTx()
	if err != nil {
		panic(err)
	}
	// insert null String
	nullableStr := ora.String{IsNull: true}
	stmtTrans, err := ses.Prep(fmt.Sprintf(
		"INSERT INTO %v (C2) VALUES (:C2)", tableName))
	defer stmtTrans.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtTrans.Exe(nullableStr)
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)
	// rollback second transaction
	err = tx2.Rollback()
	if err != nil {
		panic(err)
	}

	// fetch and specify return type
	stmtCount, err := ses.Prep(fmt.Sprintf(
		"SELECT COUNT(C1) FROM %v WHERE C2 IS NULL", tableName), ora.U8)
	defer stmtCount.Close()
	if err != nil {
		panic(err)
	}
	rset, err = stmtCount.Qry()
	if err != nil {
		panic(err)
	}
	row := rset.NextRow()
	if row != nil {
		fmt.Println(row[0])
	}
	if rset.Err != nil {
		panic(rset.Err)
	}

	// create stored procedure with sys_refcursor
	stmtProcCreate, err := ses.Prep(fmt.Sprintf(
		"CREATE OR REPLACE PROCEDURE PROC1(P1 OUT SYS_REFCURSOR) AS BEGIN "+
			"OPEN P1 FOR SELECT C1, C2 FROM %v WHERE C1 > 2 ORDER BY C1; "+
			"END PROC1;",
		tableName))
	defer stmtProcCreate.Close()
	rowsAffected, err = stmtProcCreate.Exe()
	if err != nil {
		panic(err)
	}

	// call stored procedure
	// pass *Rset to Exe to receive the results of a sys_refcursor
	stmtProcCall, err := ses.Prep("CALL PROC1(:1)")
	defer stmtProcCall.Close()
	if err != nil {
		panic(err)
	}
	procRset := &ora.Rset{}
	rowsAffected, err = stmtProcCall.Exe(procRset)
	if err != nil {
		panic(err)
	}
	if procRset.IsOpen() {
		for procRset.Next() {
			fmt.Println(procRset.Row[0], procRset.Row[1])
		}
		if procRset.Err != nil {
			panic(procRset.Err)
		}
		fmt.Println(procRset.Len())
	}

	// Output:
	// 0
	// 1
	// 4
	// 1 Go is expressive, concise, clean, and efficient.
	// 2 Its concurrency mechanisms make it easy to
	// 3
	// 4 It's a fast, statically typed, compiled
	// 5 One of Go's key design goals is code
	// 1
	// 1
	// 3
	// 4 It's a fast, statically typed, compiled
	// 5 One of Go's key design goals is code
	// 3
}

Pointers may be used to capture out-bound values from a SQL statement such as an insert or stored procedure call. For example, a numeric pointer captures an identity value:

// given:
// CREATE TABLE T1 (
// C1 NUMBER(19,0) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
// C2 VARCHAR2(48 CHAR))
var id int64
stmt, err = ses.Prep("INSERT INTO T1 (C2) VALUES ('GO') RETURNING C1 INTO :C1")
stmt.Exe(&id)

A string pointer captures an out parameter from a stored procedure:

// given:
// CREATE OR REPLACE PROCEDURE PROC1 (P1 OUT VARCHAR2) AS BEGIN P1 := 'GO'; END PROC1;
var str string
stmt, err = ses.Prep("CALL PROC1(:1)")
stmt.Exe(&str)

Slices may be used to insert multiple records with a single insert statement:

// insert one million rows with single insert statement
// given: CREATE TABLE T1 (C1 NUMBER)
values := make([]int64, 1000000)
for n, _ := range values {
	values[n] = int64(n)
}
rowsAffected, err := ses.PrepAndExe("INSERT INTO T1 (C1) VALUES (:C1)", values)

The ora package provides nullable Go types to support DML operations such as insert and select. The nullable Go types provided by the ora package are Int64, Int32, Int16, Int8, Uint64, Uint32, Uint16, Uint8, Float64, Float32, Time, IntervalYM, IntervalDS, String, Bool, Bytes and Bfile. For example, you may insert nullable strings and select nullable strings:

// insert String slice
// given: CREATE TABLE T1 (C1 VARCHAR2(48 CHAR))
a := make([]ora.String, 5)
a[0] = ora.String{Value: "Go is expressive, concise, clean, and efficient."}
a[1] = ora.String{Value: "Its concurrency mechanisms make it easy to"}
a[2] = ora.String{IsNull: true}
a[3] = ora.String{Value: "It's a fast, statically typed, compiled"}
a[4] = ora.String{Value: "One of Go's key design goals is code"}
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Exe(a)

// Specify OraS to Prep method to return ora.String values
// fetch records
stmt, err = ses.Prep("SELECT C1 FROM T1", OraS)
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Row[0])
}

The Stmt.Prep method is variadic accepting zero or more GoColumnType which define a Go return type for a select-list column. For example, a Prep call can be configured to return an int64 and a nullable Int64 from the same column:

// given: create table t1 (c1 number)
stmt, err = ses.Prep("SELECT C1, C1 FROM T1", ora.I64, ora.OraI64)
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Row[0], rst.Row[1])
}

Go numerics of various sizes are supported in DML operations. The ora package supports int64, int32, int16, int8, uint64, uint32, uint16, uint8, float64 and float32. For example, you may insert a uint16 and select numerics of various sizes:

// insert uint16
// given: create table t1 (c1 number)
value := uint16(9)
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Exe(value)

// select numerics of various sizes from the same column
stmt, err = ses.Prep(
	"SELECT C1, C1, C1, C1, C1, C1, C1, C1, C1, C1, FROM T1",
	ora.I64, ora.I32, ora.I16, ora.I8, ora.U64, ora.U32, ora.U16, ora.U8,
	ora.F64, ora.F32)
rst, err := stmt.Qry()
row := rst.NextRow()

If a non-nullable type is defined for a nullable column returning null, the Go type's zero value is returned.

GoColumnTypes defined by the ora package are:

Go type		GoColumnType

int64		I64

int32		I32

int16		I16

int8		I8

uint64		U64

uint32		U32

uint16		U16

uint8		U8

float64		F64

Int64		OraI64

Int32		OraI32

Int16		OraI16

Int8		OraI8

Uint64		OraU64

Uint32		OraU32

Uint16		OraU16

Uint8		OraU8

Float64		OraF64

Float32		OraF32

time.Time	T

Time		OraT

string		S

String		OraS

bool		B

Bool		OraB

[]byte		Bin

Raw			Bin

Lob°		Bin or S

default¹	D

° Lob will return binary data if the Oracle column is a BLOB; otherwise, Lob
  will return a string if the Oracle column is a CLOB.

¹ D represents a default mapping between a select-list column and a Go type.
The default mapping is defined in RsetCfg.

When Stmt.Prep doesn't receive a GoColumnType, or receives an incorrect GoColumnType, the default value defined in RsetCfg is used.

EnvCfg, SrvCfg, SesCfg, StmtCfg and RsetCfg are the main configuration structs. EnvCfg configures aspects of an Env. SrvCfg configures aspects of a Srv. SesCfg configures aspects of a Ses. StmtCfg configures aspects of a Stmt. RsetCfg configures aspects of Rset. StmtCfg and RsetCfg have the most options to configure. RsetCfg defines the default mapping between an Oracle select-list column and a Go type. StmtCfg may be set in an EnvCfg, SrvCfg, SesCfg and Stmt. RsetCfg may be set in a StmtCfg.

EnvCfg.StmtCfg, SrvCfg.StmtCfg, SesCfg.StmtCfg may optionally be specified to configure a statement. If StmtCfg isn't specified default values are applied. EnvCfg.StmtCfg, SrvCfg.StmtCfg, SesCfg.StmtCfg cascade to new descendent structs. When ora.OpenEnv() is called a specified EnvCfg is used or a default EnvCfg is created. Creating a Srv with env.OpenSrv() will use SrvCfg.StmtCfg if it is specified; otherwise, EnvCfg.StmtCfg is copied by value to SrvCfg.StmtCfg. Creating a Ses with srv.OpenSes() will use SesCfg.StmtCfg if it is specified; otherwise, SrvCfg.StmtCfg is copied by value to SesCfg.StmtCfg. Creating a Stmt with ses.Prep() will use SesCfg.StmtCfg if it is specified; otherwise, a new StmtCfg with default values is set on the Stmt. Call Stmt.Cfg() to change a Stmt's configuration.

An Env may contain multiple Srv. A Srv may contain multiple Ses. A Ses may contain multiple Stmt. A Stmt may contain multiple Rset.

// StmtCfg cascades to descendent structs
// EnvCfg -> SrvCfg -> SesCfg -> StmtCfg -> RsetCfg

Setting a RsetCfg on a StmtCfg does not cascade through descendent structs. Configuration of Stmt.Cfg takes effect prior to calls to Stmt.Exe and Stmt.Qry; consequently, any updates to Stmt.Cfg after a call to Stmt.Exe or Stmt.Qry are not observed.

One configuration scenario may be to set a server's select statements to return nullable Go types by default:

sc := ora.NewSrvCfg()
sc.Dblink = "orcl"
sc.StmtCfg.Rset.SetNumberInt(ora.OraI64)
sc.StmtCfg.Rset.SetNumberFloat(ora.OraF64)
sc.StmtCfg.Rset.SetBinaryDouble(ora.OraF64)
sc.StmtCfg.Rset.SetBinaryFloat(ora.OraF64)
sc.StmtCfg.Rset.SetFloat(ora.OraF64)
sc.StmtCfg.Rset.SetDate(ora.OraT)
sc.StmtCfg.Rset.SetTimestamp(ora.OraT)
sc.StmtCfg.Rset.SetTimestampTz(ora.OraT)
sc.StmtCfg.Rset.SetTimestampLtz(ora.OraT)
sc.StmtCfg.Rset.SetChar1(ora.OraB)
sc.StmtCfg.Rset.SetVarchar(ora.OraS)
sc.StmtCfg.Rset.SetLong(ora.OraS)
sc.StmtCfg.Rset.SetClob(ora.OraS)
sc.StmtCfg.Rset.SetBlob(ora.OraBin)
sc.StmtCfg.Rset.SetRaw(ora.OraBin)
sc.StmtCfg.Rset.SetLongRaw(ora.OraBin)
srv, err := env.OpenSrv(sc)
// any new SesCfg.StmtCfg, StmtCfg.Cfg will receive this StmtCfg
// any new Rset will receive the StmtCfg.Rset configuration

Another scenario may be to configure the runes mapped to bool values:

// update StmtCfg to change the FalseRune and TrueRune inserted into the database
// given: CREATE TABLE T1 (C1 CHAR(1 BYTE))

// insert 'false' record
var falseValue bool = false
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Cfg().FalseRune = 'N'
stmt.Exe(falseValue)

// insert 'true' record
var trueValue bool = true
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Cfg().TrueRune = 'Y'
stmt.Exe(trueValue)

// update RsetCfg to change the TrueRune
// used to translate an Oracle char to a Go bool
// fetch inserted records
stmt, err = ses.Prep("SELECT C1 FROM T1")
stmt.Cfg().Rset.TrueRune = 'Y'
rset, err := stmt.Qry()
for rset.Next() {
	fmt.Println(rset.Row[0])
}

Oracle-specific types offered by the ora package are ora.Rset, ora.IntervalYM, ora.IntervalDS, ora.Raw, ora.Lob and ora.Bfile. ora.Rset represents an Oracle SYS_REFCURSOR. IntervalYM represents an Oracle INTERVAL YEAR TO MONTH. ora.IntervalDS represents an Oracle INTERVAL DAY TO SECOND. ora.Raw represents an Oracle RAW or LONG RAW. ora.Lob may represent an Oracle BLOB or Oracle CLOB. And ora.Bfile represents an Oracle BFILE. ROWID columns are returned as strings and don't have a unique Go type.

Rset is used to obtain Go values from a SQL select statement. Methods Rset.Next, Rset.NextRow, and Rset.Len are available. Fields Rset.Row, Rset.Err, Rset.Index, and Rset.ColumnNames are also available. The Next method attempts to load data from an Oracle buffer into Row, returning true when successful. When no data is available, or if an error occurs, Next returns false setting Row to nil. Any error in Next is assigned to Err. Calling Next increments Index and method Len returns the total number of rows processed. The NextRow method is convenient for returning a single row. NextRow calls Next and returns Row. ColumnNames returns the names of columns defined by the SQL select statement.

Rset has two usages. Rset may be returned from Stmt.Qry when prepared with a SQL select statement:

// given: CREATE TABLE T1 (C1 NUMBER, C2, CHAR(1 BYTE), C3 VARCHAR2(48 CHAR))
stmt, err = ses.Prep("SELECT C1, C2, C3 FROM T1")
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Index, rst.Row[0], rst.Row[1], rst.Row[2])
}

Or, *Rset may be passed to Stmt.Exe when prepared with a stored procedure accepting an OUT SYS_REFCURSOR parameter:

// given:
// CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(48 CHAR))
// CREATE OR REPLACE PROCEDURE PROC1(P1 OUT SYS_REFCURSOR) AS
// BEGIN OPEN P1 FOR SELECT C1, C2 FROM T1 ORDER BY C1; END PROC1;
stmt, err = ses.Prep("CALL PROC1(:1)")
rst := &ora.Rset{}
stmt.Exe(rst)
if rst.IsOpen() {
	for rst.Next() {
		fmt.Println(rst.Row[0], rst.Row[1])
	}
}

Stored procedures with multiple OUT SYS_REFCURSOR parameters enable a single Exe call to obtain multiple Rsets:

// given:
// CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(48 CHAR))
// CREATE OR REPLACE PROCEDURE PROC1(P1 OUT SYS_REFCURSOR, P2 OUT SYS_REFCURSOR) AS BEGIN
// OPEN P1 FOR SELECT C1 FROM T1 ORDER BY C1; OPEN P2 FOR SELECT C2 FROM T1 ORDER BY C2;
// END PROC1;
stmt, err = ses.Prep("CALL PROC1(:1, :2)")
rst1 := &ora.Rset{}
rst2 := &ora.Rset{}
stmt.Exe(rst1, rst2)
// read from first cursor
if rst1.IsOpen() {
	for rst1.Next() {
		fmt.Println(rst1.Row[0])
	}
}
// read from second cursor
if rst2.IsOpen() {
	for rst2.Next() {
		fmt.Println(rst2.Row[0])
	}
}

The types of values assigned to Row may be configured in StmtCfg.Rset. For configuration to take effect, assign StmtCfg.Rset prior to calling Stmt.Qry or Stmt.Exe.

Rset prefetching may be controlled by StmtCfg.PrefetchRowCount and StmtCfg.PrefetchMemorySize. PrefetchRowCount works in coordination with PrefetchMemorySize. When PrefetchRowCount is set to zero only PrefetchMemorySize is used; otherwise, the minimum of PrefetchRowCount and PrefetchMemorySize is used. The default uses a PrefetchMemorySize of 134MB.

Opening and closing Rsets is managed internally. Rset does not have an Open method or Close method.

IntervalYM may be be inserted and selected:

// insert IntervalYM slice
// given: create table t1 (c1 interval year to month)
a := make([]ora.IntervalYM, 5)
a[0] = ora.IntervalYM{Year: 1, Month: 1}
a[1] = ora.IntervalYM{Year: 99, Month: 9}
a[2] = ora.IntervalYM{IsNull: true}
a[3] = ora.IntervalYM{Year: -1, Month: -1}
a[4] = ora.IntervalYM{Year: -99, Month: -9}
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Exe(a)

// query IntervalYM
stmt, err = ses.Prep("SELECT C1 FROM T1")
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Row[0])
}

IntervalDS may be be inserted and selected:

// insert IntervalDS slice
// given: CREATE TABLE T1 (C1 INTERVAL DAY TO SECOND)
a := make([]ora.IntervalDS, 5)
a[0] = ora.IntervalDS{Day: 1, Hour: 1, Minute: 1, Second: 1, Nanosecond: 123456789}
a[1] = ora.IntervalDS{Day: 59, Hour: 59, Minute: 59, Second: 59, Nanosecond: 123456789}
a[2] = ora.IntervalDS{IsNull: true}
a[3] = ora.IntervalDS{Day: -1, Hour: -1, Minute: -1, Second: -1, Nanosecond: -123456789}
a[4] = ora.IntervalDS{Day: -59, Hour: -59, Minute: -59, Second: -59, Nanosecond: -123456789}
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (:C1)")
stmt.Exe(a)

// query IntervalDS
stmt, err = ses.Prep("SELECT C1 FROM T1")
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Row[0])
}

Transactions on an Oracle server are supported. DML statements auto-commit unless a transaction has started:

// given: create table t1 (c1 number)

// rollback
tx, err := ses.BeginTransaction()
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (3)")
stmt.Exe()
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (5)")
stmt.Exe()
tx.Rollback()

// commit
tx, err = ses.BeginTransaction()
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (7)")
stmt.Exe()
stmt, err = ses.Prep("INSERT INTO T1 (C1) VALUES (9)")
stmt.Exe()
tx.Commit()

// query records
stmt, err = ses.Prep("SELECT C1 FROM T1")
rst, err := stmt.Qry()
for rst.Next() {
	fmt.Println(rst.Row[0])
}

Ses.PrepAndExe, Ses.PrepAndQry, Ses.Ins, Ses.Upd, and Ses.Sel are convenient one-line methods.

Ses.PrepAndExe offers a convenient one-line call to Ses.Prep and Stmt.Exe.

rowsAffected, err := ses.PrepAndExe("CREATE TABLE T1 (C1 NUMBER)")

Ses.PrepAndQry offers a convenient one-line call to Ses.Prep and Stmt.Qry.

rset, err := ses.PrepAndQry("SELECT CURRENT_TIMESTAMP FROM DUAL")

Ses.Ins composes, prepares and executes a sql INSERT statement. Ses.Ins is useful when you have to create and maintain a simple INSERT statement with a long list of columns. As table columns are added and dropped over the lifetime of a table Ses.Ins is easy to read and revise.

err = ses.Ins("T1",
  "C2", e.C2,
  "C3", e.C3,
  "C4", e.C4,
  "C5", e.C5,
  "C6", e.C6,
  "C7", e.C7,
  "C8", e.C8,
  "C9", e.C9,
  "C10", e.C10,
  "C11", e.C11,
  "C12", e.C12,
  "C13", e.C13,
  "C14", e.C14,
  "C15", e.C15,
  "C16", e.C16,
  "C17", e.C17,
  "C18", e.C18,
  "C19", e.C19,
  "C20", e.C20,
  "C21", e.C21,
  "C1", &e.C1)

Ses.Upd composes, prepares and executes a sql UPDATE statement. Ses.Upd is useful when you have to create and maintain a simple UPDATE statement with a long list of columns. As table columns are added and dropped over the lifetime of a table Ses.Upd is easy to read and revise.

err = ses.Upd("T1",
  "C2", e.C2*2,
  "C3", e.C3*2,
  "C4", e.C4*2,
  "C5", e.C5*2,
  "C6", e.C6*2,
  "C7", e.C7*2,
  "C8", e.C8*2,
  "C9", e.C9*2,
  "C10", e.C10*2,
  "C11", e.C11*2,
  "C12", e.C12*2,
  "C13", e.C13*2,
  "C14", e.C14*2,
  "C15", e.C15*2,
  "C16", e.C16*2,
  "C17", e.C17*2,
  "C18", e.C18*2,
  "C19", e.C19*2,
  "C20", e.C20*2,
  "C21", e.C21*2,
  "C1", e.C1)

Ses.Sel composes, prepares and queries a sql SELECT statement. Ses.Sel is useful when you have to create and maintain a simple SELECT statement with a long list of columns that have non-default GoColumnTypes. As table columns are added and dropped over the lifetime of a table Ses.Sel is easy to read and revise.

rset, err := ses.Sel("T1",
  "C1", ora.U64,
  "C2", ora.F64,
  "C3", ora.I8,
  "C4", ora.I16,
  "C5", ora.I32,
  "C6", ora.I64,
  "C7", ora.U8,
  "C8", ora.U16,
  "C9", ora.U32,
  "C10", ora.U64,
  "C11", ora.F32,
  "C12", ora.F64,
  "C13", ora.I8,
  "C14", ora.I16,
  "C15", ora.I32,
  "C16", ora.I64,
  "C17", ora.U8,
  "C18", ora.U16,
  "C19", ora.U32,
  "C20", ora.U64,
  "C21", ora.F32)

The Ses.Ping method checks whether the client's connection to an Oracle server is valid. A call to Ping requires an open Ses. Ping will return a nil error when the connection is fine:

// open a session before calling Ping
ses, _ := srv.OpenSes("username", "password")
err := ses.Ping()
if err == nil {
	fmt.Println("Ping successful")
}

The Srv.Version method is available to obtain the Oracle server version. A call to Version requires an open Ses:

// open a session before calling Version
ses, err := srv.OpenSes("username", "password")
version, err := srv.Version()
if version != "" && err == nil {
	fmt.Println("Received version from server")
}

Further code examples are available in the example file, test files and samples folder.

Logging

The ora package provides a simple ora.Logger interface for logging. Logging is disabled by default. Specify one of three optional built-in logging packages to enable logging; or, use your own logging package.

ora.Cfg().Log offers various options to enable or disable logging of specific ora driver methods. For example:

// enable logging of the Rset.Next method
ora.Cfg().Log.Rset.Next = true

To use the standard Go log package:

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

func main() {
  // use the optional log package for ora logging
  ora.Cfg().Log.Logger = lg.Log
}

which produces a sample log of:

ORA I 2015/05/23 16:54:44.615462 drv.go:411: OpenEnv 1
ORA I 2015/05/23 16:54:44.626443 drv.go:411: OpenEnv 2
ORA I 2015/05/23 16:54:44.627465 env.go:115: E2] OpenSrv (dbname orcl)
ORA I 2015/05/23 16:54:44.643449 env.go:150: E2] OpenSrv (srvId 1)
ORA I 2015/05/23 16:54:44.643449 srv.go:113: E2S1] OpenSes (username test)
ORA I 2015/05/23 16:54:44.665451 ses.go:163: E2S1S1] Prep: SELECT CURRENT_TIMESTAMP FROM DUAL
ORA I 2015/05/23 16:54:44.666451 rset.go:205: E2S1S1S1R0] open
ORA I 2015/05/23 16:54:44.666451 ses.go:74: E2S1S1] Close
ORA I 2015/05/23 16:54:44.666451 stmt.go:78: E2S1S1S1] Close
ORA I 2015/05/23 16:54:44.666451 rset.go:57: E2S1S1S1R0] close
ORA I 2015/05/23 16:54:44.666451 srv.go:63: E2S1] Close
ORA I 2015/05/23 16:54:44.667451 env.go:68: E2] Close

Messages are prefixed with 'ORA I' for information or 'ORA E' for an error. The log package is configured to write to os.Stderr by default. Use the ora/lg.Std type to configure an alternative io.Writer.

To use the glog package:

import (
	"flag"
	"gopkg.in/rana/ora.v3"
	"gopkg.in/rana/ora.v3/glg"
)

func main() {

	// parse flags for glog (required)
	// consider specifying cmd line arg -alsologtostderr=true
	flag.Parse()

	// use the glog package for ora logging
	ora.Cfg().Log.Logger = glg.Log
}

which produces a sample log of:

I0523 17:31:41.702365   97708 drv.go:411] OpenEnv 1
I0523 17:31:41.728377   97708 drv.go:411] OpenEnv 2
I0523 17:31:41.728377   97708 env.go:115] E2] OpenSrv (dbname orcl)
I0523 17:31:41.741390   97708 env.go:150] E2] OpenSrv (srvId 1)
I0523 17:31:41.741390   97708 srv.go:113] E2S1] OpenSes (username test)
I0523 17:31:41.762366   97708 ses.go:163] E2S1S1] Prep: SELECT CURRENT_TIMESTAMP FROM DUAL
I0523 17:31:41.762366   97708 rset.go:205] E2S1S1S1R0] open
I0523 17:31:41.762366   97708 ses.go:74] E2S1S1] Close
I0523 17:31:41.762366   97708 stmt.go:78] E2S1S1S1] Close
I0523 17:31:41.762366   97708 rset.go:57] E2S1S1S1R0] close
I0523 17:31:41.763365   97708 srv.go:63] E2S1] Close
I0523 17:31:41.763365   97708 env.go:68] E2] Close

To use the log15 package:

import (
	"gopkg.in/rana/ora.v3"
	"gopkg.in/rana/ora.v3/lg15"
)
func main() {
	// use the optional log15 package for ora logging
	ora.Cfg().Log.Logger = lg15.Log
}

which produces a sample log of:

t=2015-05-23T17:08:32-0700 lvl=info msg="OpenEnv 1" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="OpenEnv 2" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2] OpenSrv (dbname orcl)" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2] OpenSrv (srvId 1)" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1] OpenSes (username test)" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1S1] Prep: SELECT CURRENT_TIMESTAMP FROM DUAL" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1S1S1R0] open" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1S1] Close" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1S1S1] Close" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1S1S1R0] close" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2S1] Close" lib=ora
t=2015-05-23T17:08:32-0700 lvl=info msg="E2] Close" lib=ora

Test Database Setup

Tests are available and require some setup. Setup varies depending on whether the Oracle server is configured as a container database or non-container database. It's simpler to setup a non-container database. An example for each setup is explained.

Non-container test database setup steps:

-- 1. login to an Oracle server with SqlPlus as sysdba:
SQLPLUS / AS SYSDBA

-- 2. create a file for the test database use
CREATE TABLESPACE test_ts NOLOGGING DATAFILE 'test.dat' SIZE 100M AUTOEXTEND ON;

-- 3. create a test database
CREATE USER test IDENTIFIED BY test DEFAULT TABLESPACE test_ts;

-- 4. grant permissions to the database
GRANT
	CREATE SESSION,
	CREATE TABLE, CREATE VIEW, CREATE SEQUENCE,
	CREATE PROCEDURE, UNLIMITED TABLESPACE,
	SELECT ANY DICTIONARY
TO test;

-- 5. increase the number allowable open cursors
ALTER SYSTEM SET OPEN_CURSORS = 400 SCOPE=BOTH;

-- 6. create OS environment variables
-- specify your_database_name; varies based on installation; may be 'orcl'
GO_ORA_DRV_TEST_DB = your_database_name
GO_ORA_DRV_TEST_USERNAME = test
GO_ORA_DRV_TEST_PASSWORD = test

Container test database setup steps:

-- 1. login to an Oracle server with SqlPlus as sysdba:
SQLPLUS / AS SYSDBA

-- 2. create a test pluggable database and permissions
-- you will need to change the FILE_NAME_CONVERT file paths for your database installation
CREATE PLUGGABLE DATABASE go_driver_test
ADMIN USER test IDENTIFIED BY test
ROLES = (DBA)
FILE_NAME_CONVERT = ('d:\oracle\data\orcl\pdbseed\', 'd:\oracle\data\go_driver_test\');

-- 3. modify the pluggable database settings
ALTER PLUGGABLE DATABASE go_driver_test OPEN;
ALTER SESSION SET CONTAINER = go_driver_test;
GRANT DBA TO test;

-- 4. add new database service to the tnsnames.ora file:
-- located on your client machine in $ORACLE_HOME\network\admin\tnsnames.ora
GO_DRIVER_TEST =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = go_driver_test)
	)
  )

-- 5. create OS environment variables
GO_ORA_DRIVER_TEST_DB = go_driver_test
GO_ORA_DRIVER_TEST_USERNAME = test
GO_ORA_DRIVER_TEST_PASSWORD = test

Some helpful SQL maintenance statements:

-- delete all tables in a non-container database
BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;
END;

-- delete the non-container test database; use SqlPlus as sysdba
DROP USER test CASCADE;

Run the tests.

Limitations

database/sql method Stmt.QueryRow is not supported.

License

Copyright 2014 Rana Ian. All rights reserved. Use of this source code is governed by The MIT License found in the accompanying LICENSE file.