Oracle driver for Go, using the ODPI-C driver
Permalink
Failed to load latest commit information.
odpi remove dummy.go Sep 28, 2018
.gitignore initial commit Jun 24, 2017
.golangci.yml support RETURNING from DML Jun 21, 2018
.travis.yml travis: Go 1.11.1 Oct 12, 2018
CHANGELOG.md Merge remote-tracking branch 'origin/SDO' Oct 12, 2018
LICENSE.md include license for ODPI-C, too Aug 10, 2018
NOTES.md initial commit Jun 24, 2017
README.md README: LOB as string Oct 12, 2018
conn.go nicer switch for ErrBadConn Oct 16, 2018
conn_test.go fill ClientVersion eagerly and hash password in ConnectionParams.Stri… Sep 22, 2018
data.go add ObjectCollection.AsSlice Oct 8, 2018
drv.go quote Number.MarshalJSON - closes #112 Oct 12, 2018
drv_posix.go mage goracle.v2 compile on Windows Sep 15, 2017
drv_test.go quote Number.MarshalJSON - closes #112 Oct 12, 2018
go.mod update deps Sep 13, 2018
go.sum ODPI v3.0.0 is released Sep 14, 2018
lob.go bump version Mar 8, 2018
obj.go remove excess logging Oct 12, 2018
obj_test.go getConn cannot be called concurrently - use a Mutex to protect it Mar 2, 2018
orahlp.go document stored procedure calling and resultset wrapping Sep 18, 2018
orahlp_test.go remove kylelemons/godebug/diff in favor of google/go-cmp/cmp Aug 31, 2017
rows.go fix missing alias Oct 8, 2018
stmt.go remove excess logging Oct 12, 2018
stmt_go09.go use bytes.Buffer for go1.9 (instead of strings.Builder)i - helps #48 Mar 2, 2018
stmt_go10.go ResetSession: no timeout Mar 21, 2018
subscr.c lower maxArraySize - helps #73 Jun 29, 2018
subscr.go Do not allow events by default, make them opt-in with EnableEvents co… Sep 21, 2018
version.go change the default to retrieve CLOB as string, provide LobAsReader op… Oct 12, 2018
z_bench_test.go use golangci-lint Jun 3, 2018
z_lob_test.go remove excess logging Oct 12, 2018
z_qrcn_test.go add TestSDO for #90 Aug 23, 2018
z_test.go Merge remote-tracking branch 'origin/SDO' Oct 12, 2018

README.md

Build Status GoDoc Go Report Card codecov

goracle

goracle is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga's excellent OCI wrapper, ODPI-C.

At least Go 1.9 is required!

Connect

In sql.Open("goracle", connString), you can provide the classic "user/passw@service_name" as connString, or an URL like "oracle://user:passw@service_name".

You can provide all possible options with ConnectionParams. Watch out the ConnectionParams.String() does redact the password (for security, to avoid logging it - see https://github.com/go-goracle/goracle/issues/79). So use ConnectionParams.StringWithPassword().

More advanced configurations can be set with a connection string such as: user/pass@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=sn)))

A configuration like this is how you would add functionality such as load balancing across mutliple servers. The portion described in parenthesis above can also be set in the SID field of ConnectionParams.

For other possible connection strings, see https://oracle.github.io/node-oracledb/doc/api.html#connectionstrings and https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netag/configuring-naming-methods.html#GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE .

TL;DR; the short form is username@[//]host[:port][/service_name][:server][/instance_name], the long form is (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port)) (CONNECT_DATA= (SERVICE_NAME=service_name) (SERVER=server) (INSTANCE_NAME=instance_name))).

Rationale

With Go 1.9, driver-specific things are not needed, everything (I need) can be achieved with the standard database/sql library. Even calling stored procedures with OUT parameters, or sending/retrieving PL/SQL array types - just give a goracle.PlSQLArrays Option within the parameters of Exec!

The array size of the returned PL/SQL arrays can be set with goracle.ArraySize(2000)

  • the default is 1024.

Connections are pooled by default (except AS SYSOPER or AS SYSDBA).

Speed

Correctness and simplicity is more important than speed, but the underlying ODPI-C library helps a lot with the lower levels, so the performance is not bad.

Queries are prefetched (256 rows by default, can be changed by adding a goracle.FetchRowCount(1000) argument to the call of Query), but you can speed up INSERT/UPDATE/DELETE statements by providing all the subsequent parameters at once, by putting each param's subsequent elements in a separate slice:

Instead of

db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", 1, "a")
db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", 2, "b")

do

db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", []int{1, 2}, []string{"a", "b"})

Logging

Goracle uses github.com/go-kit/kit/log's concept of a Log function. Either set goracle.Log to a logging function globally, or (better) set the logger in the Context of ExecContext or QueryContext:

db.QueryContext(goracle.ContextWithLog(ctx, logger.Log), qry)

Tracing

To set ClientIdentifier, ClientInfo, Module, Action and DbOp on the session, to be seen in the Database by the Admin, set goracle.TraceTag on the Context:

db.QueryContext(goracle.ContextWithTraceTag(goracle.TraceTag{
	Module: "processing",
	Action: "first",
}), qry)

Extras

To use the goracle-specific functions, you'll need a *goracle.conn. That's what goracle.DriverConn is for! See z_qrcn_test.go for using that to reach NewSubscription.

Calling stored procedures

Use ExecContext and mark each OUT parameter with sql.Out.

Using cursors returned by stored procedures

Use ExecContext and an interface{} or a database/sql/driver.Rows as the sql.Out destination, then either use the driver.Rows interface, or transform it into a regular *sql.Rows with goracle.WrapRows.

Caveats

sql.NullString

sql.NullString is not supported: Oracle DB does not differentiate between an empty string ("") and a NULL, so an

sql.NullString{String:"", Valid:true} == sql.NullString{String:"", Valid:false}

and this would be more confusing than not supporting sql.NullString at all.

Just use plain old string !

NUMBER

NUMBERs are transferred as goracle.Number (which is a string) to Go under the hood. This ensures that we don't lose any precision (Oracle's NUMBER has 38 decimal digits), and sql.Scan will hide this and Scan into your int64, float64 or string, as you wish.

For PLS_INTEGER and BINARY_INTEGER (PL/SQL data types) you can use int32.

CLOB, BLOB

From 2.9.0, LOBs are returned as string/[]byte by default (before it needed the ClobAsString() option). Now it's reversed, and the default is string, to get a Lob reader, give the LobAsReader() option.

If you return Lob as a reader, watch out with sql.QueryRow, sql.QueryRowContext ! They close the statement right after you Scan from the returned *Row, the returned Lob will be invalid, producing getSize: ORA-00000: DPI-1002: invalid dpiLob handle.

So, use a separate Stmt or sql.QueryContext.

Install

Just

go get gopkg.in/goracle.v2

Or if you prefer dep

dep ensure -add gopkg.in/goracle.v2

and you're ready to go!

Note that Windows may need some newer gcc (mingw-w64 with gcc 7.2.0).

Contribute

Just as with other Go projects, you don't want to change the import paths, but you can hack on the library in place, just set up different remotes:

cd $GOPATH.src/gopkg.in/goracle.v2
git remote add upstream https://github.com/go-goracle/goracle.git
git fetch upstream
git checkout -b master upstream/master

git checkout -f master
git pull upstream master
git remote add fork git@github.com:mygithubacc/goracle
git checkout -b newfeature upstream/master

Change, experiment as you wish, then

git commit -m 'my great changes' *.go
git push fork newfeature

and you're ready to send a GitHub Pull Request from github.com/mygithubacc/goracle, newfeature branch.

pre-commit

Add this to .git/hooks/pre-commit (after go get github.com/golangci/golangci-lint/cmd/golangci-lint)

#!/bin/sh
set -e

output="$(gofmt -l "$@")"

if [ -n "$output" ]; then
	echo >&2 "Go files must be formatted with gofmt. Please run:"
	for f in $output; do
		echo >&2 "  gofmt -w $PWD/$f"
	done
	exit 1
fi

golangci-lint run

Third-party

  • oracall generates a server for calling stored procedures.