ora

README

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) on Windows 8.1.

---

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

---

Installation

Minimum requirements are Go 1.3 with CGO enabled, a GCC C compiler, and Oracle 12c (12.1.0.1.0) or Oracle Instant Client (12.1.0.1.0).

Get the ora package from GitHub:

go get github.com/ranaian/ora

Install Oracle 12c 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

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.

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 is immutable. 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'.

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 is mutable. 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

float64, float32				NUMBER¹, BINARY_DOUBLE, BINARY_FLOAT, FLOAT
Float64, Float32

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

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

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

[]byte							BLOB, LONG RAW, RAW
Bytes

IntervalYM						INTERVAL MONTH TO YEAR

IntervalDS						INTERVAL DAY TO SECOND

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"
	"github.com/ranaian/ora"
)

func main() {
	// example usage of the oracle package driver
	// connect to a server and open a session
	env := ora.NewEnvironment()
	env.Open()
	defer env.Close()
	srv, err := env.OpenServer("orcl")
	defer srv.Close()
	if err != nil {
		panic(err)
	}
	ses, err := srv.OpenSession("test", "test")
	defer ses.Close()
	if err != nil {
		panic(err)
	}

	// create table
	stmtTbl, err := ses.Prepare("create table t1 " +
		"(c1 number(19,0) generated always as identity (start with 1 increment by 1), " +
		"c2 varchar2(48 char))")
	defer stmtTbl.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err := stmtTbl.Execute()
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

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

	// insert record
	var id uint64
	str := "Go is expressive, concise, clean, and efficient."
	stmtIns, err := ses.Prepare("insert into t1 (c2) values (:c2) returning c1 into :c1")
	defer stmtIns.Close()
	rowsAffected, err = stmtIns.Execute(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.Prepare("insert into t1 (c2) values (:c2)")
	defer stmtSliceIns.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtSliceIns.Execute(a)
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

	// fetch records
	stmtFetch, err := ses.Prepare("select c1, c2 from t1")
	defer stmtFetch.Close()
	if err != nil {
		panic(err)
	}
	resultSet, err := stmtFetch.Fetch()
	if err != nil {
		panic(err)
	}
	for resultSet.Next() {
		fmt.Println(resultSet.Row[0], resultSet.Row[1])
	}
	if resultSet.Err != nil {
		panic(resultSet.Err)
	}

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

	// begin second transaction
	tx2, err := ses.BeginTransaction()
	if err != nil {
		panic(err)
	}
	// insert null String
	nullableStr := ora.String{IsNull: true}
	stmtTrans, err := ses.Prepare("insert into t1 (c2) values (:c2)")
	defer stmtTrans.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtTrans.Execute(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.Prepare("select count(c1) from t1 where c2 is null", ora.U8)
	defer stmtCount.Close()
	if err != nil {
		panic(err)
	}
	resultSet, err = stmtCount.Fetch()
	if err != nil {
		panic(err)
	}
	row := resultSet.NextRow()
	if row != nil {
		fmt.Println(row[0])
	}
	if resultSet.Err != nil {
		panic(resultSet.Err)
	}

	// create stored procedure with sys_refcursor
	stmtProcCreate, err := ses.Prepare(
		"create or replace procedure proc1(p1 out sys_refcursor) as begin " +
		"open p1 for select c1, c2 from t1 where c1 > 2 order by c1; " +
		"end proc1;")
	defer stmtProcCreate.Close()
	rowsAffected, err = stmtProcCreate.Execute()
	if err != nil {
		panic(err)
	}

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

	// Output:
	// 0
	// 1
	// 4
	// 1 Go is expressive, concise, clean, and efficient.
	// 2 Its concurrency mechanisms make it easy to
	// 3 <nil>
	// 4 It's a fast, statically typed, compiled
	// 5 One of Go's key design goals is code
	// 1
	// 1
	// 3 <nil>
	// 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.Prepare("insert into t1 (c2) values ('go') returning c1 into :c1")
stmt.Execute(&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.Prepare("call proc1(:1)")
stmt.Execute(&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)
}
stmt, err = ses.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// Specify OraS to Prepare method to return ora.String values
// fetch records
stmt, err = ses.Prepare("select c1 from t1", OraS)
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

The Statement.Prepare method is variadic accepting zero or more GoColumnType which define a Go return type for a select-list column. For example, a Prepare 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.Prepare("select c1, c1 from t1", ora.I64, ora.OraI64)
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0], resultSet.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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(value)

// select numerics of various sizes from the same column
stmt, err = ses.Prepare(
	"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)
resultSet, err := stmt.Fetch()
row := resultSet.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		Bits

Bytes		OraBits

default°	D

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

When Statement.Prepare doesn't receive a GoColumnType, or receives an incorrect GoColumnType, the default value defined in ResultSetConfig is used.

There are two configuration structs, StatementConfig and ResultSetConfig. StatementConfig configures various aspects of a Statement. ResultSetConfig configures various aspects of a ResultSet, including the default mapping between a select-list column and a Go type. StatementConfig may be set in an Environment, Server, Session and Statement. ResultSetConfig may be set in a ResultSet and StatementConfig.

Setting a StatementConfig on a configurable struct, such as Environment, Server, Session or Statement, cascades the StatementConfig to all current and future descendent structs. An Environment may contain multiple Servers. A Server may contain multiple Sessions. A Session may contain multiple Statements. A Statement may contain multiple ResultSets.

// setting StatementConfig cascades to descendent structs
// Environment -> Server -> Session -> Statement

Setting a ResultSetConfig on a StatementConfig does not cascade through descendent structs.

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

sc := NewStatementConfig()
sc.ResultSet.SetNumberScaless(ora.OraI64)
sc.ResultSet.SetNumberScaled(ora.OraF64)
sc.ResultSet.SetBinaryDouble(ora.OraF64)
sc.ResultSet.SetBinaryFloat(ora.OraF64)
sc.ResultSet.SetFloat(ora.OraF64)
sc.ResultSet.SetDate(ora.OraT)
sc.ResultSet.SetTimestamp(ora.OraT)
sc.ResultSet.SetTimestampTz(ora.OraT)
sc.ResultSet.SetTimestampLtz(ora.OraT)
sc.ResultSet.SetChar1(ora.OraB)
sc.ResultSet.SetVarchar(ora.OraS)
sc.ResultSet.SetLong(ora.OraS)
sc.ResultSet.SetClob(ora.OraS)
sc.ResultSet.SetBlob(ora.OraBits)
sc.ResultSet.SetRaw(ora.OraBits)
sc.ResultSet.SetLongRaw(ora.OraBits)
srv, err := env.OpenServer("orcl")
// setting the server StatementConfig will cascade to any open Sessions, Statements
// any new Session, Statement will receive this StatementConfig
// any new ResultSet will receive the StatementConfig.ResultSet configuration
srv.SetStatementConfig(sc)

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

// update StatementConfig 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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Config.FalseRune = 'N'
stmt.Execute(falseValue)

// insert 'true' record
var trueValue bool = true
stmt, err = ses.Prepare("insert into t1 (c1) values (:c1)")
stmt.Config.TrueRune = 'Y'
stmt.Execute(trueValue)

// update ResultSetConfig to change the TrueRune
// used to translate an Oracle char to a Go bool
// fetch inserted records
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
resultSet.Config.TrueRune = 'Y'
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Oracle-specific types offered by the ora package are ResultSet, IntervalYM, IntervalDS, and Bfile. ResultSet represents an Oracle SYS_REFCURSOR. IntervalYM represents an Oracle INTERVAL YEAR TO MONTH. IntervalDS represents an Oracle INTERVAL DAY TO SECOND. And Bfile represents an Oracle BFILE. ROWID columns are returned as strings and don't have a unique Go type.

ResultSet is used to obtain Go values from a SQL select statement. ResultSet has two usages. Statement.Fetch may be called to obtain a ResultSet when a SQL select statement is provided to Statement.Prepare:

// given: create table t1 (c1 number, c2, char(1 byte), c3 varchar2(48 char))
stmt, err = ses.Prepare("select c1, c2, c3 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0], resultSet.Row[1], resultSet.Row[2])
}

ResultSet may also be used with stored procedure parameters that are defined as OUT SYS_REFCURSOR. For example:

// 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.Prepare("call proc1(:1)")
resultSet := &ora.ResultSet{}
stmt.Execute(resultSet)
if resultSet.IsOpen() {
	for resultSet.Next() {
		fmt.Println(resultSet.Row[0], resultSet.Row[1])
	}
}

Stored procedures with multiple SYS_REFCURSOR parameters enable a single Execute call to obtain multiple ResultSets:

// 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.Prepare("call proc1(:1, :2)")
resultSet1 := &ora.ResultSet{}
resultSet2 := &ora.ResultSet{}
stmt.Execute(resultSet1, resultSet2)
// read from first cursor
if resultSet1.IsOpen() {
	for resultSet1.Next() {
		fmt.Println(resultSet1.Row[0])
	}
}
// read from second cursor
if resultSet2.IsOpen() {
	for resultSet2.Next() {
		fmt.Println(resultSet2.Row[0])
	}
}

Opening and closing a ResultSet is managed internally. ResultSet doesn't have an Open method or Close method.

ResultSet prefetching may be controlled by StatementConfig.PrefetchRowCount and StatementConfig.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.

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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// fetch IntervalYM
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// fetch IntervalDS
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Transactions on an Oracle server are supported:

// given: create table t1 (c1 number)

// rollback
tx, err := ses.BeginTransaction()
stmt, err = ses.Prepare("insert into t1 (c1) values (3)")
stmt.Execute()
stmt, err = ses.Prepare("insert into t1 (c1) values (5)")
stmt.Execute()
tx.Rollback()

// commit
tx, err = ses.BeginTransaction()
stmt, err = ses.Prepare("insert into t1 (c1) values (7)")
stmt.Execute()
stmt, err = ses.Prepare("insert into t1 (c1) values (9)")
stmt.Execute()
tx.Commit()

// fetch records
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

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

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

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

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

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

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 SEQUENCE, 
CREATE PROCEDURE, UNLIMITED TABLESPACE TO test;

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

Documentation

Overview

Package ora implements an Oracle database driver.

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) on Windows 8.1.

Installation

Minimum requirements are Go 1.3 with CGO enabled, a GCC C compiler, and Oracle 12c (12.1.0.1.0) or Oracle Instant Client (12.1.0.1.0).

Get the ora package from GitHub:

go get github.com/ranaian/ora

Install Oracle 12c 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

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.

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 is immutable. 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'.

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 is mutable. 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

float64, float32		NUMBER¹, BINARY_DOUBLE, BINARY_FLOAT, FLOAT
Float64, Float32

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

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

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

[]byte				BLOB, LONG RAW, RAW
Bytes

IntervalYM			INTERVAL MONTH TO YEAR

IntervalDS			INTERVAL DAY TO SECOND

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"
	"github.com/ranaian/ora"
)

func main() {
	// example usage of the oracle package driver
	// connect to a server and open a session
	env := ora.NewEnvironment()
	env.Open()
	defer env.Close()
	srv, err := env.OpenServer("orcl")
	defer srv.Close()
	if err != nil {
		panic(err)
	}
	ses, err := srv.OpenSession("test", "test")
	defer ses.Close()
	if err != nil {
		panic(err)
	}

	// create table
	stmtTbl, err := ses.Prepare("create table t1 " +
		"(c1 number(19,0) generated always as identity (start with 1 increment by 1), " +
		"c2 varchar2(48 char))")
	defer stmtTbl.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err := stmtTbl.Execute()
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

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

	// insert record
	var id uint64
	str := "Go is expressive, concise, clean, and efficient."
	stmtIns, err := ses.Prepare("insert into t1 (c2) values (:c2) returning c1 into :c1")
	defer stmtIns.Close()
	rowsAffected, err = stmtIns.Execute(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.Prepare("insert into t1 (c2) values (:c2)")
	defer stmtSliceIns.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtSliceIns.Execute(a)
	if err != nil {
		panic(err)
	}
	fmt.Println(rowsAffected)

	// fetch records
	stmtFetch, err := ses.Prepare("select c1, c2 from t1")
	defer stmtFetch.Close()
	if err != nil {
		panic(err)
	}
	resultSet, err := stmtFetch.Fetch()
	if err != nil {
		panic(err)
	}
	for resultSet.Next() {
		fmt.Println(resultSet.Row[0], resultSet.Row[1])
	}
	if resultSet.Err != nil {
		panic(resultSet.Err)
	}

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

	// begin second transaction
	tx2, err := ses.BeginTransaction()
	if err != nil {
		panic(err)
	}
	// insert null String
	nullableStr := ora.String{IsNull: true}
	stmtTrans, err := ses.Prepare("insert into t1 (c2) values (:c2)")
	defer stmtTrans.Close()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = stmtTrans.Execute(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.Prepare("select count(c1) from t1 where c2 is null", ora.U8)
	defer stmtCount.Close()
	if err != nil {
		panic(err)
	}
	resultSet, err = stmtCount.Fetch()
	if err != nil {
		panic(err)
	}
	row := resultSet.NextRow()
	if row != nil {
		fmt.Println(row[0])
	}
	if resultSet.Err != nil {
		panic(resultSet.Err)
	}

	// create stored procedure with sys_refcursor
	stmtProcCreate, err := ses.Prepare(
		"create or replace procedure proc1(p1 out sys_refcursor) as begin " +
		"open p1 for select c1, c2 from t1 where c1 > 2 order by c1; " +
		"end proc1;")
	defer stmtProcCreate.Close()
	rowsAffected, err = stmtProcCreate.Execute()
	if err != nil {
		panic(err)
	}

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

	// Output:
	// 0
	// 1
	// 4
	// 1 Go is expressive, concise, clean, and efficient.
	// 2 Its concurrency mechanisms make it easy to
	// 3 <nil>
	// 4 It's a fast, statically typed, compiled
	// 5 One of Go's key design goals is code
	// 1
	// 1
	// 3 <nil>
	// 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.Prepare("insert into t1 (c2) values ('go') returning c1 into :c1")
stmt.Execute(&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.Prepare("call proc1(:1)")
stmt.Execute(&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)
}
stmt, err = ses.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// Specify OraS to Prepare method to return ora.String values
// fetch records
stmt, err = ses.Prepare("select c1 from t1", OraS)
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

The Statement.Prepare method is variadic accepting zero or more GoColumnType which define a Go return type for a select-list column. For example, a Prepare 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.Prepare("select c1, c1 from t1", ora.I64, ora.OraI64)
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0], resultSet.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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(value)

// select numerics of various sizes from the same column
stmt, err = ses.Prepare(
	"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)
resultSet, err := stmt.Fetch()
row := resultSet.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		Bits

Bytes		OraBits

default°	D

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

When Statement.Prepare doesn't receive a GoColumnType, or receives an incorrect GoColumnType, the default value defined in ResultSetConfig is used.

There are two configuration structs, StatementConfig and ResultSetConfig. StatementConfig configures various aspects of a Statement. ResultSetConfig configures various aspects of a ResultSet, including the default mapping between a select-list column and a Go type. StatementConfig may be set in an Environment, Server, Session and Statement. ResultSetConfig may be set in a ResultSet and StatementConfig.

Setting a StatementConfig on a configurable struct, such as Environment, Server, Session or Statement, cascades the StatementConfig to all current and future descendent structs. An Environment may contain multiple Servers. A Server may contain multiple Sessions. A Session may contain multiple Statements. A Statement may contain multiple ResultSets.

// setting StatementConfig cascades to descendent structs
// Environment -> Server -> Session -> Statement

Setting a ResultSetConfig on a StatementConfig does not cascade through descendent structs.

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

sc := NewStatementConfig()
sc.ResultSet.SetNumberScaless(ora.OraI64)
sc.ResultSet.SetNumberScaled(ora.OraF64)
sc.ResultSet.SetBinaryDouble(ora.OraF64)
sc.ResultSet.SetBinaryFloat(ora.OraF64)
sc.ResultSet.SetFloat(ora.OraF64)
sc.ResultSet.SetDate(ora.OraT)
sc.ResultSet.SetTimestamp(ora.OraT)
sc.ResultSet.SetTimestampTz(ora.OraT)
sc.ResultSet.SetTimestampLtz(ora.OraT)
sc.ResultSet.SetChar1(ora.OraB)
sc.ResultSet.SetVarchar(ora.OraS)
sc.ResultSet.SetLong(ora.OraS)
sc.ResultSet.SetClob(ora.OraS)
sc.ResultSet.SetBlob(ora.OraBits)
sc.ResultSet.SetRaw(ora.OraBits)
sc.ResultSet.SetLongRaw(ora.OraBits)
srv, err := env.OpenServer("orcl")
// setting the server StatementConfig will cascade to any open Sessions, Statements
// any new Session, Statement will receive this StatementConfig
// any new ResultSet will receive the StatementConfig.ResultSet configuration
srv.SetStatementConfig(sc)

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

// update StatementConfig 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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Config.FalseRune = 'N'
stmt.Execute(falseValue)

// insert 'true' record
var trueValue bool = true
stmt, err = ses.Prepare("insert into t1 (c1) values (:c1)")
stmt.Config.TrueRune = 'Y'
stmt.Execute(trueValue)

// update ResultSetConfig to change the TrueRune
// used to translate an Oracle char to a Go bool
// fetch inserted records
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
resultSet.Config.TrueRune = 'Y'
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Oracle-specific types offered by the ora package are ResultSet, IntervalYM, IntervalDS, and Bfile. ResultSet represents an Oracle SYS_REFCURSOR. IntervalYM represents an Oracle INTERVAL YEAR TO MONTH. IntervalDS represents an Oracle INTERVAL DAY TO SECOND. And Bfile represents an Oracle BFILE. ROWID columns are returned as strings and don't have a unique Go type.

ResultSet is used to obtain Go values from a SQL select statement. ResultSet has two usages. Statement.Fetch may be called to obtain a ResultSet when a SQL select statement is provided to Statement.Prepare:

// given: create table t1 (c1 number, c2, char(1 byte), c3 varchar2(48 char))
stmt, err = ses.Prepare("select c1, c2, c3 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0], resultSet.Row[1], resultSet.Row[2])
}

ResultSet may also be used with stored procedure parameters that are defined as OUT SYS_REFCURSOR. For example:

// 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.Prepare("call proc1(:1)")
resultSet := &ora.ResultSet{}
stmt.Execute(resultSet)
if resultSet.IsOpen() {
	for resultSet.Next() {
		fmt.Println(resultSet.Row[0], resultSet.Row[1])
	}
}

Stored procedures with multiple SYS_REFCURSOR parameters enable a single Execute call to obtain multiple ResultSets:

// 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.Prepare("call proc1(:1, :2)")
resultSet1 := &ora.ResultSet{}
resultSet2 := &ora.ResultSet{}
stmt.Execute(resultSet1, resultSet2)
// read from first cursor
if resultSet1.IsOpen() {
	for resultSet1.Next() {
		fmt.Println(resultSet1.Row[0])
	}
}
// read from second cursor
if resultSet2.IsOpen() {
	for resultSet2.Next() {
		fmt.Println(resultSet2.Row[0])
	}
}

Opening and closing ResultSets is managed internally. ResultSet doesn't have an Open method or Close method.

ResultSet prefetching may be controlled by StatementConfig.PrefetchRowCount and StatementConfig.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.

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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// fetch IntervalYM
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.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.Prepare("insert into t1 (c1) values (:c1)")
stmt.Execute(a)

// fetch IntervalDS
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Transactions on an Oracle server are supported:

// given: create table t1 (c1 number)

// rollback
tx, err := ses.BeginTransaction()
stmt, err = ses.Prepare("insert into t1 (c1) values (3)")
stmt.Execute()
stmt, err = ses.Prepare("insert into t1 (c1) values (5)")
stmt.Execute()
tx.Rollback()

// commit
tx, err = ses.BeginTransaction()
stmt, err = ses.Prepare("insert into t1 (c1) values (7)")
stmt.Execute()
stmt, err = ses.Prepare("insert into t1 (c1) values (9)")
stmt.Execute()
tx.Commit()

// fetch records
stmt, err = ses.Prepare("select c1 from t1")
resultSet, err := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

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

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

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

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

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

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 SEQUENCE,
CREATE PROCEDURE, UNLIMITED TABLESPACE TO test;

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

Index

• Constants
• type Bfile
  • func (this Bfile) Equals(other Bfile) bool
• type Bool
  • func (this Bool) Equals(other Bool) bool
• type Bytes
  • func (this Bytes) Equals(other Bytes) bool
• type Connection
  • func (connection *Connection) Begin() (driver.Tx, error)
  • func (connection *Connection) Close() (err error)
  • func (connection *Connection) IsOpen() bool
  • func (connection *Connection) Ping() error
  • func (connection *Connection) Prepare(sql string) (driver.Stmt, error)
• type Driver
  • func (driver *Driver) Open(connStr string) (driver.Conn, error)
• type Environment
  • func NewEnvironment() (environment *Environment)
  • func (environment *Environment) Close() error
  • func (environment *Environment) IsOpen() bool
  • func (environment *Environment) Open() error
  • func (environment *Environment) OpenConnection(connStr string) (driver.Conn, error)
  • func (environment *Environment) OpenServer(dblink string) (*Server, error)
  • func (environment *Environment) SetStatementConfig(c StatementConfig)
  • func (environment *Environment) StatementConfig() *StatementConfig
• type ExecResult
  • func (execResult *ExecResult) LastInsertId() (int64, error)
  • func (execResult *ExecResult) RowsAffected() (int64, error)
• type Float32
  • func (this Float32) Equals(other Float32) bool
• type Float64
  • func (this Float64) Equals(other Float64) bool
• type GoColumnType
• type Int16
  • func (this Int16) Equals(other Int16) bool
• type Int32
  • func (this Int32) Equals(other Int32) bool
• type Int64
  • func (this Int64) Equals(other Int64) bool
• type Int8
  • func (this Int8) Equals(other Int8) bool
• type IntervalDS
  • func (this IntervalDS) Equals(other IntervalDS) bool
  • func (this IntervalDS) ShiftTime(t time.Time) time.Time
• type IntervalYM
  • func (this IntervalYM) Equals(other IntervalYM) bool
  • func (this IntervalYM) ShiftTime(t time.Time) time.Time
• type QueryResult
  • func (queryResult *QueryResult) Close() error
  • func (queryResult *QueryResult) Columns() []string
  • func (queryResult *QueryResult) Next(dest []driver.Value) (err error)
• type ResultSet
  • func (resultSet *ResultSet) IsOpen() bool
  • func (resultSet *ResultSet) Len() int
  • func (resultSet *ResultSet) Next() bool
  • func (resultSet *ResultSet) NextRow() []interface{}
• type ResultSetConfig
  • func NewResultSetConfig() ResultSetConfig
  • func (resultSetConfig *ResultSetConfig) BinaryDouble() GoColumnType
  • func (resultSetConfig *ResultSetConfig) BinaryFloat() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Blob() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Char() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Char1() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Clob() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Date() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Float() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Long() GoColumnType
  • func (resultSetConfig *ResultSetConfig) LongRaw() GoColumnType
  • func (resultSetConfig *ResultSetConfig) NumberScaled() GoColumnType
  • func (resultSetConfig *ResultSetConfig) NumberScaless() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Raw() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Reset()
  • func (resultSetConfig *ResultSetConfig) SetBinaryDouble(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetBinaryFloat(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetBlob(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetChar(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetChar1(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetClob(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetDate(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetFloat(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetLong(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetLongRaw(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetNumberScaled(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetNumberScaless(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetRaw(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetTimestamp(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetTimestampLtz(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetTimestampTz(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) SetVarchar(gct GoColumnType) (err error)
  • func (resultSetConfig *ResultSetConfig) Timestamp() GoColumnType
  • func (resultSetConfig *ResultSetConfig) TimestampLtz() GoColumnType
  • func (resultSetConfig *ResultSetConfig) TimestampTz() GoColumnType
  • func (resultSetConfig *ResultSetConfig) Varchar() GoColumnType
• type Server
  • func (server *Server) Close() error
  • func (server *Server) IsOpen() bool
  • func (server *Server) OpenSession(username string, password string) (*Session, error)
  • func (server *Server) Ping() error
  • func (server *Server) SetStatementConfig(c StatementConfig)
  • func (server *Server) StatementConfig() *StatementConfig
  • func (server *Server) Version() (string, error)
• type Session
  • func (session *Session) BeginTransaction() (*Transaction, error)
  • func (session *Session) Close() error
  • func (session *Session) CommitTransaction() error
  • func (session *Session) IsOpen() bool
  • func (session *Session) Prepare(sql string, goColumnTypes ...GoColumnType) (*Statement, error)
  • func (session *Session) RollbackTransaction() error
  • func (session *Session) SetStatementConfig(c StatementConfig)
  • func (session *Session) StatementConfig() *StatementConfig
• type Statement
  • func (statement *Statement) Close() error
  • func (statement *Statement) Exec(values []driver.Value) (result driver.Result, err error)
  • func (statement *Statement) Execute(params ...interface{}) (rowsAffected uint64, err error)
  • func (statement *Statement) Fetch(params ...interface{}) (*ResultSet, error)
  • func (statement *Statement) IsOpen() bool
  • func (statement *Statement) NumInput() int
  • func (statement *Statement) Query(values []driver.Value) (driver.Rows, error)
• type StatementConfig
  • func NewStatementConfig() StatementConfig
  • func (statementConfig *StatementConfig) ByteSlice() GoColumnType
  • func (statementConfig *StatementConfig) LobBufferSize() int
  • func (statementConfig *StatementConfig) LongBufferSize() uint32
  • func (statementConfig *StatementConfig) LongRawBufferSize() uint32
  • func (statementConfig *StatementConfig) PrefetchMemorySize() uint32
  • func (statementConfig *StatementConfig) PrefetchRowCount() uint32
  • func (statementConfig *StatementConfig) Reset()
  • func (statementConfig *StatementConfig) SetByteSlice(gct GoColumnType) (err error)
  • func (statementConfig *StatementConfig) SetLobBufferSize(size int) error
  • func (statementConfig *StatementConfig) SetLongBufferSize(size uint32) error
  • func (statementConfig *StatementConfig) SetLongRawBufferSize(size uint32) error
  • func (statementConfig *StatementConfig) SetPrefetchMemorySize(prefetchMemorySize uint32) error
  • func (statementConfig *StatementConfig) SetPrefetchRowCount(prefetchRowCount uint32) error
  • func (statementConfig *StatementConfig) SetStringPtrBufferSize(size int) error
  • func (statementConfig *StatementConfig) StringPtrBufferSize() int
• type String
  • func (this String) Equals(other String) bool
• type Time
  • func (this Time) Equals(other Time) bool
• type Transaction
  • func (transaction *Transaction) Commit() (err error)
  • func (transaction *Transaction) Rollback() (err error)
• type Uint16
  • func (this Uint16) Equals(other Uint16) bool
• type Uint32
  • func (this Uint32) Equals(other Uint32) bool
• type Uint64
  • func (this Uint64) Equals(other Uint64) bool
• type Uint8
  • func (this Uint8) Equals(other Uint8) bool

Examples

• Bfile
• Bool
• Bytes
• Driver (Usage)
• Float32
• Float64
• Int16
• Int32
• Int64
• Int8
• IntervalDS
• IntervalDS.ShiftTime
• IntervalYM
• IntervalYM.ShiftTime
• ResultSet (Cursor_multiple)
• ResultSet (Cursor_single)
• ResultSet.Next
• ResultSet.NextRow
• Server.Ping
• Server.Version
• Statement.Exec (Delete)
• Statement.Exec (Insert)
• Statement.Exec (Insert_bool)
• Statement.Exec (Insert_return_identity)
• Statement.Exec (Update)
• Statement.Execute (Delete)
• Statement.Execute (Insert)
• Statement.Execute (Insert_fetch_blob)
• Statement.Execute (Insert_fetch_bool)
• Statement.Execute (Insert_fetch_bool_alternate)
• Statement.Execute (Insert_fetch_byteSlice)
• Statement.Execute (Insert_nullable)
• Statement.Execute (Insert_return_identity)
• Statement.Execute (Insert_return_rowid)
• Statement.Execute (Insert_slice)
• Statement.Execute (Update)
• Statement.Fetch
• Statement.Fetch (Nullable)
• Statement.Fetch (Numerics)
• String
• Time
• Transaction
• Uint16
• Uint32
• Uint64
• Uint8

Constants

const (
	// The Oracle driver name registered with the database/sql package.
	DriverName string = "oracle"
)

Types

type Bfile

type Bfile struct {
	IsNull         bool
	DirectoryAlias string
	Filename       string
}

Bfile represents a nullable Oracle BFILE.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 bfile)", tableName))
defer stmt.Close()
stmt.Execute()

// insert Bfile
a := Bfile{IsNull: false, DirectoryAlias: "TEMP_DIR", Filename: "test.txt"}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// fetch Bfile
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v", resultSet.Row[0])
}

Output:

{false TEMP_DIR test.txt}

func (Bfile) Equals

func (this Bfile) Equals(other Bfile) bool

Equals returns true when the receiver and specified Bfile are both null, or when the receiver and specified Bfile are both not null, DirectoryAlias are equal and Filename are equal.

type Bool

type Bool struct {
	IsNull bool
	Value  bool
}

Bool is a nullable bool.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Bool slice
a := make([]Bool, 5)
a[0] = Bool{Value: true}
a[1] = Bool{Value: false}
a[2] = Bool{IsNull: true}
a[3] = Bool{Value: false}
a[4] = Bool{Value: true}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraB to Prepare method to return Bool values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraB)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false true}
{false false}
{true false}
{false false}
{false true}

func (Bool) Equals

func (this Bool) Equals(other Bool) bool

Equals returns true when the receiver and specified Bool are both null, or when the receiver and specified Bool are both not null and Values are equal.

type Bytes

type Bytes struct {
	IsNull bool
	Value  []byte
}

Bytes is a nullable byte slice.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 blob)", tableName))
defer stmt.Close()
stmt.Execute()

// insert Bytes slice
a := make([]Bytes, 5)
b := make([]byte, 10)
for n, _ := range b {
	b[n] = byte(n)
}
a[0] = Bytes{Value: b}
b = make([]byte, 10)
for n, _ := range b {
	b[n] = byte(n * 2)
}
a[1] = Bytes{Value: b}
a[2] = Bytes{IsNull: true}
b = make([]byte, 10)
for n, _ := range b {
	b[n] = byte(n * 3)
}
a[3] = Bytes{Value: b}
b = make([]byte, 10)
for n, _ := range b {
	b[n] = byte(n * 4)
}
a[4] = Bytes{Value: b}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraBits to Prepare method to return Bytes values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraBits)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false [0 1 2 3 4 5 6 7 8 9]}
{false [0 2 4 6 8 10 12 14 16 18]}
{true []}
{false [0 3 6 9 12 15 18 21 24 27]}
{false [0 4 8 12 16 20 24 28 32 36]}

func (Bytes) Equals

func (this Bytes) Equals(other Bytes) bool

Equals returns true when the receiver and specified Bytes are both null, or when the receiver and specified Bytes are both not null and Values are equal.

type Connection

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

An Oracle connection associated with a session and server.

Implements the driver.Conn interface.

func (*Connection) Begin

func (connection *Connection) Begin() (driver.Tx, error)

Begin starts a transaction.

Begin is a member of the driver.Conn interface.

func (*Connection) Close

func (connection *Connection) Close() (err error)

Close ends a session and disconnects from an Oracle server.

Close is a member of the driver.Conn interface.

func (*Connection) IsOpen

func (connection *Connection) IsOpen() bool

IsOpen returns true when the connection to the Oracle server is open; otherwise, false.

Calling Close will cause IsOpen to return false. Once closed, a connection cannot be re-opened. To open a new connection call Open on a driver.

func (*Connection) Ping

func (connection *Connection) Ping() error

Ping makes a round-trip call to an Oracle server to confirm that the connection is active.

func (*Connection) Prepare

func (connection *Connection) Prepare(sql string) (driver.Stmt, error)

Prepare readies a sql string for use.

Prepare is a member of the driver.Conn interface.

type Driver

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

An Oracle database driver.

Implements the driver.Driver interface.

Example (Usage)

// example usage of the oracle package driver
// connect to a server and open a session
env := NewEnvironment()
env.Open()
defer env.Close()
srv, err := env.OpenServer(testServerName)
defer srv.Close()
if err != nil {
	panic(err)
}
ses, err := srv.OpenSession(testUsername, testPassword)
defer ses.Close()
if err != nil {
	panic(err)
}

// create table
tableName := tableName()
stmtTbl, err := ses.Prepare(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.Execute()
if err != nil {
	panic(err)
}
fmt.Println(rowsAffected)

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

// insert record
var id uint64
str := "Go is expressive, concise, clean, and efficient."
stmtIns, err := ses.Prepare(fmt.Sprintf("insert into %v (c2) values (:c2) returning c1 into :c1", tableName))
defer stmtIns.Close()
rowsAffected, err = stmtIns.Execute(str, &id)
if err != nil {
	panic(err)
}
fmt.Println(rowsAffected)

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

// fetch records
stmtFetch, err := ses.Prepare(fmt.Sprintf("select c1, c2 from %v", tableName))
defer stmtFetch.Close()
if err != nil {
	panic(err)
}
resultSet, err := stmtFetch.Fetch()
if err != nil {
	panic(err)
}
for resultSet.Next() {
	fmt.Println(resultSet.Row[0], resultSet.Row[1])
}
if resultSet.Err != nil {
	panic(resultSet.Err)
}

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

// begin second transaction
tx2, err := ses.BeginTransaction()
if err != nil {
	panic(err)
}
// insert null String
nullableStr := String{IsNull: true}
stmtTrans, err := ses.Prepare(fmt.Sprintf("insert into %v (c2) values (:c2)", tableName))
defer stmtTrans.Close()
if err != nil {
	panic(err)
}
rowsAffected, err = stmtTrans.Execute(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.Prepare(fmt.Sprintf("select count(c1) from %v where c2 is null", tableName), U8)
defer stmtCount.Close()
if err != nil {
	panic(err)
}
resultSet, err = stmtCount.Fetch()
if err != nil {
	panic(err)
}
row := resultSet.NextRow()
if row != nil {
	fmt.Println(row[0])
}
if resultSet.Err != nil {
	panic(resultSet.Err)
}

// create stored procedure with sys_refcursor
stmtProcCreate, err := ses.Prepare(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.Execute()
if err != nil {
	panic(err)
}

// call stored procedure
// pass *ResultSet to Execute to receive the results of a sys_refcursor
stmtProcCall, err := ses.Prepare("call proc1(:1)")
defer stmtProcCall.Close()
if err != nil {
	panic(err)
}
procResultSet := &ResultSet{}
rowsAffected, err = stmtProcCall.Execute(procResultSet)
if err != nil {
	panic(err)
}
if procResultSet.IsOpen() {
	for procResultSet.Next() {
		fmt.Println(procResultSet.Row[0], procResultSet.Row[1])
	}
	if procResultSet.Err != nil {
		panic(procResultSet.Err)
	}
	fmt.Println(procResultSet.Len())
}

Output:

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

func (*Driver) Open

func (driver *Driver) Open(connStr string) (driver.Conn, error)

Open starts a connection to an Oracle server.

The connection string has the form username/password@dblink where dblink is defined on your client machine's tnsnames.ora file. For example, rob/pike@orcl.

Open is a member of the driver.Driver interface.

type Environment

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

An Oracle environment.

func NewEnvironment

func NewEnvironment() (environment *Environment)

NewEnvironment creates a new Oracle environment.

func (*Environment) Close

func (environment *Environment) Close() error

Close disconnects from servers and resets optional fields.

func (*Environment) IsOpen

func (environment *Environment) IsOpen() bool

IsOpen returns true when the environment is open; otherwise, false.

Calling Close will cause IsOpen to return false. Once closed, the environment may be re-opened by calling Open.

func (*Environment) Open

func (environment *Environment) Open() error

Open starts an Oracle environment.

Calling Open is required prior to any other Environment method call.

func (*Environment) OpenConnection

func (environment *Environment) OpenConnection(connStr string) (driver.Conn, error)

OpenConnection starts a connection to an Oracle server and returns a driver.Conn.

The connection string has the form username/password@dblink where dblink is defined on your client machine's tnsnames.ora file. For example, scott/tiger@orcl.

func (*Environment) OpenServer

func (environment *Environment) OpenServer(dblink string) (*Server, error)

OpenServer connects to an Oracle server.

func (*Environment) SetStatementConfig

func (environment *Environment) SetStatementConfig(c StatementConfig)

Sets the StatementConfig on the Environment and all open Environment Servers.

func (*Environment) StatementConfig

func (environment *Environment) StatementConfig() *StatementConfig

StatementConfig returns a *StatementConfig.

type ExecResult

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

An execution result.

Implements the driver.Result interface.

func (*ExecResult) LastInsertId

func (execResult *ExecResult) LastInsertId() (int64, error)

LastInsertId returns the identity value from an insert statement.

There are two setup steps required to reteive the LastInsertId. One, specify a 'returning into' clause in the SQL insert statement. And, two, specify a nil parameter to DB.Exec or Stmt.Exec.

For example:

db, err := sql.Open("oracle", "scott/tiger@orcl")

db.Exec("create table t1 (c1 number(19,0) generated always as identity (start with 1 increment by 1), c2 varchar2(48 char))")

result, err := db.Exec("insert into t1 (c2) values ('go') returning c1 into :c1", nil)

id, err := result.LastInsertId()

func (*ExecResult) RowsAffected

func (execResult *ExecResult) RowsAffected() (int64, error)

RowsAffected returns the number of rows affected by the exec statement.

type Float32

type Float32 struct {
	IsNull bool
	Value  float32
}

Float32 is a nullable float32.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(16,15))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Float32 slice
a := make([]Float32, 5)
a[0] = Float32{Value: -float32(6.28318)}
a[1] = Float32{Value: -float32(3.14159)}
a[2] = Float32{IsNull: true}
a[3] = Float32{Value: float32(3.14159)}
a[4] = Float32{Value: float32(6.28318)}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraF32 to Prepare method to return Float32 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraF32)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -6.28318}
{false -3.14159}
{true 0}
{false 3.14159}
{false 6.28318}

func (Float32) Equals

func (this Float32) Equals(other Float32) bool

Equals returns true when the receiver and specified Float32 are both null, or when the receiver and specified Float32 are both not null and Values are equal.

type Float64

type Float64 struct {
	IsNull bool
	Value  float64
}

Float64 is a nullable float64.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(16,15))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Float64 slice
a := make([]Float64, 5)
a[0] = Float64{Value: -float64(6.28318)}
a[1] = Float64{Value: -float64(3.14159)}
a[2] = Float64{IsNull: true}
a[3] = Float64{Value: float64(3.14159)}
a[4] = Float64{Value: float64(6.28318)}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraF64 to Prepare method to return Float64 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraF64)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -6.28318}
{false -3.14159}
{true 0}
{false 3.14159}
{false 6.28318}

func (Float64) Equals

func (this Float64) Equals(other Float64) bool

Equals returns true when the receiver and specified Float64 are both null, or when the receiver and specified Float64 are both not null and Values are equal.

type GoColumnType

type GoColumnType uint

ColumnGoType defines the Go type returned from a sql select column.

const (
	// D defines a sql select column as a default Go type.
	D GoColumnType = iota
	// I64 defines a sql select column as a Go int64.
	I64
	// I32 defines a sql select column as a Go int32.
	I32
	// I16 defines a sql select column as a Go int16.
	I16
	// I8 defines a sql select column as a Go int8.
	I8
	// U64 defines a sql select column as a Go uint64.
	U64
	// U32 defines a sql select column as a Go uint32.
	U32
	// U16 defines a sql select column as a Go uint16.
	U16
	// U8 defines a sql select column as a Go uint8.
	U8
	// F64 defines a sql select column as a Go float64.
	F64
	// F32 defines a sql select column as a Go float32.
	F32
	// OraI64 defines a sql select column as a nullable Go oracle.Int64.
	OraI64
	// OraI32 defines a sql select column as a nullable Go oracle.Int32.
	OraI32
	// OraI16 defines a sql select column as a nullable Go oracle.Int16.
	OraI16
	// OraI8 defines a sql select column as a nullable Go oracle.Int8.
	OraI8
	// OraU64 defines a sql select column as a nullable Go oracle.Uint64.
	OraU64
	// OraU32 defines a sql select column as a nullable Go oracle.Uint32.
	OraU32
	// OraU16 defines a sql select column as a nullable Go oracle.Uint16.
	OraU16
	// OraU8 defines a sql select column as a nullable Go oracle.Uint8.
	OraU8
	// OraF64 defines a sql select column as a nullable Go oracle.Float64.
	OraF64
	// OraF32 defines a sql select column as a nullable Go oracle.Float32.
	OraF32
	// T defines a sql select column as a Go time.Time.
	T
	// OraT defines a sql select column as a nullable Go oracle.Time.
	OraT
	// S defines a sql select column as a Go string.
	S
	// OraS defines a sql select column as a nullable Go string.
	OraS
	// B defines a sql select column as a Go bool.
	B
	// OraB defines a sql select column as a nullable Go bool.
	OraB
	// Bits defines a sql select column or bind parmeter as a Go byte slice.
	Bits
	// OraBits defines a sql select column as a nullable Go oracle.Bytes.
	OraBits
)

type Int16

type Int16 struct {
	IsNull bool
	Value  int16
}

Int16 is a nullable int16.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Int16 slice
a := make([]Int16, 5)
a[0] = Int16{Value: -9}
a[1] = Int16{Value: -1}
a[2] = Int16{IsNull: true}
a[3] = Int16{Value: 1}
a[4] = Int16{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraI16 to Prepare method to return Int16 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraI16)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -9}
{false -1}
{true 0}
{false 1}
{false 9}

func (Int16) Equals

func (this Int16) Equals(other Int16) bool

Equals returns true when the receiver and specified Int16 are both null, or when the receiver and specified Int16 are both not null and Values are equal.

type Int32

type Int32 struct {
	IsNull bool
	Value  int32
}

Int32 is a nullable int32.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Int32 slice
a := make([]Int32, 5)
a[0] = Int32{Value: -9}
a[1] = Int32{Value: -1}
a[2] = Int32{IsNull: true}
a[3] = Int32{Value: 1}
a[4] = Int32{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraI32 to Prepare method to return Int32 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraI32)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -9}
{false -1}
{true 0}
{false 1}
{false 9}

func (Int32) Equals

func (this Int32) Equals(other Int32) bool

Equals returns true when the receiver and specified Int32 are both null, or when the receiver and specified Int32 are both not null and Values are equal.

type Int64

type Int64 struct {
	IsNull bool
	Value  int64
}

Int64 is a nullable int64.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Int64 slice
a := make([]Int64, 5)
a[0] = Int64{Value: -9}
a[1] = Int64{Value: -1}
a[2] = Int64{IsNull: true}
a[3] = Int64{Value: 1}
a[4] = Int64{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraI64 to Prepare method to return Int64 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraI64)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -9}
{false -1}
{true 0}
{false 1}
{false 9}

func (Int64) Equals

func (this Int64) Equals(other Int64) bool

Equals returns true when the receiver and specified Int64 are both null, or when the receiver and specified Int64 are both not null and Values are equal.

type Int8

type Int8 struct {
	IsNull bool
	Value  int8
}

Int8 is a nullable int8.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Int8 slice
a := make([]Int8, 5)
a[0] = Int8{Value: -9}
a[1] = Int8{Value: -1}
a[2] = Int8{IsNull: true}
a[3] = Int8{Value: 1}
a[4] = Int8{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraI8 to Prepare method to return Int8 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraI8)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false -9}
{false -1}
{true 0}
{false 1}
{false 9}

func (Int8) Equals

func (this Int8) Equals(other Int8) bool

Equals returns true when the receiver and specified Int8 are both null, or when the receiver and specified Int8 are both not null and Values are equal.

type IntervalDS

type IntervalDS struct {
	IsNull     bool
	Day        int32
	Hour       int32
	Minute     int32
	Second     int32
	Nanosecond int32
}

IntervalDS represents a nullable Oracle INTERVAL DAY TO SECOND.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 interval day to second)", tableName))
defer stmt.Close()
stmt.Execute()

// insert IntervalDS slice
a := make([]IntervalDS, 5)
a[0] = IntervalDS{Day: 1, Hour: 1, Minute: 1, Second: 1, Nanosecond: 123456789}
a[1] = IntervalDS{Day: 59, Hour: 59, Minute: 59, Second: 59, Nanosecond: 123456789}
a[2] = IntervalDS{IsNull: true}
a[3] = IntervalDS{Day: -1, Hour: -1, Minute: -1, Second: -1, Nanosecond: -123456789}
a[4] = IntervalDS{Day: -59, Hour: -59, Minute: -59, Second: -59, Nanosecond: -123456789}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// fetch IntervalDS
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v, ", resultSet.Row[0])
}

Output:

{false 1 1 1 1 123457000}, {false 59 59 59 59 123457000}, {true 0 0 0 0 0}, {false -1 -1 -1 -1 -123457000}, {false -59 -59 -59 -59 -123457000},

func (IntervalDS) Equals

func (this IntervalDS) Equals(other IntervalDS) bool

Equals returns true when the receiver and specified IntervalDS are both null, or when the receiver and specified IntervalDS are both not null, and all other fields are equal.

func (IntervalDS) ShiftTime

func (this IntervalDS) ShiftTime(t time.Time) time.Time

ShiftTime returns a new Time with IntervalDS applied.

Example

interval := IntervalDS{Day: 1, Hour: 1, Minute: 1, Second: 1, Nanosecond: 123456789}
actual := interval.ShiftTime(time.Date(2000, time.Month(1), 1, 0, 0, 0, 0, time.Local))
fmt.Println(actual.Day(), actual.Hour(), actual.Minute(), actual.Second(), actual.Nanosecond())

Output:

2 1 1 1 123456789

type IntervalYM

type IntervalYM struct {
	IsNull bool
	Year   int32
	Month  int32
}

IntervalYM represents a nullable Oracle INTERVAL YEAR TO MONTH.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 interval year to month)", tableName))
defer stmt.Close()
stmt.Execute()

// insert IntervalYM slice
a := make([]IntervalYM, 5)
a[0] = IntervalYM{Year: 1, Month: 1}
a[1] = IntervalYM{Year: 99, Month: 9}
a[2] = IntervalYM{IsNull: true}
a[3] = IntervalYM{Year: -1, Month: -1}
a[4] = IntervalYM{Year: -99, Month: -9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// fetch IntervalYM
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v, ", resultSet.Row[0])
}

Output:

{false 1 1}, {false 99 9}, {true 0 0}, {false -1 -1}, {false -99 -9},

func (IntervalYM) Equals

func (this IntervalYM) Equals(other IntervalYM) bool

Equals returns true when the receiver and specified IntervalYM are both null, or when the receiver and specified IntervalYM are both not null, Year are equal and Month are equal.

func (IntervalYM) ShiftTime

func (this IntervalYM) ShiftTime(t time.Time) time.Time

ShiftTime returns a new Time with IntervalYM applied.

Example

interval := IntervalYM{Year: 1, Month: 1}
actual := interval.ShiftTime(time.Date(2000, time.January, 0, 0, 0, 0, 0, time.Local))
fmt.Println(actual.Year(), actual.Month(), actual.Day())
// returns normalized date per time.AddDate

type QueryResult

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

QueryResult contains methods to retrieve the results of a SQL select statement.

Implements the driver.Rows interface.

func (*QueryResult) Close

func (queryResult *QueryResult) Close() error

Close performs no operations.

Close is a member of the driver.Rows interface.

func (*QueryResult) Columns

func (queryResult *QueryResult) Columns() []string

Columns returns query column names.

Columns is a member of the driver.Rows interface.

func (*QueryResult) Next

func (queryResult *QueryResult) Next(dest []driver.Value) (err error)

Next populates the specified slice with the next row of data.

Returns io.EOF when there are no more rows.

Next is a member of the driver.Rows interface.

type ResultSet

type ResultSet struct {
	Err         error
	Index       int
	Row         []interface{}
	ColumnNames []string
	Config      ResultSetConfig
	// contains filtered or unexported fields
}

ResultSet contains methods to retrieve the results of a SQL select statement.

ResultSet internally manages opening resources and closing resources and doesn't require any user calls to an open method or close method.

Example (Cursor_multiple)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char))", tableName))
defer stmt.Close()
stmt.Execute()

// insert records
a := make([]int64, 3)
a[0] = 5
a[1] = 7
a[2] = 9
b := make([]string, 3)
b[0] = "Go is expressive, concise, clean, and efficient."
b[1] = "Its concurrency mechanisms make it easy to"
b[2] = "Go compiles quickly to machine code yet has"
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2) values (:1, :2)", tableName))
stmt.Execute(a, b)

// create proc
stmt, _ = ses.Prepare(fmt.Sprintf("create or replace procedure proc1(p1 out sys_refcursor, p2 out sys_refcursor) as begin open p1 for select c1 from %v order by c1; open p2 for select c2 from %v order by c2; end proc1;", tableName, tableName))
defer stmt.Close()
stmt.Execute()

// pass *ResultSet to Execute for an out sys_refcursor
// call proc
stmt, _ = ses.Prepare("call proc1(:1, :2)")
defer stmt.Close()
resultSetC1 := &ResultSet{}
resultSetC2 := &ResultSet{}
stmt.Execute(resultSetC1, resultSetC2)
fmt.Println("--- first result set ---")
if resultSetC1.IsOpen() {
	for resultSetC1.Next() {
		fmt.Println(resultSetC1.Row[0])
	}
}
fmt.Println("--- second result set ---")
if resultSetC2.IsOpen() {
	for resultSetC2.Next() {
		fmt.Println(resultSetC2.Row[0])
	}
}

Output:

--- first result set ---
5
7
9
--- second result set ---
Go compiles quickly to machine code yet has
Go is expressive, concise, clean, and efficient.
Its concurrency mechanisms make it easy to

Example (Cursor_single)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char))", tableName))
defer stmt.Close()
stmt.Execute()

// insert records
a := make([]int64, 3)
a[0] = 5
a[1] = 7
a[2] = 9
b := make([]string, 3)
b[0] = "Go is expressive, concise, clean, and efficient."
b[1] = "Its concurrency mechanisms make it easy to"
b[2] = "Go compiles quickly to machine code yet has"
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2) values (:1, :2)", tableName))
stmt.Execute(a, b)

// create proc
stmt, _ = ses.Prepare(fmt.Sprintf("create or replace procedure proc1(p1 out sys_refcursor) as begin open p1 for select c1, c2 from %v order by c1; end proc1;", tableName))
defer stmt.Close()
stmt.Execute()

// pass *ResultSet to Execute for an out sys_refcursor
// call proc
stmt, _ = ses.Prepare("call proc1(:1)")
defer stmt.Close()
resultSet := &ResultSet{}
stmt.Execute(resultSet)
if resultSet.IsOpen() {
	for resultSet.Next() {
		fmt.Println(resultSet.Row[0], resultSet.Row[1])
	}
}

Output:

5 Go is expressive, concise, clean, and efficient.
7 Its concurrency mechanisms make it easy to
9 Go compiles quickly to machine code yet has

func (*ResultSet) IsOpen

func (resultSet *ResultSet) IsOpen() bool

IsOpen returns true when a result set is open; otherwise, false.

func (*ResultSet) Len

func (resultSet *ResultSet) Len() int

Len returns the number of rows retrieved.

func (*ResultSet) Next

func (resultSet *ResultSet) Next() bool

Next attempts to load a row of data from an Oracle buffer. True is returned when a row of data is retrieved. False is returned when no data is available.

Retrieve the loaded row from the ResultSet.Row field. ResultSet.Row is updated on each call to Next. ResultSet.Row is set to nil when Next returns false.

When Next returns false check ResultSet.Err for any error that may have occured.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// insert records
a := make([]uint16, 5)
for n, _ := range a {
	a[n] = uint16(n)
}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(a)
fmt.Println(rowsAffected)

// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), U16)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v, ", resultSet.Row[0])
}

Output:

5
0, 1, 2, 3, 4,

func (*ResultSet) NextRow

func (resultSet *ResultSet) NextRow() []interface{}

NextRow attempts to load a row from the Oracle buffer and return the row. Nil is returned when there's no data.

When NextRow returns nil check ResultSet.Err for any error that may have occured.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char), c3 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()

// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (7, 'go', '1')", tableName))
defer stmt.Close()
stmt.Execute()

// fetch record
stmt, _ = ses.Prepare(fmt.Sprintf("select c1, c2, c3 from %v", tableName))
resultSet, _ := stmt.Fetch()
row := resultSet.NextRow()
fmt.Printf("%v %v %v", row[0], row[1], row[2])

Output:

7 go true

type ResultSetConfig

type ResultSetConfig struct {

	// TrueRune is rune a Go bool true value from SQL select-list character column.
	//
	// The is default is '1'.
	TrueRune rune
	// contains filtered or unexported fields
}

ResultSetConfig affects the association of Oracle select-list columns to Go types.

func NewResultSetConfig

func NewResultSetConfig() ResultSetConfig

NewResultSetConfig returns a ResultSetConfig with default values.

func (*ResultSetConfig) BinaryDouble

func (resultSetConfig *ResultSetConfig) BinaryDouble() GoColumnType

BinaryDouble returns a GoColumnType associated to an Oracle select-list BINARY_DOUBLE column.

The default is F64.

BinaryDouble is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, BinaryDouble is used.

func (*ResultSetConfig) BinaryFloat

func (resultSetConfig *ResultSetConfig) BinaryFloat() GoColumnType

BinaryFloat returns a GoColumnType associated to an Oracle select-list BINARY_FLOAT column.

The default for the database/sql package is F64.

The default for the oracle package is F32.

BinaryFloat is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, BinaryFloat is used.

func (*ResultSetConfig) Blob

func (resultSetConfig *ResultSetConfig) Blob() GoColumnType

Blob returns a GoColumnType associated to an Oracle select-list BLOB column.

The default is Bits.

Blob is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Blob is used.

func (*ResultSetConfig) Char

func (resultSetConfig *ResultSetConfig) Char() GoColumnType

Char returns a GoColumnType associated to an Oracle select-list CHAR column and NCHAR column.

The default is S.

Char is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Char is used.

func (*ResultSetConfig) Char1

func (resultSetConfig *ResultSetConfig) Char1() GoColumnType

Char1 returns a GoColumnType associated to an Oracle select-list CHAR column with length 1 and NCHAR column with length 1.

The default is B.

Char1 is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Char1 is used.

func (*ResultSetConfig) Clob

func (resultSetConfig *ResultSetConfig) Clob() GoColumnType

Clob returns a GoColumnType associated to an Oracle select-list CLOB column and NCLOB column.

The default is S.

Clob is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Clob is used.

func (*ResultSetConfig) Date

func (resultSetConfig *ResultSetConfig) Date() GoColumnType

Date returns a GoColumnType associated to an Oracle select-list DATE column.

The default is T.

Date is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Date is used.

func (*ResultSetConfig) Float

func (resultSetConfig *ResultSetConfig) Float() GoColumnType

Float returns a GoColumnType associated to an Oracle select-list FLOAT column.

The default is F64.

Float is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Float is used.

func (*ResultSetConfig) Long

func (resultSetConfig *ResultSetConfig) Long() GoColumnType

Long returns a GoColumnType associated to an Oracle select-list LONG column.

The default is S.

Long is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Long is used.

func (*ResultSetConfig) LongRaw

func (resultSetConfig *ResultSetConfig) LongRaw() GoColumnType

LongRaw returns a GoColumnType associated to an Oracle select-list LONG RAW column.

The default is Bits.

LongRaw is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, LongRaw is used.

func (*ResultSetConfig) NumberScaled

func (resultSetConfig *ResultSetConfig) NumberScaled() GoColumnType

NumberScaled returns a GoColumnType associated to an Oracle select-list NUMBER column defined with a scale greater than zero.

The default is F64.

NumberScaled is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, NumberScaled is used.

func (*ResultSetConfig) NumberScaless

func (resultSetConfig *ResultSetConfig) NumberScaless() GoColumnType

NumberScaless returns a GoColumnType associated to an Oracle select-list NUMBER column defined with scale zero.

The default is I64.

The database/sql package uses NumberScaless.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, NumberScaless is used.

func (*ResultSetConfig) Raw

func (resultSetConfig *ResultSetConfig) Raw() GoColumnType

Raw returns a GoColumnType associated to an Oracle select-list RAW column.

The default is Bits.

Raw is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Raw is used.

func (*ResultSetConfig) Reset

func (resultSetConfig *ResultSetConfig) Reset()

Reset sets driver-defined values to all fields.

func (*ResultSetConfig) SetBinaryDouble

func (resultSetConfig *ResultSetConfig) SetBinaryDouble(gct GoColumnType) (err error)

SetBinaryDouble sets a GoColumnType associated to an Oracle select-list BINARY_DOUBLE column.

Valid values are I64, I32, I16, I8, U64, U32, U16, U8, F64, F32, OraI64, OraI32, OraI16, OraI8, OraU64, OraU32, OraU16, OraU8, OraF64, OraF32.

Returns an error if a non-numeric GoColumnType is specified.

func (*ResultSetConfig) SetBinaryFloat

func (resultSetConfig *ResultSetConfig) SetBinaryFloat(gct GoColumnType) (err error)

SetBinaryFloat sets a GoColumnType associated to an Oracle select-list BINARY_FLOAT column.

Valid values are I64, I32, I16, I8, U64, U32, U16, U8, F64, F32, OraI64, OraI32, OraI16, OraI8, OraU64, OraU32, OraU16, OraU8, OraF64, OraF32.

Returns an error if a non-numeric GoColumnType is specified.

func (*ResultSetConfig) SetBlob

func (resultSetConfig *ResultSetConfig) SetBlob(gct GoColumnType) (err error)

SetBlob sets a GoColumnType associated to an Oracle select-list BLOB column.

Valid values are Bits and OraBits.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetChar

func (resultSetConfig *ResultSetConfig) SetChar(gct GoColumnType) (err error)

SetChar sets a GoColumnType associated to an Oracle select-list CHAR column and NCHAR column.

Valid values are S and OraS.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetChar1

func (resultSetConfig *ResultSetConfig) SetChar1(gct GoColumnType) (err error)

SetChar1 sets a GoColumnType associated to an Oracle select-list CHAR column with length 1 and NCHAR column with length 1.

Valid values are B, OraB, S and OraS.

Returns an error if a non-bool or non-string GoColumnType is specified.

func (*ResultSetConfig) SetClob

func (resultSetConfig *ResultSetConfig) SetClob(gct GoColumnType) (err error)

SetClob sets a GoColumnType associated to an Oracle select-list CLOB column and NCLOB column.

Valid values are S and OraS.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetDate

func (resultSetConfig *ResultSetConfig) SetDate(gct GoColumnType) (err error)

SetDate sets a GoColumnType associated to an Oracle select-list DATE column.

Valid values are T and OraT.

Returns an error if a non-time GoColumnType is specified.

func (*ResultSetConfig) SetFloat

func (resultSetConfig *ResultSetConfig) SetFloat(gct GoColumnType) (err error)

SetFloat sets a GoColumnType associated to an Oracle select-list FLOAT column.

Valid values are I64, I32, I16, I8, U64, U32, U16, U8, F64, F32, OraI64, OraI32, OraI16, OraI8, OraU64, OraU32, OraU16, OraU8, OraF64, OraF32.

Returns an error if a non-numeric GoColumnType is specified.

func (*ResultSetConfig) SetLong

func (resultSetConfig *ResultSetConfig) SetLong(gct GoColumnType) (err error)

SetLong sets a GoColumnType associated to an Oracle select-list LONG column.

Valid values are S and OraS.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetLongRaw

func (resultSetConfig *ResultSetConfig) SetLongRaw(gct GoColumnType) (err error)

SetLongRaw sets a GoColumnType associated to an Oracle select-list LONG RAW column.

Valid values are Bits and OraBits.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetNumberScaled

func (resultSetConfig *ResultSetConfig) SetNumberScaled(gct GoColumnType) (err error)

SetNumberScaled sets a GoColumnType associated to an Oracle select-list NUMBER column defined with a scale greater than zero.

Valid values are I64, I32, I16, I8, U64, U32, U16, U8, F64, F32, OraI64, OraI32, OraI16, OraI8, OraU64, OraU32, OraU16, OraU8, OraF64, OraF32.

Returns an error if a non-numeric GoColumnType is specified.

func (*ResultSetConfig) SetNumberScaless

func (resultSetConfig *ResultSetConfig) SetNumberScaless(gct GoColumnType) (err error)

SetNumberScaless sets a GoColumnType associated to an Oracle select-list NUMBER column defined with scale zero.

Valid values are I64, I32, I16, I8, U64, U32, U16, U8, F64, F32, OraI64, OraI32, OraI16, OraI8, OraU64, OraU32, OraU16, OraU8, OraF64, OraF32.

Returns an error if a non-numeric GoColumnType is specified.

func (*ResultSetConfig) SetRaw

func (resultSetConfig *ResultSetConfig) SetRaw(gct GoColumnType) (err error)

SetRaw sets a GoColumnType associated to an Oracle select-list RAW column.

Valid values are Bits and OraBits.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) SetTimestamp

func (resultSetConfig *ResultSetConfig) SetTimestamp(gct GoColumnType) (err error)

SetTimestamp sets a GoColumnType associated to an Oracle select-list TIMESTAMP column.

Valid values are T and OraT.

Returns an error if a non-time GoColumnType is specified.

func (*ResultSetConfig) SetTimestampLtz

func (resultSetConfig *ResultSetConfig) SetTimestampLtz(gct GoColumnType) (err error)

SetTimestampLtz sets a GoColumnType associated to an Oracle select-list TIMESTAMP WITH LOCAL TIME ZONE column.

Valid values are T and OraT.

Returns an error if a non-time GoColumnType is specified.

func (*ResultSetConfig) SetTimestampTz

func (resultSetConfig *ResultSetConfig) SetTimestampTz(gct GoColumnType) (err error)

SetTimestampTz sets a GoColumnType associated to an Oracle select-list TIMESTAMP WITH TIME ZONE column.

Valid values are T and OraT.

Returns an error if a non-time GoColumnType is specified.

func (*ResultSetConfig) SetVarchar

func (resultSetConfig *ResultSetConfig) SetVarchar(gct GoColumnType) (err error)

SetVarchar sets a GoColumnType associated to an Oracle select-list VARCHAR column, VARCHAR2 column and NVARCHAR2 column.

Valid values are S and OraS.

Returns an error if a non-string GoColumnType is specified.

func (*ResultSetConfig) Timestamp

func (resultSetConfig *ResultSetConfig) Timestamp() GoColumnType

Timestamp returns a GoColumnType associated to an Oracle select-list TIMESTAMP column.

The default is T.

Timestamp is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Timestamp is used.

func (*ResultSetConfig) TimestampLtz

func (resultSetConfig *ResultSetConfig) TimestampLtz() GoColumnType

TimestampLtz returns a GoColumnType associated to an Oracle select-list TIMESTAMP WITH LOCAL TIME ZONE column.

The default is T.

TimestampLtz is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, TimestampLtz is used.

func (*ResultSetConfig) TimestampTz

func (resultSetConfig *ResultSetConfig) TimestampTz() GoColumnType

TimestampTz returns a GoColumnType associated to an Oracle select-list TIMESTAMP WITH TIME ZONE column.

The default is T.

TimestampTz is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, TimestampTz is used.

func (*ResultSetConfig) Varchar

func (resultSetConfig *ResultSetConfig) Varchar() GoColumnType

Varchar returns a GoColumnType associated to an Oracle select-list VARCHAR column, VARCHAR2 column and NVARCHAR2 column.

The default is S.

Varchar is used by the database/sql package.

When using the oracle package directly, custom GoColumnType associations may be specified to the Session.Prepare method. If no custom GoColumnType association is specified, Varchar is used.

type Server

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

An Oracle server associated with an environment.

func (*Server) Close

func (server *Server) Close() error

Close disconnects from an Oracle server.

Any open sessions associated with the server are closed.

Calling Close will cause Server.IsOpen to return false. Once closed, a server cannot be re-opened. Call Environment.OpenServer to open a new server.

func (*Server) IsOpen

func (server *Server) IsOpen() bool

IsOpen returns true when the server is open; otherwise, false.

Calling Close will cause Server.IsOpen to return false. Once closed, a server cannot be re-opened. Call Environment.OpenServer to open a new server.

func (*Server) OpenSession

func (server *Server) OpenSession(username string, password string) (*Session, error)

OpenSession opens a session on an Oracle server and returns a *Session.

func (*Server) Ping

func (server *Server) Ping() error

Ping return nil when an Oracle server is contacted; otherwise, an error.

Ping requires the server have at least one open session.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()

// open a session before calling Ping
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

err := srv.Ping()
if err == nil {
	fmt.Println("Ping sucessful")
}

Output:

Ping sucessful

func (*Server) SetStatementConfig

func (server *Server) SetStatementConfig(c StatementConfig)

Sets the StatementConfig on the Server and all open Server Sessions.

func (*Server) StatementConfig

func (server *Server) StatementConfig() *StatementConfig

StatementConfig returns a *StatementConfig.

func (*Server) Version

func (server *Server) Version() (string, error)

Version returns the Oracle database server version.

Version requires the server have at least one open session.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()

// open a session before calling Version
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

version, err := srv.Version()
if version != "" && err == nil {
	fmt.Println("Received version from server")
}

Output:

Received version from server

type Session

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

A session associated with an Oracle server.

func (*Session) BeginTransaction

func (session *Session) BeginTransaction() (*Transaction, error)

BeginTransaction starts a transaction and returns a *Transaction.

func (*Session) Close

func (session *Session) Close() error

Close ends a session on an Oracle server.

Any open statements associated with the session are closed.

Calling Close will cause Session.IsOpen to return false. Once closed, a session cannot be re-opened. Call Server.OpenSession to open a new session.

func (*Session) CommitTransaction

func (session *Session) CommitTransaction() error

CommitTransaction commits a transaction.

func (*Session) IsOpen

func (session *Session) IsOpen() bool

IsOpen returns true when a session is open; otherwise, false.

Calling Close will cause Session.IsOpen to return false. Once closed, a session cannot be re-opened. Call Server.OpenSession to open a new session.

func (*Session) Prepare

func (session *Session) Prepare(sql string, goColumnTypes ...GoColumnType) (*Statement, error)

Prepare readies a sql statement associated with a session and returns a *Statement.

func (*Session) RollbackTransaction

func (session *Session) RollbackTransaction() error

RollbackTransaction rolls back a transaction.

func (*Session) SetStatementConfig

func (session *Session) SetStatementConfig(c StatementConfig)

Sets the StatementConfig on the Session and all open Session Statements.

func (*Session) StatementConfig

func (session *Session) StatementConfig() *StatementConfig

StatementConfig returns a *StatementConfig.

type Statement

type Statement struct {
	Config StatementConfig
	// contains filtered or unexported fields
}

A sql statement associated with an Oracle session.

Implements the driver.Stmt interface.

func (*Statement) Close

func (statement *Statement) Close() error

Close ends a sql statement.

Calling Close will cause Statement.IsOpen to return false. Once closed, a statement cannot be re-opened. Call Statement.Prepare to create a new statement.

Close is a member of the driver.Stmt interface.

func (*Statement) Exec

func (statement *Statement) Exec(values []driver.Value) (result driver.Result, err error)

Exec runs a SQL statement on an Oracle server returning driver.Result and a possible error.

Exec is meant to be called by the database/sql package.

Exec is a member of the driver.Stmt interface.

Example (Delete)

db, _ := sql.Open("oracle", testConnectionStr)
defer db.Close()

tableName := tableName()
db.Exec(fmt.Sprintf("create table %v (c1 number)", tableName))
db.Exec(fmt.Sprintf("insert into %v (c1) values (9)", tableName))

// placeholder ':1' is bound by position; ':1' may be any name
var v int64 = 9
result, _ := db.Exec(fmt.Sprintf("delete from %v where c1 = :1", tableName), v)
rowsAffected, _ := result.RowsAffected()
fmt.Println(rowsAffected)

Output:

1

Example (Insert)

db, _ := sql.Open("oracle", testConnectionStr)
defer db.Close()

tableName := tableName()
db.Exec(fmt.Sprintf("create table %v (c1 number)", tableName))

// placeholder ':c1' is bound by position; ':c1' may be any name
var value int64 = 9
result, _ := db.Exec(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName), value)
rowsAffected, _ := result.RowsAffected()
fmt.Println(rowsAffected)

Output:

1

Example (Insert_bool)

db, _ := sql.Open("oracle", testConnectionStr)
defer db.Close()

tableName := tableName()
db.Exec(fmt.Sprintf("create table %v (c1 char(1 byte))", tableName))

// default false symbol is '0'
// default true symbol is '1'
// placeholder ':c1' is bound by position; ':c1' may be any name
var value bool = true
result, _ := db.Exec(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName), value)
rowsAffected, _ := result.RowsAffected()
fmt.Println(rowsAffected)

Output:

1

Example (Insert_return_identity)

db, _ := sql.Open("oracle", testConnectionStr)
defer db.Close()

tableName := tableName()
db.Exec(fmt.Sprintf("create table %v (c1 number(19,0) generated always as identity (start with 1 increment by 1), c2 varchar2(48 char))", tableName))

// use a 'returning into' SQL clause and specify a nil parameter to Exec
// placeholder ':c1' is bound by position; ':c1' may be any name
result, _ := db.Exec(fmt.Sprintf("insert into %v (c2) values ('go') returning c1 into :c1", tableName), nil)
id, _ := result.LastInsertId()
fmt.Println(id)

Output:

1

Example (Update)

db, _ := sql.Open("oracle", testConnectionStr)
defer db.Close()

tableName := tableName()
db.Exec(fmt.Sprintf("create table %v (c1 number)", tableName))
db.Exec(fmt.Sprintf("insert into %v (c1) values (9)", tableName))

// placeholder ':three' and ':nine' are bound by position; ':three' and ':nine' may be any name
var three int64 = 3
var nine int64 = 9
result, _ := db.Exec(fmt.Sprintf("update %v set c1 = :three where c1 = :nine", tableName), three, nine)
rowsAffected, _ := result.RowsAffected()
fmt.Println(rowsAffected)

Output:

1

func (*Statement) Execute

func (statement *Statement) Execute(params ...interface{}) (rowsAffected uint64, err error)

Execute runs a SQL statement on an Oracle server returning the number of rows affected and a possible error.

Execute is meant to be called when working with the oracle package directly.

Example (Delete)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (9)", tableName))
defer stmt.Close()
stmt.Execute()

// delete record
var value int64 = 9
stmt, _ = ses.Prepare(fmt.Sprintf("delete from %v where c1 = :1", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(value)
fmt.Println(rowsAffected)

Output:

1

Example (Insert)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// insert record
var value int64 = 9
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(value)
fmt.Println(rowsAffected)

Output:

1

Example (Insert_fetch_blob)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 blob)", tableName))
defer stmt.Close()
stmt.Execute()

// by default, byte slices are expected to be bound and retrieved
// to/from a binary column such as a blob
// insert record
a := make([]byte, 10)
for n, _ := range a {
	a[n] = byte(n)
}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(a)
fmt.Println(rowsAffected)

// fetch record
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
defer stmt.Close()
resultSet, _ := stmt.Fetch()
row := resultSet.NextRow()
fmt.Println(row[0])

Output:

1
[0 1 2 3 4 5 6 7 8 9]

Example (Insert_fetch_bool)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()

// insert 'false' record
var falseValue bool = false
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(falseValue)
// insert 'true' record
var trueValue bool = true
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(trueValue)

// fetch inserted records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
defer stmt.Close()
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v ", resultSet.Row[0])
}

Output:

false true

Example (Insert_fetch_bool_alternate)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()

// Update StatementConfig to change the FalseRune and TrueRune inserted into the database
// insert 'false' record
var falseValue bool = false
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Config.FalseRune = 'N'
stmt.Execute(falseValue)
// insert 'true' record
var trueValue bool = true
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Config.TrueRune = 'Y'
stmt.Execute(trueValue)

// Update ResultSetConfig to change the TrueRune
// used to translate an Oracle char to a Go bool
// fetch inserted records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
defer stmt.Close()
resultSet, _ := stmt.Fetch()
resultSet.Config.TrueRune = 'Y'
for resultSet.Next() {
	fmt.Printf("%v ", resultSet.Row[0])
}

Output:

false true

Example (Insert_fetch_byteSlice)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// note the NUMBER column
// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// Specify stmt.Config.SetByteSlice(U8)
// Specify byte slice to be inserted into a NUMBER column
// insert records
a := make([]byte, 10)
for n, _ := range a {
	a[n] = byte(n)
}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Config.SetByteSlice(U8)
rowsAffected, _ := stmt.Execute(a)
fmt.Println(rowsAffected)

// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
defer stmt.Close()
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v, ", resultSet.Row[0])
}

Output:

10
0, 1, 2, 3, 4, 5, 6, 7, 8, 9,

Example (Insert_nullable)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char), c3 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()

// create nullable Go types for inserting null
// insert record
a := Int64{IsNull: true}
b := String{IsNull: true}
c := Bool{IsNull: true}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (:c1, :c2, :c3)", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(a, b, c)
fmt.Println(rowsAffected)

Output:

1

Example (Insert_return_identity)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(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 stmt.Close()
stmt.Execute()

// insert record
var id int64
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c2) values ('go') returning c1 into :c1", tableName))
defer stmt.Close()
// pass a numeric pointer to rereive a database generated identity value
stmt.Execute(&id)
fmt.Println(id)

Output:

1

Example (Insert_return_rowid)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// insert record
var rowid string
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (9) returning rowid into :r", tableName))
defer stmt.Close()
// pass a string pointer to rereive a rowid
stmt.Execute(&rowid)
if rowid != "" {
	fmt.Println("Retrieved rowid")
}

Output:

Retrieved rowid

Example (Insert_slice)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// insert one million rows with single round-trip to server
values := make([]int64, 1000000)
for n, _ := range values {
	values[n] = int64(n)
}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(values)
fmt.Println(rowsAffected)

Output:

1000000

Example (Update)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (9)", tableName))
defer stmt.Close()
stmt.Execute()

// update record
var a int64 = 3
var b int64 = 9
stmt, _ = ses.Prepare(fmt.Sprintf("update %v set c1 = :three where c1 = :nine", tableName))
defer stmt.Close()
rowsAffected, _ := stmt.Execute(a, b)
fmt.Println(rowsAffected)

Output:

1

func (*Statement) Fetch

func (statement *Statement) Fetch(params ...interface{}) (*ResultSet, error)

Fetch runs a SQL query on an Oracle server returning a *ResultSet and a possible error.

Fetch is meant to be called when working with the oracle package directly.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char), c3 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (3, 'slice', '0')", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (7, 'map', '1')", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (9, 'channel', '1')", tableName))
defer stmt.Close()
stmt.Execute()

// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1, c2, c3 from %v", tableName))
defer stmt.Close()
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v %v %v, ", resultSet.Row[0], resultSet.Row[1], resultSet.Row[2])
}

Output:

3 slice false, 7 map true, 9 channel true,

Example (Nullable)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number, c2 varchar2(48 char), c3 char(1 byte))", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (null, 'slice', '0')", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (7, null, '1')", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1, c2, c3) values (9, 'channel', null)", tableName))
defer stmt.Close()
stmt.Execute()

// Specify nullable return types to the Prepare method
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1, c2, c3 from %v", tableName), OraI64, OraS, OraB)
defer stmt.Close()
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Printf("%v %v %v, ", resultSet.Row[0], resultSet.Row[1], resultSet.Row[2])
}

Output:

{true 0} {false slice} {false false}, {false 7} {true } {false true}, {false 9} {false channel} {true false},

Example (Numerics)

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()
// insert record
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (9)", tableName))
defer stmt.Close()
stmt.Execute()

// Specify various numeric return types to the Prepare method
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1, c1, c1, c1, c1, c1, c1, c1, c1, c1 from %v", tableName), I64, I32, I16, I8, U64, U32, U16, U8, F64, F32)
defer stmt.Close()
resultSet, _ := stmt.Fetch()
row := resultSet.NextRow()
fmt.Printf("%v %v %v %v %v %v %v %v %v %v",
	reflect.TypeOf(row[0]).Name(),
	reflect.TypeOf(row[1]).Name(),
	reflect.TypeOf(row[2]).Name(),
	reflect.TypeOf(row[3]).Name(),
	reflect.TypeOf(row[4]).Name(),
	reflect.TypeOf(row[5]).Name(),
	reflect.TypeOf(row[6]).Name(),
	reflect.TypeOf(row[7]).Name(),
	reflect.TypeOf(row[8]).Name(),
	reflect.TypeOf(row[9]).Name())

Output:

int64 int32 int16 int8 uint64 uint32 uint16 uint8 float64 float32

func (*Statement) IsOpen

func (statement *Statement) IsOpen() bool

IsOpen returns true when a statement is open; otherwise, false.

Calling Close will cause Statement.IsOpen to return false. Once closed, a statement cannot be re-opened. Call Statement.Prepare to create a new statement.

func (*Statement) NumInput

func (statement *Statement) NumInput() int

NumInput returns the number of placeholders in a sql statement.

NumInput is a member of the driver.Stmt interface.

func (*Statement) Query

func (statement *Statement) Query(values []driver.Value) (driver.Rows, error)

Query runs a sql query on an Oracle server returning driver.Rows and a possible error.

Query is meant to be called by the database/sql package.

Query is a member of the driver.Stmt interface.

type StatementConfig

type StatementConfig struct {

	// FalseRune represents the false Go bool value sent to an Oracle server
	// during a parameter bind.
	//
	// The is default is '0'.
	FalseRune rune

	// TrueRune represents the true Go bool value sent to an Oracle server
	// during a parameter bind.
	//
	// The is default is '1'.
	TrueRune rune

	// ResultSet represents configuration options for a ResultSet struct.
	ResultSet ResultSetConfig
	// contains filtered or unexported fields
}

StatementConfig affects various aspects of a SQL statement.

func NewStatementConfig

func NewStatementConfig() StatementConfig

NewStatementConfig returns a StatementConfig with default values.

func (*StatementConfig) ByteSlice

func (statementConfig *StatementConfig) ByteSlice() GoColumnType

ByteSlice returns a GoColumnType associated to SQL statement []byte parameter.

The default is Bits.

ByteSlice is used by the database/sql package.

Sending a byte slice to an Oracle server as a parameter in a SQL statement requires knowing the destination column type ahead of time. Set ByteSlice to Bits if the destination column is BLOB, RAW or LONG RAW. Set ByteSlice to U8 if the destination column is NUMBER, BINARY_DOUBLE, BINARY_FLOAT or FLOAT.

func (*StatementConfig) LobBufferSize

func (statementConfig *StatementConfig) LobBufferSize() int

LobBufferSize returns the LOB buffer size in bytes used to define the sql select-column buffer size of an Oracle LOB type.

The default is 16,777,216 bytes.

The default is considered a moderate buffer where the 2GB max buffer may not be feasible on all clients.

func (*StatementConfig) LongBufferSize

func (statementConfig *StatementConfig) LongBufferSize() uint32

LongBufferSize returns the long buffer size in bytes used to define the sql select-column buffer size of an Oracle LONG type.

The default is 16,777,216 bytes.

The default is considered a moderate buffer where the 2GB max buffer may not be feasible on all clients.

func (*StatementConfig) LongRawBufferSize

func (statementConfig *StatementConfig) LongRawBufferSize() uint32

LongRawBufferSize returns the LONG RAW buffer size in bytes used to define the sql select-column buffer size of an Oracle LONG RAW type.

The default is 16,777,216 bytes.

The default is considered a moderate buffer where the 2GB max buffer may not be feasible on all clients.

func (*StatementConfig) PrefetchMemorySize

func (statementConfig *StatementConfig) PrefetchMemorySize() uint32

PrefetchMemorySize returns the prefetch memory size in bytes used during a SQL select command.

The default is 134,217,728 bytes.

PrefetchMemorySize works in coordination with PrefetchRowCount. When PrefetchRowCount is set to zero only PrefetchMemorySize is used; otherwise, the minimum of PrefetchRowCount and PrefetchMemorySize is used.

func (*StatementConfig) PrefetchRowCount

func (statementConfig *StatementConfig) PrefetchRowCount() uint32

PrefetchRowCount returns the number of rows to prefetch during a select query.

The default is 0.

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.

func (*StatementConfig) Reset

func (statementConfig *StatementConfig) Reset()

Reset sets driver-defined values to all fields.

func (*StatementConfig) SetByteSlice

func (statementConfig *StatementConfig) SetByteSlice(gct GoColumnType) (err error)

SetByteSlice sets a GoColumnType associated to SQL statement []byte parameter.

Valid values are U8 and Bits.

Returns an error if U8 or Bits is not specified.

func (*StatementConfig) SetLobBufferSize

func (statementConfig *StatementConfig) SetLobBufferSize(size int) error

SetLobBufferSize sets the LOB buffer size in bytes.

The maximum is 2,147,483,642 bytes.

Returns an error if the specified size is greater than 2,147,483,642.

func (*StatementConfig) SetLongBufferSize

func (statementConfig *StatementConfig) SetLongBufferSize(size uint32) error

SetLongBufferSize sets the long buffer size in bytes.

The maximum is 2,147,483,642 bytes.

Returns an error if the specified size is less than 1 or greater than 2,147,483,642.

func (*StatementConfig) SetLongRawBufferSize

func (statementConfig *StatementConfig) SetLongRawBufferSize(size uint32) error

SetLongRawBufferSize sets the LONG RAW buffer size in bytes.

The maximum is 2,147,483,642 bytes.

Returns an error if the specified size is greater than 2,147,483,642.

func (*StatementConfig) SetPrefetchMemorySize

func (statementConfig *StatementConfig) SetPrefetchMemorySize(prefetchMemorySize uint32) error

SetPrefetchMemorySize sets the prefetch memory size in bytes used during a SQL select command.

func (*StatementConfig) SetPrefetchRowCount

func (statementConfig *StatementConfig) SetPrefetchRowCount(prefetchRowCount uint32) error

SetPrefetchRowCount sets the number of rows to prefetch during a select query.

func (*StatementConfig) SetStringPtrBufferSize

func (statementConfig *StatementConfig) SetStringPtrBufferSize(size int) error

SetStringPtrBufferSize sets the size of a buffer used to store a string during *string parameter binding and []*string parameter binding in a SQL statement.

func (*StatementConfig) StringPtrBufferSize

func (statementConfig *StatementConfig) StringPtrBufferSize() int

StringPtrBufferSize returns the size of a buffer in bytes used to store a string during *string parameter binding and []*string parameter binding in a SQL statement.

The default is 4000 bytes.

For a *string parameter binding, you may wish to increase the size of StringPtrBufferSize depending on the Oracle column type. For VARCHAR2, NVARCHAR2, and RAW oracle columns the Oracle MAX_STRING_SIZE is usually 4000 but may be set up to 32767.

type String

type String struct {
	IsNull bool
	Value  string
}

String is a nullable string.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 varchar2(48 char))", tableName))
defer stmt.Close()
stmt.Execute()

// insert String slice
a := make([]String, 5)
a[0] = String{Value: "Go is expressive, concise, clean, and efficient."}
a[1] = String{Value: "Its concurrency mechanisms make it easy to"}
a[2] = String{IsNull: true}
a[3] = String{Value: "It's a fast, statically typed, compiled"}
a[4] = String{Value: "One of Go's key design goals is code"}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraS to Prepare method to return String values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraS)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false Go is expressive, concise, clean, and efficient.}
{false Its concurrency mechanisms make it easy to}
{true }
{false It's a fast, statically typed, compiled}
{false One of Go's key design goals is code}

func (String) Equals

func (this String) Equals(other String) bool

Equals returns true when the receiver and specified String are both null, or when the receiver and specified String are both not null and Values are equal.

type Time

type Time struct {
	IsNull bool
	Value  time.Time
}

Time is a nullable time.Time.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 timestamp)", tableName))
defer stmt.Close()
stmt.Execute()

// insert Time slice
a := make([]Time, 5)
a[0] = Time{Value: time.Date(2000, 1, 2, 3, 4, 5, 0, testDbsessiontimezone)}
a[1] = Time{Value: time.Date(2001, 2, 3, 4, 5, 6, 0, testDbsessiontimezone)}
a[2] = Time{IsNull: true}
a[3] = Time{Value: time.Date(2003, 4, 5, 6, 7, 8, 0, testDbsessiontimezone)}
a[4] = Time{Value: time.Date(2004, 5, 6, 7, 8, 9, 0, testDbsessiontimezone)}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraT to Prepare method to return Time values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraT)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	t := resultSet.Row[0].(Time)
	fmt.Printf("%v %v-%v-%v %v:%v:%v\n", t.IsNull, t.Value.Year(), t.Value.Month(), t.Value.Day(), t.Value.Hour(), t.Value.Minute(), t.Value.Second())
}

Output:

false 2000-January-2 3:4:5
false 2001-February-3 4:5:6
true 1-January-1 0:0:0
false 2003-April-5 6:7:8
false 2004-May-6 7:8:9

func (Time) Equals

func (this Time) Equals(other Time) bool

Equals returns true when the receiver and specified Time are both null, or when the receiver and specified Time are both not null and Values are equal.

type Transaction

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

A transaction associated with an Oracle server.

Implements the driver.Tx interface.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number)", tableName))
defer stmt.Close()
stmt.Execute()

// rollback
tx, _ := ses.BeginTransaction()
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (3)", tableName))
stmt.Execute()
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (5)", tableName))
stmt.Execute()
tx.Rollback()

// commit
tx, _ = ses.BeginTransaction()
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (7)", tableName))
stmt.Execute()
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (9)", tableName))
stmt.Execute()
tx.Commit()

// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName))
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

7
9

func (*Transaction) Commit

func (transaction *Transaction) Commit() (err error)

Commit commits a transaction.

Commit is a member of the driver.Tx interface.

func (*Transaction) Rollback

func (transaction *Transaction) Rollback() (err error)

Rollback rolls back a transaction.

Rollback is a member of the driver.Tx interface.

type Uint16

type Uint16 struct {
	IsNull bool
	Value  uint16
}

Uint16 is a nullable uint16.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Uint16 slice
a := make([]Uint16, 5)
a[0] = Uint16{Value: 0}
a[1] = Uint16{Value: 3}
a[2] = Uint16{IsNull: true}
a[3] = Uint16{Value: 7}
a[4] = Uint16{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraU16 to Prepare method to return Uint16 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraU16)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false 0}
{false 3}
{true 0}
{false 7}
{false 9}

func (Uint16) Equals

func (this Uint16) Equals(other Uint16) bool

Equals returns true when the receiver and specified Uint16 are both null, or when the receiver and specified Uint16 are both not null and Values are equal.

type Uint32

type Uint32 struct {
	IsNull bool
	Value  uint32
}

Uint32 is a nullable uint32.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Uint32 slice
a := make([]Uint32, 5)
a[0] = Uint32{Value: 0}
a[1] = Uint32{Value: 3}
a[2] = Uint32{IsNull: true}
a[3] = Uint32{Value: 7}
a[4] = Uint32{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraU32 to Prepare method to return Uint32 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraU32)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false 0}
{false 3}
{true 0}
{false 7}
{false 9}

func (Uint32) Equals

func (this Uint32) Equals(other Uint32) bool

Equals returns true when the receiver and specified Uint32 are both null, or when the receiver and specified Uint32 are both not null and Values are equal.

type Uint64

type Uint64 struct {
	IsNull bool
	Value  uint64
}

Uint64 is a nullable uint64.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Uint64 slice
a := make([]Uint64, 5)
a[0] = Uint64{Value: 0}
a[1] = Uint64{Value: 3}
a[2] = Uint64{IsNull: true}
a[3] = Uint64{Value: 7}
a[4] = Uint64{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraU64 to Prepare method to return Uint64 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraU64)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false 0}
{false 3}
{true 0}
{false 7}
{false 9}

func (Uint64) Equals

func (this Uint64) Equals(other Uint64) bool

Equals returns true when the receiver and specified Uint64 are both null, or when the receiver and specified Uint64 are both not null and Values are equal.

type Uint8

type Uint8 struct {
	IsNull bool
	Value  uint8
}

Uint8 is a nullable uint8.

Example

// setup
env := NewEnvironment()
env.Open()
defer env.Close()
srv, _ := env.OpenServer(testServerName)
defer srv.Close()
ses, _ := srv.OpenSession(testUsername, testPassword)
defer ses.Close()

// create table
tableName := tableName()
stmt, _ := ses.Prepare(fmt.Sprintf("create table %v (c1 number(10,0))", tableName))
defer stmt.Close()
stmt.Execute()

// insert Uint8 slice
a := make([]Uint8, 5)
a[0] = Uint8{Value: 0}
a[1] = Uint8{Value: 3}
a[2] = Uint8{IsNull: true}
a[3] = Uint8{Value: 7}
a[4] = Uint8{Value: 9}
stmt, _ = ses.Prepare(fmt.Sprintf("insert into %v (c1) values (:c1)", tableName))
defer stmt.Close()
stmt.Execute(a)

// Specify OraU8 to Prepare method to return Uint8 values
// fetch records
stmt, _ = ses.Prepare(fmt.Sprintf("select c1 from %v", tableName), OraU8)
resultSet, _ := stmt.Fetch()
for resultSet.Next() {
	fmt.Println(resultSet.Row[0])
}

Output:

{false 0}
{false 3}
{true 0}
{false 7}
{false 9}

func (Uint8) Equals

func (this Uint8) Equals(other Uint8) bool

Equals returns true when the receiver and specified Uint8 are both null, or when the receiver and specified Uint8 are both not null and Values are equal.