Go DRiver for ORacle

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

Build-time Requirements

• Go 1.15
• C compiler withCGO_ENABLED=1- so cross-compilation is hard

Run-time Requirements

• Oracle Client libraries - seeODPI-C

Although Oracle Client libraries are NOT required for compiling, theyare needed at run time. Download the free Basic or Basic Light package from https://www.oracle.com/database/technologies/instant-client/downloads.html.

Rationale

With Go 1.9, driver-specific things are not needed, everything (I need) can be achieved with the standarddatabase/sqllibrary. Even calling stored procedures with OUT parameters, or sending/retrieving PL/SQL array types - just give agodror.PlSQLArraysOption within the parameters ofExec(but not in sql.Named)! For example, the array size of the returned PL/SQL arrays can be set with godror.ArraySize(2000)(default value is 1024).

Documentation

SeeGodror API Documentationand theGodror User Guide.

Installation

Run:

go get github.com/godror/godror@latest

Then install Oracle Client libraries and you're ready to go!

godror is cgo package. If you want to build your app using godror, you need gcc (a C compiler).

Important: because this is a CGO enabled package, you are required to set the environment variableCGO_ENABLED=1 and have a gcc compile present within your path.

SeeGodror Installationfor more information.

Connection

To connect to Oracle Database usesql.Open( "godror", dataSourceName), wheredataSourceNameis alogfmt-encoded parameter list. Specify at least "user", "password" and "connectString". For example:

db, err:= sql.Open( "godror", `user= "scott" password= "tiger" connectString= "dbhost:1521/orclpdb1" `)

TheconnectStringcan beANYTHINGthat SQL*Plus or Oracle Call Interface (OCI) accepts: a service name, anEasy Connect string likehost:port/service_name,or a connect descriptor like(DESCRIPTION=...).

You can specify connection timeout seconds with "?connect_timeout=15" - Ping uses this timeout, NOT the Deadline in Context! Note thatconnect_timeoutrequires at least 19c client.

For more connection options, seeGodor Connection Handling.

Extras

To use the godror-specific functions, you'll need a*godror.conn. That's whatgodror.DriverConnis for! Seez_qrcn_test.gofor using that to reach NewSubscription.

Calling stored procedures

UseExecContextand mark each OUT parameter withsql.Out.

As sql.DB will close the statement ASAP, for long-lived objects (LOB, REF CURSOR), you have to keep the Stmt alive: Prepare the statement, and Close only after finished with the Lob/Rows.

Using cursors returned by stored procedures

UseExecContextand aninterface{}or adatabase/sql/driver.Rowsas thesql.Outdestination, then either use thedriver.Rowsinterface, or transform it into a regular*sql.Rowswithgodror.WrapRows, or (since Go 1.12) just Scan into*sql.Rows.

As sql.DB will close the statemenet ASAP, you have to keep the Stmt alive: Prepare the statement, and Close only after finished with the Rows.

For examples, see Anthony Tuininga's presentation about Go (page 41)!

Caveats

sql.NullString

sql.NullStringis 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 supportingsql.NullStringat all.

Just use plain oldstring!

NUMBER

NUMBERs are transferred asstringto Go under the hood. This ensures that we don't lose any precision (Oracle's NUMBER has 38 decimal digits), andsql.Scanwill hide this andScaninto yourint64,float64orstring,as you wish.

ForPLS_INTEGERandBINARY_INTEGER(PL/SQL data types) you can useint32.

CLOB, BLOB

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

Watch out, Oracle will error out if the CLOB is too large, and you have to usegodror.Lobin such cases!

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

So, use a separateStmtorsql.QueryContext.

For writing a LOB, the LOB locator returned from the database is valid only till theStmtis valid! SoPreparethe statement for the retrieval, thenExec,and onlyClosethe stmt iff you've finished with your LOB! For example, seez_lob_test.go,TestLOBAppend.

TIMESTAMP

As I couldn't make TIMESTAMP arrays work, alltime.Timeis bind asDATE,so fractional seconds are lost. A workaround is converting to string:

time.Now().Format("2-Jan-06 3:04:05.000000 PM")

See#121 under the old project.

Timezone

See thedocumentation- but for short, the database's OS' time zone is used, as that's what SYSDATE/SYSTIMESTAMP uses. If you want something different (because you fill DATE columns differently), then set the "location" in the connection string, or theTimezonein theConnectionParamsaccord to your chosen timezone.

Stored procedure returning cursor (result set)

varrset1,rset2driver.Rows

constquery=`BEGIN Package.StoredProcA(123,:1,:2); END;`

stmt,err:=db.PrepareContext(ctx,query)
iferr!=nil{
returnfmt.Errorf("%s: %w",query,err)
}
deferstmt.Close()
if_,err:=stmt.ExecContext(ctx,sql.Out{Dest:&rset1}, sql.Out{Dest:&rset2});err!=nil{
log.Printf("Error running %q: %+v",query,err)
return
}
deferrset1.Close()
deferrset2.Close()

cols1:=rset1.(driver.RowsColumnTypeScanType).Columns()
dests1:=make([]driver.Value,len(cols1))
for{
iferr:=rset1.Next(dests1);err!=nil{
iferr==io.EOF{
break
}
rset1.Close()
returnerr
}
fmt.Println(dests1)
}

cols2:=rset1.(driver.RowsColumnTypeScanType).Columns()
dests2:=make([]driver.Value,len(cols2))
for{
iferr:=rset2.Next(dests2);err!=nil{
iferr==io.EOF{
break
}
rset2.Close()
returnerr
}
fmt.Println(dests2)
}

Context with Deadline/Timeout

TL;DR; *always closesql.Rows ASAP!

Creating a watchdog goroutine, done channel for each call ofrows.Nextkills performance, so we create only one watchdog goroutine, at the firstrows.Nextcall. It is defused afterrows.Close(or the cursor is exhausted).

If it is not defused, it willBreakthe currently executing OCI call on the connection, when the Context is canceled/timeouted. You should always callrows.CloseASAP, but if you experience randomBreaks, remember this warning!

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/github.com/godror/godror
git remote add upstream https://github.com/godror/godror.git
git fetch upstream
git checkout -b master upstream/master

git checkout -f master
git pull upstream master
git remote add fork [email protected]:mygithubacc/godror
git checkout -b newfeature upstream/master

Change, experiment as you wish. Then run

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

and you're ready to send a GitHub Pull Request from the github.com/mygithubacc/godrorbranch callednewfeature.

Pre-commit

Download astaticcheck releaseand add this to .git/hooks/pre-commit:

#!/bin/sh
set-e

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

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

go run./check
execstaticcheck

Guidelines

As ODPI stores the error buffer in a thread-local-storage, we must ensure that the error is retrieved on the same thread as the prvious function executed on.

This means we have to encapsulate each execute-then-retrieve-error sequence in runtime.LockOSThread()andruntime.UnlockOSThread(). For details, see#120.

This is automatically detected bygo run./checkwhich should be called in the pre-commit hook.

Third-party

• oracallgenerates a server for calling stored procedures.