oci8

README

go-oci8

Description

Golang Oracle database driver conforming to the Go database/sql interface

Installation

Install Oracle full client or Instant Client:

https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

Install a C/C++ compiler

Install pkg-config, edit your package config file oci8.pc (examples below), then set environment variable PKG_CONFIG_PATH to oci8.pc file location (Or can use Go tag noPkgConfig then setup environment variables CGO_CFLAGS and CGO_LDFLAGS)

Go get with Go version 1.9 or higher

go get github.com/mattn/go-oci8

Try the simple select example:

https://godoc.org/github.com/mattn/go-oci8#example-package--SqlSelect

If you have a build error it is normaly because of a misconfiguration, make sure to search close issues for help

oci8.pc Examples

Windows

prefix=/devel/target/XXXXXXXXXXXXXXXXXXXXXXXXXX
exec_prefix=${prefix}
libdir=C:/app/instantclient_12_2/sdk/oci/lib/msvc
includedir=C:/app/instantclient_12_2/sdk/include

glib_genmarshal=glib-genmarshal
gobject_query=gobject-query
glib_mkenums=glib-mkenums

Name: oci8
Description: oci8 library
Libs: -L${libdir} -loci
Cflags: -I${includedir}
Version: 12.2

Linux

prefix=/devel/target/XXXXXXXXXXXXXXXXXXXXXXXXXX
exec_prefix=${prefix}
libdir=/usr/lib/oracle/12.2/client64/lib
includedir=/usr/include/oracle/12.2/client64

glib_genmarshal=glib-genmarshal
gobject_query=gobject-query
glib_mkenums=glib-mkenums

Name: oci8
Description: oci8 library
Libs: -L${libdir} -lclntsh
Cflags: -I${includedir}
Version: 12.2

MacOs

Please install pkg-config with brew if not already present. Download the instant client and the sdk and unpack it e.g. in your Downloads folder and create therein a file names oci8.pc. Please replace <username> with your actual username.

prefixdir=/Users/<username>/Downloads/instantclient_12_2/
libdir=${prefixdir}
includedir=${prefixdir}/sdk/include

Name: OCI
Description: Oracle database driver
Version: 12.2
Libs: -L${libdir} -lclntsh
Cflags: -I${includedir}

You also have to set these environment variables (e.g. permanently by adding them to your .bashrc)

export LD_LIBRARY_PATH=/Users/<username>/Downloads/instantclient_12_2
export PKG_CONFIG_PATH=/Users/<username>/Downloads/instantclient_12_2

SQL Examples

SQL examples can be found in the GoDoc reference:

https://godoc.org/github.com/mattn/go-oci8

And in _example:

https://github.com/mattn/go-oci8/tree/master/_example

Author

Yasuhiro Matsumoto (a.k.a mattn)

Special Thanks

Jamil Djadala

Documentation

Overview

Example (SqlBlob)

// Example shows how write and read a blob

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println("done")
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

// create table
tableName := "E_BLOB_" + oci8.TestTimeString
query := "create table " + tableName + " ( A BLOB )"
ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// insert row
query = "insert into " + tableName + " ( A ) values (:1)"
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query, bytes.Repeat([]byte("abcdefghijklmnopqrstuvwxyz"), 200))
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, "select A from "+tableName)
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

var theBytes []byte
err = rows.Scan(&theBytes)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if len(theBytes) != 5200 {
	fmt.Println("len theBytes != 5200")
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

// drop table
query = "drop table " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

fmt.Println("done")

Output:

done

Example (SqlClob)

// Example shows how write and read a clob

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println("done")
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

// create table
tableName := "E_CLOB_" + oci8.TestTimeString
query := "create table " + tableName + " ( A CLOB )"
ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// insert row
query = "insert into " + tableName + " ( A ) values (:1)"
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query, strings.Repeat("abcdefghijklmnopqrstuvwxyz", 200))
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, "select A from "+tableName)
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

var aString string
err = rows.Scan(&aString)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if len(aString) != 5200 {
	fmt.Println("len aString != 5200")
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

// drop table
query = "drop table " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

fmt.Println("done")

Output:

done

Example (SqlCursor)

// Example shows how to do a cursor select

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase {
	fmt.Println(3)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
err = db.PingContext(ctx)
cancel()
if err != nil {
	fmt.Println("PingContext error is not nil:", err)
	return
}

var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, "select 1, cursor(select 2 from dual union select 3 from dual) from dual")
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

var aInt int64
var subRows *sql.Rows
err = rows.Scan(&aInt, &subRows)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 1 {
	fmt.Println("aInt != 1")
	return
}
if subRows == nil {
	fmt.Println("subRows is nil")
	return
}

if !subRows.Next() {
	fmt.Println("no Next subRows")
	return
}

err = subRows.Scan(&aInt)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 2 {
	fmt.Println("aInt != 2")
	return
}

if !subRows.Next() {
	fmt.Println("no Next subRows")
	return
}

err = subRows.Scan(&aInt)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 3 {
	fmt.Println("aInt != 3")
	return
}

if subRows.Next() {
	fmt.Println("has Next rows")
	return
}

err = subRows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

fmt.Println(aInt)

Output:

3

Example (SqlCursorFunction)

// Example shows how to do a cursor select from function

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println(3)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
err = db.PingContext(ctx)
cancel()
if err != nil {
	fmt.Println("PingContext error is not nil:", err)
	return
}

// create function
functionName := "E_F_CURSOR_" + oci8.TestTimeString
query := `create or replace function ` + functionName + ` return SYS_REFCURSOR
	is
		l_cursor SYS_REFCURSOR;
	begin
		open l_cursor for select 2 from dual union select 3 from dual;
		return l_cursor;
	end ` + functionName + `;`
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, "select 1, "+functionName+"() from dual")
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

var aInt int64
var subRows *sql.Rows
err = rows.Scan(&aInt, &subRows)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 1 {
	fmt.Println("aInt != 1")
	return
}
if subRows == nil {
	fmt.Println("subRows is nil")
	return
}

if !subRows.Next() {
	fmt.Println("no Next subRows")
	return
}

err = subRows.Scan(&aInt)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 2 {
	fmt.Println("aInt != 2")
	return
}

if !subRows.Next() {
	fmt.Println("no Next subRows")
	return
}

err = subRows.Scan(&aInt)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if aInt != 3 {
	fmt.Println("aInt != 3")
	return
}

if subRows.Next() {
	fmt.Println("has Next rows")
	return
}

err = subRows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

// drop function
query = "drop function " + functionName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

fmt.Println(aInt)

Output:

3

Example (SqlFunction)

// Example shows how to do a function call with binds

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase {
	fmt.Println(3)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

number := int64(2)
query := `
declare
	function ADD_ONE(p_number INTEGER) return INTEGER as
	begin
		return p_number + 1;
	end ADD_ONE;
begin
	:num1 := ADD_ONE(:num1);
end;`

ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query, sql.Out{Dest: &number, In: true})
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

if number != 3 {
	fmt.Println("number != 3")
	return
}

fmt.Println(number)

Output:

3

Example (SqlInsert)

// Example shows how to do a single insert

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println(1)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

// create table
tableName := "E_INSERT_" + oci8.TestTimeString
query := "create table " + tableName + " ( A INTEGER )"
ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// insert row
var result sql.Result
query = "insert into " + tableName + " ( A ) values (:1)"
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
result, err = db.ExecContext(ctx, query, 1)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// can see number of RowsAffected if wanted
var rowsAffected int64
rowsAffected, err = result.RowsAffected()
if err != nil {
	fmt.Println("RowsAffected error is not nil:", err)
	return
}

// drop table
query = "drop table " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

fmt.Println(rowsAffected)

Output:

1

Example (SqlManyInserts)

// Example shows how to do a many inserts

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println(3)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

// create table
tableName := "E_MANY_INSERT_" + oci8.TestTimeString
query := "create table " + tableName + " ( A INTEGER )"
ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// prepare insert query statement
var stmt *sql.Stmt
query = "insert into " + tableName + " ( A ) values (:1)"
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
stmt, err = db.PrepareContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("PrepareContext error is not nil:", err)
	return
}

// insert 3 rows
for i := 0; i < 3; i++ {
	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = stmt.ExecContext(ctx, i)
	cancel()
	if err != nil {
		stmt.Close()
		fmt.Println("ExecContext error is not nil:", err)
		return
	}
}

// close insert query statement
err = stmt.Close()
if err != nil {
	fmt.Println("Close error is not nil:", err)
	return
}

// select count/number of rows
var rows *sql.Rows
query = "select count(1) from " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, query)
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

var count int64
err = rows.Scan(&count)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if count != 3 {
	fmt.Println("count not equal to 3")
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

// drop table
query = "drop table " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

fmt.Println(count)

Output:

3

Example (SqlRowid)

// Example shows a few ways to get rowid

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase || oci8.TestDisableDestructive {
	fmt.Println("done")
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

// create table
tableName := "E_ROWID_" + oci8.TestTimeString
query := "create table " + tableName + " ( A INTEGER )"
ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// insert row and get rowid from returning
var rowid1 string // rowid will be put into here
var result sql.Result
query = "insert into " + tableName + " ( A ) values (:1) returning rowid into :rowid1"
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
result, err = db.ExecContext(ctx, query, 1, sql.Named("rowid1", sql.Out{Dest: &rowid1}))
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

// get rowid from LastInsertId
var rowid2 string // rowid will be put into here
var id int64
id, err = result.LastInsertId()
if err != nil {
	fmt.Println("LastInsertId error is not nil:", err)
	return
}
rowid2 = oci8.GetLastInsertId(id)

// select rowid
var rowid3 string // rowid will be put into here
var rows *sql.Rows
query = "select rowid from " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, query)
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

err = rows.Scan(&rowid3)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

// drop table
query = "drop table " + tableName
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
_, err = db.ExecContext(ctx, query)
cancel()
if err != nil {
	fmt.Println("ExecContext error is not nil:", err)
	return
}

if len(rowid1) != len(rowid2) || len(rowid2) != len(rowid3) {
	fmt.Println("rowid len is not equal", rowid1, rowid2, rowid3)
	return
}

fmt.Println("done")

Output:

done

Example (SqlSelect)

// Example shows how to do a basic select

// For testing, check if database tests are disabled
if oci8.TestDisableDatabase {
	fmt.Println(1)
	return
}

oci8.Driver.Logger = log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)

var openString string
// [username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]
if len(oci8.TestUsername) > 0 {
	if len(oci8.TestPassword) > 0 {
		openString = oci8.TestUsername + "/" + oci8.TestPassword + "@"
	} else {
		openString = oci8.TestUsername + "@"
	}
}
openString += oci8.TestHostValid

// A normal simple Open to localhost would look like:
// db, err := sql.Open("oci8", "127.0.0.1")
// For testing, need to use additional variables
db, err := sql.Open("oci8", openString)
if err != nil {
	fmt.Printf("Open error is not nil: %v", err)
	return
}
if db == nil {
	fmt.Println("db is nil")
	return
}

// defer close database
defer func() {
	err = db.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
err = db.PingContext(ctx)
cancel()
if err != nil {
	fmt.Println("PingContext error is not nil:", err)
	return
}

var rows *sql.Rows
ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
defer cancel()
rows, err = db.QueryContext(ctx, "select 1 from dual")
if err != nil {
	fmt.Println("QueryContext error is not nil:", err)
	return
}

// defer close rows
defer func() {
	err = rows.Close()
	if err != nil {
		fmt.Println("Close error is not nil:", err)
	}
}()

if !rows.Next() {
	fmt.Println("no Next rows")
	return
}

dest := make([]interface{}, 1)
destPointer := make([]interface{}, 1)
destPointer[0] = &dest[0]
err = rows.Scan(destPointer...)
if err != nil {
	fmt.Println("Scan error is not nil:", err)
	return
}

if len(dest) != 1 {
	fmt.Println("len dest != 1")
	return
}
data, ok := dest[0].(float64)
if !ok {
	fmt.Println("dest type not float64")
	return
}
if data != 1 {
	fmt.Println("data not equal to 1")
	return
}

if rows.Next() {
	fmt.Println("has Next rows")
	return
}

err = rows.Err()
if err != nil {
	fmt.Println("Err error is not nil:", err)
	return
}

fmt.Println(data)

Output:

1

Index

• Variables
• func GetLastInsertId(id int64) string
• func NewConnector(hosts ...string) driver.Connector
• func QueryEscape(s string) string
• func QueryUnescape(s string) (string, error)
• type Conn
  • func (conn *Conn) Begin() (driver.Tx, error)
  • func (conn *Conn) BeginTx(ctx context.Context, txOptions driver.TxOptions) (driver.Tx, error)
  • func (conn *Conn) Close() error
  • func (conn *Conn) Ping(ctx context.Context) error
  • func (conn *Conn) Prepare(query string) (driver.Stmt, error)
  • func (conn *Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)
• type Connector
  • func (connector *Connector) Connect(ctx context.Context) (driver.Conn, error)
  • func (connector *Connector) Driver() driver.Driver
• type DSN
  • func ParseDSN(dsnString string) (dsn *DSN, err error)
• type DriverStruct
  • func (drv *DriverStruct) Open(dsnString string) (driver.Conn, error)
• type EscapeError
  • func (e EscapeError) Error() string
• type Result
  • func (result *Result) LastInsertId() (int64, error)
  • func (result *Result) RowsAffected() (int64, error)
• type Rows
  • func (rows *Rows) Close() error
  • func (rows *Rows) ColumnTypeDatabaseTypeName(i int) string
  • func (rows *Rows) ColumnTypeLength(i int) (int64, bool)
  • func (rows *Rows) ColumnTypeScanType(i int) reflect.Type
  • func (rows *Rows) Columns() []string
  • func (rows *Rows) Next(dest []driver.Value) error
• type Stmt
  • func (stmt *Stmt) CheckNamedValue(namedValue *driver.NamedValue) error
  • func (stmt *Stmt) Close() error
  • func (stmt *Stmt) Exec(values []driver.Value) (driver.Result, error)
  • func (stmt *Stmt) ExecContext(ctx context.Context, namedValues []driver.NamedValue) (driver.Result, error)
  • func (stmt *Stmt) NumInput() int
  • func (stmt *Stmt) Query(values []driver.Value) (driver.Rows, error)
  • func (stmt *Stmt) QueryContext(ctx context.Context, namedValues []driver.NamedValue) (driver.Rows, error)
• type Tx
  • func (tx *Tx) Commit() error
  • func (tx *Tx) Rollback() error
• type Values
  • func ParseQuery(query string) (m Values, err error)
  • func (v Values) Add(key, value string)
  • func (v Values) Del(key string)
  • func (v Values) Encode() string
  • func (v Values) Get(key string) string
  • func (v Values) Set(key, value string)

Examples

• Package (SqlBlob)
• Package (SqlClob)
• Package (SqlCursor)
• Package (SqlCursorFunction)
• Package (SqlFunction)
• Package (SqlInsert)
• Package (SqlManyInserts)
• Package (SqlRowid)
• Package (SqlSelect)

Variables

var (
	// ErrOCIInvalidHandle is OCI_INVALID_HANDLE
	ErrOCIInvalidHandle = errors.New("OCI_INVALID_HANDLE")
	// ErrOCISuccessWithInfo is OCI_SUCCESS_WITH_INFO
	ErrOCISuccessWithInfo = errors.New("OCI_SUCCESS_WITH_INFO")
	// ErrOCIReservedForIntUse is OCI_RESERVED_FOR_INT_USE
	ErrOCIReservedForIntUse = errors.New("OCI_RESERVED_FOR_INT_USE")
	// ErrOCINoData is OCI_NO_DATA
	ErrOCINoData = errors.New("OCI_NO_DATA")
	// ErrOCINeedData is OCI_NEED_DATA
	ErrOCINeedData = errors.New("OCI_NEED_DATA")
	// ErrOCIStillExecuting is OCI_STILL_EXECUTING
	ErrOCIStillExecuting = errors.New("OCI_STILL_EXECUTING")

	// ErrNoRowid is result has no rowid
	ErrNoRowid = errors.New("result has no rowid")

	// Driver is the sql driver
	Driver = &DriverStruct{
		Logger: log.New(ioutil.Discard, "", 0),
	}
)

Functions

func GetLastInsertId

func GetLastInsertId(id int64) string

GetLastInsertId returns rowid from LastInsertId

func NewConnector

func NewConnector(hosts ...string) driver.Connector

NewConnector returns a new database connector

func QueryEscape

func QueryEscape(s string) string

QueryEscape escapes the string so it can be safely placed inside a URL query.

func QueryUnescape

func QueryUnescape(s string) (string, error)

QueryUnescape does the inverse transformation of QueryEscape, converting %AB into the byte 0xAB and '+' into ' ' (space). It returns an error if any % is not followed by two hexadecimal digits.

Types

type Conn

type Conn struct {
	// contains filtered or unexported fields
}

Conn is Oracle connection

func (*Conn) Begin

func (conn *Conn) Begin() (driver.Tx, error)

Begin starts a transaction

func (*Conn) BeginTx

func (conn *Conn) BeginTx(ctx context.Context, txOptions driver.TxOptions) (driver.Tx, error)

BeginTx starts a transaction

func (*Conn) Close

func (conn *Conn) Close() error

Close a connection

func (*Conn) Ping

func (conn *Conn) Ping(ctx context.Context) error

Ping database connection

func (*Conn) Prepare

func (conn *Conn) Prepare(query string) (driver.Stmt, error)

Prepare prepares a query

func (*Conn) PrepareContext

func (conn *Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

PrepareContext prepares a query with context

type Connector

type Connector struct {
	// Logger is used to log connection ping errors
	Logger *log.Logger
}

Connector is the sql driver connector

func (*Connector) Connect

func (connector *Connector) Connect(ctx context.Context) (driver.Conn, error)

Connect returns a new database connection

func (*Connector) Driver

func (connector *Connector) Driver() driver.Driver

Driver returns the OCI8 driver

type DSN

type DSN struct {
	Connect  string
	Username string
	Password string
	// contains filtered or unexported fields
}

DSN is Oracle Data Source Name

func ParseDSN

func ParseDSN(dsnString string) (dsn *DSN, err error)

ParseDSN parses a DSN used to connect to Oracle

It expects to receive a string in the form:

[username/[password]@]host[:port][/service_name][?param1=value1&...&paramN=valueN]

Connection timeout can be set in the Oracle files: sqlnet.ora as SQLNET.OUTBOUND_CONNECT_TIMEOUT or tnsnames.ora as CONNECT_TIMEOUT

Supported parameters are:

loc - the time location for reading timestamp (without time zone). Defaults to UTC Note that writing a timestamp (without time zone) just truncates the time zone.

isolation - the isolation level that can be set to: READONLY, SERIALIZABLE, or DEFAULT

prefetch_rows - the number of top level rows to be prefetched. Defaults to 0. A 0 means unlimited rows.

prefetch_memory - the max memory for top level rows to be prefetched. Defaults to 4096. A 0 means unlimited memory.

questionph - when true, enables question mark placeholders. Defaults to false. (uses strconv.ParseBool to check for true)

type DriverStruct

type DriverStruct struct {
	// Logger is used to log connection ping errors, defaults to discard
	// To log set it to something like: log.New(os.Stderr, "oci8 ", log.Ldate|log.Ltime|log.LUTC|log.Lshortfile)
	Logger *log.Logger
}

DriverStruct is Oracle driver struct

func (*DriverStruct) Open

func (drv *DriverStruct) Open(dsnString string) (driver.Conn, error)

Open opens a new database connection

type EscapeError

type EscapeError string

EscapeError for invalid escape

func (EscapeError) Error

func (e EscapeError) Error() string

Error returns string for invalid URL escape

type Result

type Result struct {
	// contains filtered or unexported fields
}

Result is Oracle result

func (*Result) LastInsertId

func (result *Result) LastInsertId() (int64, error)

LastInsertId returns last inserted ID

func (*Result) RowsAffected

func (result *Result) RowsAffected() (int64, error)

RowsAffected returns rows affected

type Rows

type Rows struct {
	// contains filtered or unexported fields
}

Rows is Oracle rows

func (*Rows) Close

func (rows *Rows) Close() error

Close closes rows

func (*Rows) ColumnTypeDatabaseTypeName

func (rows *Rows) ColumnTypeDatabaseTypeName(i int) string

ColumnTypeDatabaseTypeName implement RowsColumnTypeDatabaseTypeName.

func (*Rows) ColumnTypeLength

func (rows *Rows) ColumnTypeLength(i int) (int64, bool)

ColumnTypeLength is returning OCI_ATTR_DATA_SIZE, which is max data size in bytes. Note this is not returing length of the column type, like the 20 in FLOAT(20), which is what is normally expected. TODO: Should / can it be changed to return length of the column type?

func (*Rows) ColumnTypeScanType

func (rows *Rows) ColumnTypeScanType(i int) reflect.Type

ColumnTypeScanType implement RowsColumnTypeScanType.

func (*Rows) Columns

func (rows *Rows) Columns() []string

Columns returns column names

func (*Rows) Next

func (rows *Rows) Next(dest []driver.Value) error

Next gets next row

type Stmt

type Stmt struct {
	// contains filtered or unexported fields
}

Stmt is Oracle statement

func (*Stmt) CheckNamedValue

func (stmt *Stmt) CheckNamedValue(namedValue *driver.NamedValue) error

CheckNamedValue checks a named value

func (*Stmt) Close

func (stmt *Stmt) Close() error

Close closes the statement

func (*Stmt) Exec

func (stmt *Stmt) Exec(values []driver.Value) (driver.Result, error)

Exec runs an exec query

func (*Stmt) ExecContext

func (stmt *Stmt) ExecContext(ctx context.Context, namedValues []driver.NamedValue) (driver.Result, error)

ExecContext run a exec query with context

func (*Stmt) NumInput

func (stmt *Stmt) NumInput() int

NumInput returns the number of input

func (*Stmt) Query

func (stmt *Stmt) Query(values []driver.Value) (driver.Rows, error)

Query runs a query

func (*Stmt) QueryContext

func (stmt *Stmt) QueryContext(ctx context.Context, namedValues []driver.NamedValue) (driver.Rows, error)

QueryContext runs a query with context

type Tx

type Tx struct {
	// contains filtered or unexported fields
}

Tx is Oracle transaction

func (*Tx) Commit

func (tx *Tx) Commit() error

Commit transaction commit

func (*Tx) Rollback

func (tx *Tx) Rollback() error

Rollback transaction rollback

type Values

type Values map[string][]string

Values maps a string key to a list of values. It is typically used for query parameters and form values. Unlike in the http.Header map, the keys in a Values map are case-sensitive.

func ParseQuery

func ParseQuery(query string) (m Values, err error)

ParseQuery parses the URL-encoded query string and returns a map listing the values specified for each key. ParseQuery always returns a non-nil map containing all the valid query parameters found; err describes the first decoding error encountered, if any.

func (Values) Add

func (v Values) Add(key, value string)

Add adds the value to key. It appends to any existing values associated with key.

func (Values) Del

func (v Values) Del(key string)

Del deletes the values associated with key.

func (Values) Encode

func (v Values) Encode() string

Encode encodes the values into “URL encoded” form ("bar=baz&foo=quux") not sorted by key

func (Values) Get

func (v Values) Get(key string) string

Get gets the first value associated with the given key. If there are no values associated with the key, Get returns the empty string. To access multiple values, use the map directly.

func (Values) Set

func (v Values) Set(key, value string)

Set sets the key to value. It replaces any existing values.